0

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
enter image description here

Control Panel ODBC Link
enter image description here

Community
  • 1
  • 1
Pradeep Yenkuwale
  • 181
  • 3
  • 12
  • 1
    Shouldn't that be `"DRIVER=SQLite3 ODBC Driver;Database=E:\VBA_Project_Demo\Demo\demo.db;"` ? –  Aug 03 '18 at 07:28
  • 1
    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 Aug 03 '18 at 07:30
  • @Jeeped actually i tried that syntax also as you mentioned, but again am getting the same error – Pradeep Yenkuwale Aug 03 '18 at 08: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 Aug 03 '18 at 08: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 Aug 03 '18 at 09:01
  • Possible duplicate of [Accessing a SQLite Database in VBA in Excel](https://stackoverflow.com/questions/42509154/accessing-a-sqlite-database-in-vba-in-excel) – Our Man in Bananas Aug 03 '18 at 11:07
  • have a look at [Using an Access, MySQL, PostgreSQL or SQLite database from VBA](http://pragmateek.com/using-an-access-mysql-postgresql-or-sqlite-database-from-vba/) – Our Man in Bananas Aug 03 '18 at 11:08
  • @OurManinBananas thanks for the response, i have used that syntax only, even am getting the same error, what i have mentioned :( – Pradeep Yenkuwale Aug 03 '18 at 11:15

2 Answers2

1

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
Vinh Can Code
  • 407
  • 3
  • 14
0

use 32bit odbc driver. 64bit not comaptible

Khennete
  • 26
  • 1
  • While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) 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. – Yunnosch May 12 '21 at 05:59