CODEWITHSUNDEEP
exceldatabasevbasqlite
Pradeep Yenkuwale
Pradeep Yenkuwale

Reputation: 300

Connect to Sqlite Database using VBA

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

Upvotes: 0

Views: 9404

Answers (2)

Khennete
Khennete

Reputation: 26

use 32bit odbc driver. 64bit not comaptible

Upvotes: 0

Vinh Can Code
Vinh Can Code

Reputation: 427

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

Upvotes: 2

Related Questions