From SQLite to Excel with ODBC-driver

[Florian Mauderer]

Hello,



I would like to access from Excel 2003 (OS: Windows XP) to a SQLite-Database
with ODBC.



I did the following steps:



1.) Install SQLite-ODBC-driver:



From http://www.ch-werner.de/sqliteodbc/ I downloaded the file
"sqliteodbc.exe" which installs the ODBC-driver on my computer. I saw after
the installation on the "ODBC-datasources/System-DSN"-menu three new entries
("SQLite Datasource", "SQLite UTF-8 Datasource" und "SQLite3 Datasource").



2.) Create an SQLite-database:



From http://sourceforge.net/projects/sqlitebrowser/ I got a SQLiteBrowser
(version 1.2.1) and I created one new database ("TestDB") with three tables
and I put few data inside. I saved the database under "C:\Test.db".



3.) Create USer-DSN:



On "ODBC-datasources/User-DSN" I accomplished the following steps:



"Add"

"SQLite ODBC Driver"

"Finish"

Data Source Name: TestDB

Database Name: C:\Test.db

"Ok"



4.) Get the data in Excel:



In Excel I made the following:



"Data/Import external data/Import data.."

"+Create new datasource.odc" + "Open"

"ODBC DSN" + "continue"

"TestDB" + "continue"

(mask "SQLite ODBC Driver Connect" appears)

"OK"

(mask " Dataconnectionproperties " appears)

"OK"

(Maske "SQLite ODBC Driver Connect" appears again)

When I push "OK" I get an error message that I have no access and so on.



Alternatively I accomplished step 1.) until 3.) und wrote the following code
in VBA:



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=TestDB") 'Test = DSN-Name

Set rs = con.OpenRecordset("select * from TabPerson")



' Enumerate the specified Recordset object.

With rs

Do While Not .EOF

'Debug.Print , .Fields(0), .Fields(1)

MsgBox .Fields(0)

.MoveNext

Loop

End With



rs.Close

con.Close

ws.Close

End Sub



When I call this procedure I get the following error message:



"Run time error '3146'



What is wrong?



Thanks a lot



Florian