From SQLite to Excel with ODBC

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

I have the same symptoms as you. Did you ever find a solution?

I can link to or import the SQLite db in MS Access 2003 but nothing in Excel 2003 using the same System DSN.

Here is what I did:

Installed SQLite ODBC driver on a WinXP PC.
Configured a System DSN
Open Excel
> Data
> Import External Data
> Import Data
> New Source (button)
> Other/Advanced
> Connection (tab)
> select the System DSN (that I created previously) in the Data Source Name drop down
> OK (leaving uid, pw and intitial catelog left blank)

<Dialog Box>

Microsoft Excel
The Data Connection Wizard cannot obtain a list of databases from the specificed data source.

>OK
</Dialog Box>

TIA