SQLite Forum

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified error when use VBA connect Sqlite

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified error when use VBA connect Sqlite

(1.1) Originally by Nhan (Nhan1978) with edits by Stephan Beal (stephan) on 2021-09-30 14:47:34 from 1.0 [link] [source]

Hi guys, I am using VBA to connect to SQLite. I have installed ODBC driver 64 bits from http://www.ch-werner.de/sqliteodbc/ and checked that SQLite ODBC Driver exists in System DSN and User DSN in ODBC Data Source Admin app. Here is my VBA code:

Sub SQLiteADO()
    Dim con As ADODB.Connection, rs As ADODB.Recordset
    Dim s As String
    s = "Driver = {SQLite3 ODBC Driver}; Database = " & ThisWorkbook.Path & "\test.db;"
    Set con = New Connection
    con.Open s
    s = "SELECT * FROM Data"
    Set rs = New Recordset
    rs.Open s, con
    Range("A1").CopyFromRecordset rs
    Set rs = Nothing
    Set con = Nothing
End Sub

My problem is I cannot connect and receive error "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". Please help me. Thank you.

(2) By Larry Brasfield (larrybr) on 2021-10-01 17:20:40 in reply to 1.1 [source]

This is not really a SQLite issue. It is an ODBC driver management issue.

BTW, setting object-referencing variables to Nothing just before they go out of scope is not necessary. (That advice is also off-topic here.)

(3) By anonymous on 2021-10-01 18:34:22 in reply to 1.1 [link] [source]

Architectures do not mix.

ADODB is 32-bit & you have installed the 64-bit ODBC driver; try again with the 32-bit ODBC driver.

(4) By Nhan (Nhan1978) on 2021-10-01 23:34:10 in reply to 3 [link] [source]

Thanks for your help. But after I removed ODBC driver 64 bits and installed 32 bits one, that error still occurs. I use Windows and Excel 64 bits.

(5) By Larry Brasfield (larrybr) on 2021-10-01 23:43:48 in reply to 4 [link] [source]

I have used Werner's ODBC driver for SQLite many times, including in an application and as a plug-in with DBMS tools. It works well, and is a good citizen among ODBC drivers insofar as its management by the ODBC infrastructure works. So the locus your problem is within the set of things you are doing or should be doing.

Your problem is not a SQLite issue and it is off-topic here. Furthermore, you are much more likely to get a useful answer by consulting ODBC documentation and posing your problem somehwhere that ODBC issues are discussed and participants are familiar with ODBC infrastructure issues.

(6) By Nhan (Nhan1978) on 2021-10-01 23:49:09 in reply to 5 [link] [source]

Thank you. I will try to search more.

(7) By anonymous on 2021-10-02 06:53:11 in reply to 4 [link] [source]

The 32- and 64-bit ODBC drivers can co-exist.

The problem is Excel 64-bits ... it can only use 64 bit software. ADODB is 32-bit and, as far as I am aware, no 64-bit version exists.

Option 1: Check your code using 32-bit Excel for reassurance.. I use ADODB, the 32-bit driver with Excel 32-bit & it works.

Option 2: Re-install 32-bit Excel

If re-installing 32-bit Excel is NOT an option, execute your queries in the SQLite CLI i.e. export to a CSV and import the CSV in your 64-bit Excel. You can do all of this using Excel VBA.

The ODBC drivers are statically linked to a much earlier version of SQLite3 & do not come with the SQLite3 CLI - you can download it but it will be the latest version, thereby introducing compatibility issues between the CLI and the earlier DLL embedded in the ODBC driver.

As Larry points out, much of this is off topic here ...

(8) By Nhan (Nhan1978) on 2021-10-02 13:13:22 in reply to 7 [link] [source]

Thank you. I will try more and post the search result.

(9) By Banana on 2021-10-02 14:02:52 in reply to 7 [link] [source]

Actually, this is not completely accurate. There is 64-bit ADO, otherwise a lot of Office documents depending on ADO would break on 64 bit Office.

The problem is likely with trying to use provider MSDASQL (aka ODBC over OLEDB provider) which is implicit in the connection (the provider keyword wasn’t specified, and the ODBC connection syntax is used rather than OLEDB connection syntax. This causes ADO to use MSDASQL as the default provider).

I suggest that the OP start with this SO thread and continue discussion there or start a new SO thread.

(10) By Nhan (Nhan1978) on 2021-10-02 14:34:38 in reply to 9 [link] [source]

Thank Banana.

(11) By bucweat on 2021-10-03 14:47:48 in reply to 1.1 [link] [source]

Been using the www.ch-werner.de drivers successfully, including ones we locally compile 32/64 bit with VS2019. We mostly use from windows script files (scripts with .wsf extension) which is very similar to using from VBA. This works both 32 and 64 bit...someone mentioned here that you can have both installed at same time which is true.

Looking at your code above, one possible issue might be the connection string you are using. Here is what I use (Note the lack of curly brackets {}):

DRIVER=SQLite3 ODBC Driver;Database=[path to database];

Extra tidbit: You can also load extensions via the connection string and use via ODBC...just add the following to the connection string:

LoadExt=[path to extension];

Here is an example:

DRIVER=SQLite3 ODBC Driver;Database=c:\temp\test.db;LoadExt=c:\sqlite\extensions\csv.dll;

Note that no quotes are required for file paths in the connection string even if there are spaces in the paths (blasphemy!!!).

WRT recordset, I've had best luck using CursorType = adOpenStatic and LockType = adLockOptimistic. Pretty sure these are not the defaults...

Hopefully that helps...