In this tutorial I am going to show you how to connect to a SQLite database with VBA.
What is SQLite
A SQLite database is open source, lightweight relationship file-based database. Unlike Microsoft Access, SQLite doesn’t have the 2GB storage limitation.
Steps To Connect To A SQLite Database In VBA
Step 1. Install SQLite ODBC Driver
To connect to a SQLite database in VBA, we first need to download the SQLite ODBC Driver. You can download the SQLIte driver by go to http://www.ch-werner.de/sqliteodbc/, download the driver executable file sqliteodbc_w64.exe, and install the driver.
Once you have successfully installed SQLite ODBC driver, you should see SQLite ODBC Driver is now available in ODBC Data Source Administrator.
Step2. Add ActiveX Data Object Reference
Next, we need to add ActiveX Data Objects reference for VBA to connect to the ODBC Connection Manager.
Open the VBA Window (shortcut ALT + F11), Tools > References, and check Microsoft ActiveX Data Objects <version number> Library and click OK.
Buy Me a Coffee? Your support is much appreciated!
Step 3. Connect To A SQLite Database With VBA Code
To connect to the SQLite database with VBA, you can use the VBA code snippet below. Makes sure you update the database path and SQL query.
How can I make the Database path dynamic? I want the user to select the database file to open.
I’m not a VBA for Excel expert, but you can use `Application.GetOpenFilename()` to bring up a file selection dialog. Take a look at the official documentation for the appropriate arguments to use. This will allow your users to easily search for and select the database they’d like to work with.
Hi all
if I copy excel file to another computer which doesn’t setup “Install SQLite ODBC Driver”. Can it run?