|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
|