I have no prior experience with SQL databases, so this is my first try. Please bear with me...
My database is a regular *.sqlite file which I'd like to connect to Excel through ADO interface using VBA. The connection string (ConnectionString) below is taken from https://www.connectionstrings.com/sqlite3-odbc-driver/. But no matter which string parameter I use, Excel returns errors such as file name too long, or data source name not found.
After a quick search, I found some people pointing to SQLite driver to access the database.
So, I'll be grateful if someone can share own methods of connecting and manipulating SQLite database?
Thanks!
My database is a regular *.sqlite file which I'd like to connect to Excel through ADO interface using VBA. The connection string (ConnectionString) below is taken from https://www.connectionstrings.com/sqlite3-odbc-driver/. But no matter which string parameter I use, Excel returns errors such as file name too long, or data source name not found.
After a quick search, I found some people pointing to SQLite driver to access the database.
- Do I really need it?! The problem is that I don't want to bother users with extra files' installation, etc.
- Can I connect an *.sqlite file to VBA using default database drivers installed on my computer (Windows 7 x64 Control Panel -> System and Security -> Administrative Tools -> Data Sources ODBC)?
- If yes, how can I accomplish this task?
So, I'll be grateful if someone can share own methods of connecting and manipulating SQLite database?
Code:
Sub ReadNavDataTest()
Dim Conn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim dbase As String
Dim SettingsFolder As String
Set Conn = New ADODB.Connection
SettingsFolder = ".Charter"
dbase = Environ("APPDATA") & "\" & SettingsFolder & "\navdata.sqlite"
Conn.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=dbase;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"
Conn.Open
Conn.Close
End Sub
Thanks!
Last edited: