Sorry, but I have never used SQLite.

I am having the same issue attempting to export tables from an Access DB to SQlite DB tables.  I have the Devart SQLite ODBC driver installed and it works perfectly to export tables, one at a time, from the Access DB using the SQlite ODBC.  However, when I attempt do this from within Access VBA using DoCmd.TransferDatabase in order to loop through multiple tables to export instead of one at a time.  It always errors with "Run Time Error [Micrsoft] [ODBC Manager] Data Source name not found and no default driver specified (#0)".  I have tried many combinations in the ODBC section of the DoCmd.TransferDatabase parameters and, so far, nothing works.  What is strange, it works so well with the same SQLite ODBC driver and SQLite DB as is referenced in those parameters using the standard Access Export ODBC SQLlite.

Need to test but found this on SO.

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:\Path\To\SQLite\Database.db;"

strSQL = "SELECT Vessels.vsl_name, Vessels.dwt FROM Vessels " & _
         " GROUP BY Vessels.vsl_name, Vessels.dwt ORDER BY Vessels.vsl_name ; "

' 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

Thank you for the reply.  I just got mine to work by downloading and using a different SQL3 ODBC driver.  i didn't change anything in the code but changed the ODBC DSN and driver using the exact same DoCmd.TransferDatabase parameters and it worked perfectly.  it was just that other ODBC driver didn't work from VBA for some reason.  I will try your code with the original ODBC driver and see if it will work also.  Thanks again!!!

Great to hear it's working now.

What SQLite GUI are you using to interrogate that db?

Tried your code and it worked with the new SQL ODBC driver but not the original one.  The original driver is causing the issue in Access VBA, but will export tables to SQLite DB using the Access Export ODBC and that driver.  Everything is good now because the new driver works perfectly for Access export ODBC and within VBA.

Missed your question.  I use SQLite DB Browser to access the SQLite DBs and the SQLite Export Access DB that I am using to export multiple tables to SQLlite is: https://blog.stranianelli.com/how-to-convert-mdb-files-to-sqlite-database.  There are 2 download files from that site: ExportMDBToSQLite3.mdb and NewSQLiteDB.db. i am using Export Access DB.