[VBA] Connect to SQLite 3 Database, and link to its table through ODBC

After many trials and study, I found out that it is impossible for a Microsoft Access Default Database Object (DBO) to connect to a SQLite database by DSN-Less or Default File DSN methods.

The only way is to create a user/system DSN to connect to a SQLite database.

Below is the details.

1. Go to below url to install the SQLite ODBC Driver,
http://www.ch-werner.de/sqliteodbc/


2. Time for trial and error,
a) Open an access file(.mdb), try to create an ODBC connection by File DSN.
After create a File DSN, then apply it to connect to a SQLite Database.
Then an error message, "Reserverd error (-7778)" will be prompted out like below,

b) Try to connect to SQLite by VBA like below,

    With CurrentDb
        Dim tdf As TableDef

        Set tdf = .CreateTableDef("SampleTable")
        tdf.Connect = "ODBC;Driver=SQLite3 ODBC Driver;Database=C:\SQLite\sample.sqlite;)
        'tdf.SourceTableName = "SampleTable"
        
        .TableDefs.Append tdf

    End With 'CurrentDb

    But it fails too.

3. Then, the only solution is to use User/System DSN.
    i) Go to "Control Panel\All Control Panel Items\Administrative Tools\Data Sources (ODBC)"
    ii) Click User/System DSN
    iii) Start to add a SQLite 3 Driver DSN till Parameter Browser, then enter a proper name (my version, SQLite3_DataSrc) necessary parameter except             database path, then save it.

4. a)Try to connect the SQLite again by the User/system DSN added in part 3) like part 2a), it should be ok.

    b) Try to connect to SQLite by VBA again like below,

    With CurrentDb
        Dim tdf As TableDef

        Set tdf = .CreateTableDef("SampleTable")
        tdf.Connect = "ODBC;DSN=SQLite3_DataSrc;Database=C:\SQLite\sample.sqlite;"
        'tdf.SourceTableName = "SampleTable"
        
        .TableDefs.Append tdf

    End With 'CurrentDb

    It works now