How to retrieve data from a SQLite Database in VBA in Excel?

Question

I am connecting to the .db file from VBA with ADO following the answer to this question. My code connects correctly but it does not retrieve correct data from the table. In my table i have several columns, the first is INT the second is TEXT and the rest are all INT.

Dim conn As Object, rst As Object
Dim strSQL As String
Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")


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

strSQL = "SELECT * from paston"


rst.Open strSQL, conn


Sheets("sheet2").Range("A1").CopyFromRecordset rst
rst.Close


Set rst = Nothing: Set conn = Nothing

The problem is when i select all on the sheet i only the get the first column idx, and if i select only the TEXT column it will not output anything, if i select any other column it will retrieve the numbers as integers but they are longs,

Does it help to actually name the columns in the SQL statement or just use the table name? — MP24, Mar 17 '18 at 12:46

score 2 · Accepted Answer · answered Mar 31 '18 at 13:54

2

After giving up, and coming back i watched this video and i spotted a difference: rst.Open strSQL, conn, 1, 1

and this allowed me to copy the entire table and paste it onto the sheet

answered Mar 31 '18 at 13:54

Mihai Moldovan

137
1
12

How to retrieve data from a SQLite Database in VBA in Excel?

1 Answers1

Linked