Accessing a SQLite Database in VBA in Excel

Question

I have been adding an MS Access database to VBA in order to conduct some analysis of ships. However the database has now changed to SQlite, which I have no idea how to access from VBA. I have tried the use GitHub's SQLiteForExcel, but I don't understand how it works, even with the examples. The code I have for accessing the Access database is below: (the db_path is the hyperlink to my Access database)

Sub query_db()

On Error GoTo Errorhandler
Dim v_db As DAO.Database
Dim rst As DAO.Recordset
Dim vessels_db As Variant
Dim strSQL As String
Dim i As Long
Dim ws As Worksheet

Set ws = Worksheets("results")
ws.Select

vessels_db = [db_path]
Set v_db = OpenDatabase(vessels_db)


Worksheets("results").Select
[x_0].Select
Range(Selection, Selection.Offset(40000, 1)).ClearContents

strSQL = " SELECT Vessels.vsl_name, Vessels.dwt FROM Vessels " & _
          " GROUP BY Vessels.vsl_name, Vessels.dwt ORDER BY Vessels.vsl_name ; "
Set rst = v_db.OpenRecordset(strSQL)
...

Does anyone have any idea how I can change this code using SQLiteForExcel so I can access the SQLite database.

Many thanks in advance

Answer 1

MS Access' default engine, Jet/ACE, and SQLite share the same quality in that they are file-level databases where database files reside at disk level in directories as opposed to server level databases (SQL Server, Oracle, MySQL, Postgres).

To fluidly interchange between backend databases, consider connecting databases in Excel using ADO. Right now you use DAO which is the default connection layer for MS Access.

The first thing you require is to download an SQLite ODBC Driver, one that matches your version (SQLite 3 most likely) and your Windows bit level (32/64 bit). As comparison, your machine most likely already has installed an MS Access ODBC Driver. Once done, simply set up your connection string:

SQLite

Dim conn As Object, rst As Object

Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

' OPEN CONNECTION
conn.Open "DRIVER=SQLite3 ODBC Driver;Database=C:\Path\To\SQLite\Database.db;"

strSQL = "SELECT Vessels.vsl_name, Vessels.dwt FROM Vessels " & _
         " GROUP BY Vessels.vsl_name, Vessels.dwt ORDER BY Vessels.vsl_name ; "

' OPEN RECORDSET
rst.Open strSQL, conn

' OUTPUT TO WORKSHEET
Worksheets("results").Range("A1").CopyFromRecordset rst
rst.Close

' FREE RESOURCES
Set rst = Nothing: Set conn = Nothing

MS Access

As comparison, with ADO you can just simply switch connection strings referencing the ODBC Driver for different database backends. Notice like above, the database source is a directory path:

Dim conn As Object, rst As Object

Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

' OPEN CONNECTION
conn.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Path\To\Access\DB.accdb;"

strSQL = "SELECT Vessels.vsl_name, Vessels.dwt FROM Vessels " & _
         " GROUP BY Vessels.vsl_name, Vessels.dwt ORDER BY Vessels.vsl_name ; "

' OPEN RECORDSET
rst.Open strSQL, conn

' OUTPUT TO WORKSHEET
Worksheets("results").Range("A1").CopyFromRecordset rst
rst.Close

' FREE RESOURCES
Set rst = Nothing: Set conn = Nothing

Answer 2

Great solution, thanks Parfait!

Just one small quick correction, you actually need to make:

rst.Open strSQL, conn, 1, 1

This way, the complete solution would be:

Dim conn As Object, rst As Object

Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

' OPEN CONNECTION
conn.Open "DRIVER=SQLite3 ODBC Driver;Database=C:\Path\To\SQLite\Database.db;"

strSQL = "SELECT Vessels.vsl_name, Vessels.dwt FROM Vessels GROUP BY Vessels.vsl_name, Vessels.dwt ORDER BY Vessels.vsl_name ;"

' OPEN RECORDSET
rst.Open strSQL, conn, 1, 1

' OUTPUT TO WORKSHEET
Worksheets("results").Range("A1").CopyFromRecordset rst
rst.Close

' FREE RESOURCES
Set rst = Nothing: Set conn = Nothing

This will make rst contain the entire table you got from the query.