Connect to Sqlite Database using VBA

Question

My VBA code to connect to the Sqlite Database

Sub LoadValues()

   Dim conn As Object, rst As Object

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

  ' OPEN CONNECTION
  'Am getting Error at this line
   conn.Open "DRIVER={Microsoft.ACE.OLEDB.12.0 (*.db,   *.accdb)};DBQ=E:\VBA_Project_Demo\Demo\demo.db;"


  strSQL = "SELECT * FROM test "

  ' 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

End Sub

The error message while executing

Control Panel ODBC Link

Shouldn't that be "DRIVER=SQLite3 ODBC Driver;Database=E:\VBA_Project_Demo\Demo\demo.db;" ? — user4039065
– user4039065, Commented Aug 3, 2018 at 7:28
For SQLite use something like conn.Open "DRIVER=SQLite3 ODBC Driver;Database=E:\VBA_Project_Demo\Demo\demo.db;" and for Access use something lile conn.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=E:\VBA_Project_Demo\Demo\demo.accdb;" — Siddharth Rout
– Siddharth Rout, Commented Aug 3, 2018 at 7:30
@Jeeped actually i tried that syntax also as you mentioned, but again am getting the same error — Pradeep Yenkuwale
– Pradeep Yenkuwale, Commented Aug 3, 2018 at 8:50
@SiddharthRout as per your syntax, it for MS Access, which takes *.mbd file, but am trying to connect with SQLite, please suggest some other solutions or syntax — Pradeep Yenkuwale
– Pradeep Yenkuwale, Commented Aug 3, 2018 at 8:51
I have used the syntax like this==> conn.Open "DRIVER=SQLite3 ODBC Driver;Database=E:\VBA_Project_Demo\Demo\demo.db;" but getting same error — Pradeep Yenkuwale
– Pradeep Yenkuwale, Commented Aug 3, 2018 at 9:01

Vinh Can Code · Accepted Answer · 2018-08-03 07:30:40Z

3

In order to connect SQLite, you download ODBC Driver at (or another source) http://www.ch-werner.de/sqliteodbc/

Then modify the connection.

Hope it helps.

Sub LoadValues()

   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:\mydb.db;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"   'Am getting Error at this line

  strSQL = "SELECT * FROM test "

  ' 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

End Sub

answered Aug 3, 2018 at 7:30

Vinh Can Code

4373 silver badges16 bronze badges

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

7 Comments

Pradeep Yenkuwale Over a year ago

,thanks for the response,but i tried with the syntax, that you have mentioned but am getting same error

Vinh Can Code Over a year ago

Did you install 32/64 bit? Can you go to control panel to add ODBC link?

Pradeep Yenkuwale Over a year ago

i have installed 64 bit,and have added the ODBC link also in control panel

Vinh Can Code Over a year ago

Then you can do "conn.Open DSN"

Pradeep Yenkuwale Over a year ago

Hi Guys, Actually i executed the syntax mentioned above in the different machine, with the same database (SQLite) and the respective driver, it is working fine, i am still not getting what was the issue in that machine that it is not working with the same syntax and same machine configurations

|

Khennete · Accepted Answer · 2021-05-12 05:30:14Z

1

use 32bit odbc driver. 64bit not comaptible

answered May 12, 2021 at 5:30

Khennete

361 bronze badge

1 Comment

Yunnosch Over a year ago

While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply.

Collectives™ on Stack Overflow

Connect to Sqlite Database using VBA

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related