SQLite and VBA: Cannot establish connection to open and work with database

simurq

Board Regular
Joined
Nov 11, 2011
Messages
73
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.
  • 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:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top