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
– MP24, Commented Mar 17, 2018 at 12:46

Mihai Moldovan · Accepted Answer · 2018-03-31 13:54:06Z

3

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, 2018 at 13:54

Mihai Moldovan

1472 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related