Accessing a SQLite Database in VBA in Excel

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 ObjectSet conn = CreateObject("ADODB.Connection")Set rst = CreateObject("ADODB.Recordset")' OPEN CONNECTIONconn.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 RECORDSETrst.Open strSQL, conn' OUTPUT TO WORKSHEETWorksheets("results").Range("A1").CopyFromRecordset rstrst.Close' FREE RESOURCESSet 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 ObjectSet conn = CreateObject("ADODB.Connection")Set rst = CreateObject("ADODB.Recordset")' OPEN CONNECTIONconn.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 RECORDSETrst.Open strSQL, conn' OUTPUT TO WORKSHEETWorksheets("results").Range("A1").CopyFromRecordset rstrst.Close' FREE RESOURCESSet rst = Nothing: Set conn = Nothing

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 ObjectSet conn = CreateObject("ADODB.Connection")Set rst = CreateObject("ADODB.Recordset")' OPEN CONNECTIONconn.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 RECORDSETrst.Open strSQL, conn, 1, 1' OUTPUT TO WORKSHEETWorksheets("results").Range("A1").CopyFromRecordset rstrst.Close' FREE RESOURCESSet rst = Nothing: Set conn = Nothing

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

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 returnthe shell object and moved/added other code to mine. This is easy to set upquickly 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 iEnd Sub