[VBA] Append table from MS ACCESS to SQLite

https://github.com/walter426/VbaUtilities/blob/master/SqlUtilities.bas

Due to the limitation of the ODBC connection, it is impossible to insert or update all records from access table into the SQLite table in one time. We need to do it in an indirect way like below,
1. Copy the table with data to be appended into a temporary .mdb database
2. Convert the mdb file to SQLite formet.(Java)
http://waltertech426.blogspot.com/2014/01/sqlite-convert-ms-access-mdb-file-into.html
3. Do the appending through SQLite script directly.(Python)
https://github.com/walter426/VbaUtilities/blob/master/SQLiteCmdParser.py

As below function required other functions I have written before, pls refer below repository
https://github.com/walter426/VbaUtilities/



'Execute SQLite Command Set
Public Function ExecuteSQLiteCmdSet(SQLiteDb_path As String, CmdSet As String) As String
    On Error GoTo Err_ExecuteSQLiteCmdSet
    
    Dim FailedReason As String

    If FileExists(SQLiteDb_path) = False Then
        FailedReason = SQLiteDb_path
        GoTo Exit_ExecuteSQLiteCmdSet
    End If
    
    
    'Create a SQLite Command file, and then parse it into the Python SQLite Command Parser for execution
    Dim SQLiteCmdFile_path As String
    Dim iFileNum_SQLiteCmd As Integer
    
    SQLiteCmdFile_path = [CurrentProject].[Path] & "\" & "SQLiteCmd.txt"
    iFileNum_SQLiteCmd = FreeFile()
    
    If FileExists(SQLiteCmdFile_path) = True Then
        Kill SQLiteCmdFile_path
    End If
    
    Open SQLiteCmdFile_path For Output As iFileNum_SQLiteCmd
    Print #iFileNum_SQLiteCmd, CmdSet
    Close #iFileNum_SQLiteCmd
    
    ShellCmd = "python " & [CurrentProject].[Path] & "\SQLiteCmdParser.py " & SQLiteDb_path & " " & SQLiteCmdFile_path
    Call ShellAndWait(ShellCmd, vbHide)

    Kill SQLiteCmdFile_path


Exit_ExecuteSQLiteCmdSet:
    ExecuteSQLiteCmdSet = FailedReason
    Exit Function

Err_ExecuteSQLiteCmdSet:
    Call ShowMsgBox(Err.Description)
    Resume Exit_ExecuteSQLiteCmdSet
    
End Function


'Append Table into a SQLite database
Public Function AppendTblToSQLite(Tbl_src_name As String, Tbl_des_name As String) As String
    On Error GoTo Err_AppendTblToSQLite
    
    Dim FailedReason As String
    
    If TableExist(Tbl_src_name) = False Then
        FailedReason = Tbl_src_name
        GoTo Exit_AppendTblToSQLite
    End If
    
    If TableExist(Tbl_des_name) = False Then
        FailedReason = Tbl_des_name
        GoTo Exit_AppendTblToSQLite
    End If
    
    
    'Create Db
    Dim TempDb_path As String
    TempDb_path = [CurrentProject].[Path] & "\TempDb.mdb"
    
    If FileExists(TempDb_path) = True Then
        Kill TempDb_path
    End If
    
    Call CreateDatabase(TempDb_path, dbLangGeneral)

    
    'Copy Table into the TempDb
    Dim SQL_cmd As String
    
    SQL_cmd = "SELECT * " & vbCrLf & _
                "INTO [" & Tbl_des_name & "]" & vbCrLf & _
                "IN '" & TempDb_path & "'" & vbCrLf & _
                "FROM [" & Tbl_src_name & "] " & vbCrLf & _
                ";"
    
    RunSQL_CmdWithoutWarning (SQL_cmd)


    'Convert TempDb into SQLite
    Dim SQLiteDb_path As String
    SQLiteDb_path = [CurrentProject].[Path] & "\TempDb.sqlite"
    
    If FileExists(SQLiteDb_path) = True Then
        Kill SQLiteDb_path
    End If
    
    Dim ShellCmd As String
    ShellCmd = "java -jar " & [CurrentProject].[Path] & "\mdb-sqlite.jar " & TempDb_path & " " & SQLiteDb_path
    Call ShellAndWait(ShellCmd, vbHide)
    
    SQL_cmd = "ATTACH """ & SQLiteDb_path & """ AS TempDb;" & vbCrLf & _
                "INSERT INTO [" & Tbl_des_name & "] SELECT * FROM TempDb.[" & Tbl_des_name & "];"
    
    Call ExecuteSQLiteCmdSet(GetLinkTblConnInfo(Tbl_des_name, "DATABASE"), SQL_cmd)
    
    Kill SQLiteDb_path
    Kill TempDb_path


Exit_AppendTblToSQLite:
    AppendTblToSQLite = FailedReason
    Exit Function

Err_AppendTblToSQLite:
    Call ShowMsgBox(Err.Description)
    Resume Exit_AppendTblToSQLite
    
End Function

[VBA] Remove all link tables

https://github.com/walter426/VbaUtilities/blob/master/AccessObjUtilities.bas

All .mdb and ODBC link tables will be deleted by below function

'Remove all link tables
Public Function RemoveLink() As String
    On Error GoTo Err_RemoveLink
    
    Dim FailedReason As String
    
    Dim tdf As TableDef

    For Each tdf In CurrentDb.TableDefs
        If tdf.Attributes = dbAttachedTable Or tdf.Attributes = dbAttachedODBC Then
            DoCmd.DeleteObject acTable, tdf.Name
        End If
    Next tdf

Exit_RemoveLink:
    RemoveLink = FailedReason
    Exit Function

Err_RemoveLink:
    FailedReason = Err.Description
    Resume Exit_RemoveLink
    
End Function

[VBA] Link Table Through Table Definition

https://github.com/walter426/VbaUtilities/blob/master/AccessObjUtilities.bas

As it is unable to add a link table directly by "CreateTableDef" method, so it needs to make below function to do so.

'Link Table Through Table Definition
Public Function LinkTblByTdf(Tbl_src_name As String, Tbl_des_name As String, str_conn As String) As String
    On Error GoTo Err_LinkTblByTdf
   
    Dim FailedReason As String
   
    DelTable (Tbl_des_name)
       
    With CurrentDb
        Dim tdf As TableDef
        Set tdf = .CreateTableDef(Tbl_des_name)

        tdf.Connect = str_conn
        tdf.SourceTableName = Tbl_src_name
       
        .TableDefs.Append tdf
        .TableDefs(Tbl_des_name).RefreshLink
       
    End With 'CurrentDb
       
    RefreshDatabaseWindow

Exit_LinkTblByTdf:
    LinkTblByTdf = FailedReason
    Exit Function

Err_LinkTblByTdf:
    FailedReason = Err.Description
    Resume Exit_LinkTblByTdf
   
End Function


Supplement:
My intention to write this function is to link SQLite database through ODBC.
Unfortunately, the MS Access application will be closed after it is linked to some SQLite table sometimes, with an error number, 462, which means "remote server machine not found.".
If the failure occurs, the only thing can do is to open the access application and re-link the table again.
i'm sorry that I did not find any fix or workaround to overcome this bug.

[SQLite] Convert MS ACCESS .mdb file into SQLite format by Java

https://code.google.com/p/mdb-sqlite/

Above is the initial java code project, pls read it for compiling and conversion.

But the latest version is moved to here now, https://github.com/paulproteus/mdb-sqlite.

Unfortunately, this version still has bug on the DATETIME conversion.

So I made below folk version for this bug fix.
https://github.com/walter426/mdb-sqlite

[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