Sqlite for Excel Demo not working

Apr 16, 2013 at 7:23 AM
Hi to all,

I am new to sqlite with Excel, I've download the SQLite for Excel Version 0.9, and trying to run the demo as mentioned in site:

1-Download the release archive.
2-Unzip the download to a convenient location.
3-Open the Distribution\SQLiteForExcel.xls file.
4-Open the VBA Editor (Alt+F11).
5-Note the SQLite3 module which contains the declarations and helper functions to access SQLite.
6-Examine and run the example test code in the SQLite3Demo module.

Before going through all above process I've create a blank database using sqliteadmin, By default it creates *.s3db file, and in demo code it is looking for db3 file, so I've to manually change the file extention after creating database. Then run the test by calling AllTests() routine, following are the results:

SQLite3Close returned 0
----- TestError Start -----
SQLite3Open returned 14
SQLite3Open error message: unable to open database file
SQLite3Close returned 0
----- TestError End -----
----- TestInsert Start -----
SQLite3Open returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3Execute - Insert affected 1 record(s).
----- TestInsert End -----
----- TestSelect Start -----
SQLite3Open returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Row Ready
Column count: 3
Column 0: TheId INTEGER 123
Column 1: TheText TEXT ABC
Column 2: TheValue FLOAT 42.1
SQLite3Step Row Ready
Column count: 3
Column 0: TheId INTEGER 987654
Column 1: TheText TEXT ZXCVBNM
Column 2: TheValue NULL Null
SQLite3Step Done
SQLite3Finalize returned 0
----- TestSelect End -----
----- TestBinding Start -----
SQLite3Open returned 0
SQLite3Finalize returned 0
Insert Elapsed: 00:00:04
SQLite3PrepareV2 returned 0

At row 1

Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.

Column 3: TheValue FLOAT 330.616414546967

At row 10001

Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.

Column 3: TheValue FLOAT 330.616414546967

At row 20001

Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.

Column 3: TheValue FLOAT 330.616414546967

At row 30001

Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.

Column 3: TheValue FLOAT 330.616414546967

At row 40001

Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.

Column 3: TheValue FLOAT 330.616414546967

At row 50001

Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.

Column 3: TheValue FLOAT 330.616414546967

At row 60001

Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.

Column 3: TheValue FLOAT 330.616414546967

At row 70001

Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.

Column 3: TheValue FLOAT 330.616414546967

At row 80001

Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.

Column 3: TheValue FLOAT 330.616414546967

At row 90001

Column count: 4
Column 0: TheId INTEGER 42501
Column 1: datetime(TheDate) TEXT 2001-05-16 00:00:00
Column 2: TheText TEXT The quick brown fox jumped over the lazy dog.

Column 3: TheValue FLOAT 330.616414546967

SQLite3Finalize returned 0
Select Elapsed: 00:00:11
----- TestBinding End -----
----- TestDates Start -----
SQLite3Open returned 0
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
Event: Nice trip somewhere Date: 19/06/2010
SQLite3Finalize returned 0
----- TestDates End -----
----- TestStrings Start -----
SQLite3Open returned 0
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
Result1:
Result2:
Long String is the same: True
SQLite3Finalize returned 0
----- TestStrings End -----
----- TestBackup Start -----
SQLite3Open returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Row Ready
Column count: 2
Column 0: Key INTEGER 1
Column 1: Value TEXT First
SQLite3Step Row Ready
Column count: 2
Column 0: Key INTEGER 2
Column 1: Value TEXT Second
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3Open returned 0
SQLite3BackupStep returned 101
SQLite3BackupFinish returned 0
Backup result 0
Selecting from backup:
SQLite3PrepareV2 returned 0
SQLite3Step Row Ready
Column count: 2
Column 0: Key INTEGER 1
Column 1: Value TEXT First
SQLite3Step Row Ready
Column count: 2
Column 0: Key INTEGER 2
Column 1: Value TEXT Second
SQLite3Step Done
SQLite3Finalize returned 0
----- TestBackup End -----
----- TestBlob Start -----
SQLite3Open returned 0
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
Blob byte 0: 90
Blob byte 1: 91
Blob byte 2: 92
SQLite3Finalize returned 0
----- TestBlob End -----
SQLite3Open returned 0
SQLite3OpenV2 returned 0
SQLite3PrepareV2 returned 0
SQLite3Step returned 101
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
SQLite3Step returned 8
Cannot Write in Read Only database
SQLite3Finalize returned 8
SQLite3PrepareV2 returned 0
SQLite3Step returned 101
But Reading is granted on Read Only database
SQLite3Finalize returned 0
SQLite3Close V2 returned 0
SQLite3Close returned 0

After this routine completes, the database file is just automatically deleted!!!! I think I am doing something very wrong, I will appreciate for any guidance for a beginner...

Thanks and best regards

Ahmed
Apr 16, 2013 at 7:47 AM
Hi Ahmed,

It sounds like the code is working as expected. The SQLite3Demo module has a number of different tests, just to show to call the different SQLite API functions. Many of these test routines create a database file, do something with it, and then delete the file again. (The Kill command is where the files are actually deleted.) So to understand what's going on in the test routines, you'd need to look at the code and how the different calls to the SQLite API are made.

A more useful example of how SQLite for Excel can be used, is the sample project by Mark Camilleri posted here: http://www.gatekeeperforexcel.com/other-freebies.html

Regards,
Govert
Apr 16, 2013 at 8:08 AM
Hi Govert,

Thanks so much for prompt reply...you are absolutely right!!!! Kill statement was there...I've comment the line and all goes well....after completing I've also check it through sqliteadmin and yes....data is all there....And amazingly it's lightning fast...

Thanks again for guiding me.

Ahmed