Excel / SQLite

[Tony Zappal]

Hi All,

Im tyring to use excel 2002 on XP to access an sqlite database.
I have a test database called Test.db with one table, containg three columns
and several lines of data.

I've installed the sqlite odbc drivers from
http://www.ch-werner.de/sqliteodbc/
I've then created a datasource via the ODBC Data Source Administrator window.

I found the following code to see if i can access the database:

Sub TestDB()
Dim ws As Workspace
Dim con As Connection
Dim rs As Recordset

Set ws = DBEngine.CreateWorkspace("ws1", "", "", dbUseODBC)
Workspaces.Append ws
Set con = ws.OpenConnection("con1", dbDriverComplete, False,
"ODBC;DSN=TestDB1")
Set rs = con.OpenRecordset("select * from TabPerson")

With rs
Do While Not .EOF
MsgBox .Fields(0)
..MoveNext
Loop
End With

rs.Close
con.Close
ws.Close
End Sub


However when i run it i get the following error:

Run-time error '0':
IM001: [Microsoft][ODBC Driver Manager] Driver does not support this function


I have the following References selected:
Microsoft DAO 3.6 Object Library

Is anyone able to help on this? I've search through google for an answer to
the error message and for alternative code. Basically I am trying to develop
an entry form which will store the data in sqlite, then have a few report
macro's to pull data out.

Thanks Heaps.
Tony Z.

 

[RB Smissaert]

I haven't used that ODBC driver, but will give it a try tonight and see what
the problem is.
I use the free VB wrapper that you can download from here:
www.datenhaus.de/Downloads/dhRichClientDemo.zip
and most likely that is a better option than the ODBC driver.

RBS

 

[Tony Zappal]

Hi,
Thanks for your reply.
I have looked at the dhRichClientDemo.zip, but i'm not sure on how to use
the wrapper.
Was hoping to just use an ODBC driver. Will look more into the wrapper.

Cheers,
Tony Z.

 

[RB Smissaert]

There are examples in the .zip file and although they are for VB6 you can
just open them as plain text and it will show you how to use this wrapper.
Look for example at fInsertsdhSQLite.frm and also fMain.frm. Let me know if
you are still stuck and I can post a demo .xls file.

RBS

 

[Tony Zappal]

Thanks for your help RBS, I'll give the wrapper a go

Cheers,
Tony Z.