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

I'd convert it to ADO - there are [examples here](http://stackoverflow.com/documentation/vba/3578/working-with-ado). You can find SQLite connection strings [here](https://www.connectionstrings.com/sqlite/). — Comintern, Feb 28 '17 at 15:15

score 19 · Accepted Answer · answered Feb 28 '17 at 15:40

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

score 4 · Answer 2 · answered Jul 13 '18 at 14:36

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.

score 0 · Answer 3 · edited Sep 12 '20 at 04:39

After failing to adapt this or this to my needs,I finally
succeeded using Marcus Mangelsdorf's refinements from here.
Not showing his code - it's in the link. I just put it in it's own module
called "WSHreturn", changed it (along with the function Name/Args) to return the shell object and moved/added other code to mine. This is easy to set up quickly and will be the basis for a LINQ? type functionality.

Sub VBALimposterQ()
    'With >1 field, SQLite default delim is Pipe "|"
    Const sqlInit As String = "c:\users\user\sqlite3.exe :memory:"
    Const sqlCreat As String = "CREATE Table Nums (n1 INTEGER NOT NULL, n2 INTEGER NOT NULL);"
    Const sqlIns0   As String = "INSERT INTO Nums VALUES (33,99);"
    Const sqlIns1   As String = "INSERT INTO Nums VALUES (11,22);"
    Const sqlIns2   As String = "INSERT INTO Nums VALUES (44,55);"
    Const sqlQry   As String = "SELECT RowId, n1, n2 from Nums;"
    Dim Ax, Axi, i, S
    Dim sqlShell As Object  'REF: Windows Script Host Object Model
    Set sqlShell = WSHreturn.getWShell(sqlInit) 'Qualifying Mssr. Mangelsdorf's code
    With sqlShell                   'with module name I gave it. 
        .StdIn.Write sqlCreat       'Thx Mathieu Guindon!
        .StdIn.Write sqlIns0
        .StdIn.Write sqlIns1
        .StdIn.Write sqlIns2
        .StdIn.Write sqlQry
        
        .StdIn.Close
        S = .StdOut.ReadAll
        Ax = Split(S, vbCrLf, , vbTextCompare)
        .Terminate
    End With
    
    For i = 0 To UBound(Ax)
        Axi = Ax(i)
        Debug.Print Axi
    Next i
End Sub

Accessing a SQLite Database in VBA in Excel

3 Answers3

Linked