Create MySQL Dump from Access DB

migrateAccessToMYsql_A.vba

Public Sub createMySqlDump(Filename As String)
    ' Declaration of Variables
    
    Dim j As Integer
    Dim k As Integer
    
    Dim SQL As String
    Dim SQL_INSERT As String
    
    Dim db As Database
    Dim tables As TableDefs
    Dim table As TableDef
    Dim lf As fields
    Dim f As field
    Dim objIndex As Index
    
    Dim rs As Recordset
    
    'init Database
    Set db = CurrentDb
    Set tables = db.TableDefs
    
    'iterate over all Tables
    For Each table In tables
         
        If Left(table.name, 4) <> "MSys" Then
            ' if table is not system Table
             
            SQL = ""        'Init SQL
            SQL_INSERT = ""
    
            Dim pkf As String 'Field for primary Keys
            Dim tablename As String 'field for tablename
            tablename = Replace(table.name, " ", "_") 'correct Tablename
            
            
            ' Writing directly into file instead of storing in String saves about 80-90% of runtime.
            ' Prepare File for Output
            ' set and open file for output
            Dim intF As Integer
            intF = FreeFile()
            Open Filename & tablename & ".sql" For Output As intF
            
            
            'Drop exisitng Table
            Print #intF, "DROP TABLE IF EXISTS " & tablename & ";" & vbNewLine
            'Create New Table
             Print #intF, "CREATE TABLE " & tablename & "(" & vbNewLine
            
            'Find Primary Key
            For Each objIndex In table.Indexes
                If objIndex.Primary Then
                    pkf = objIndex.fields
                    pkf = Replace(pkf, "+", "")
                    Exit For
                End If
            Next
            
            'Prepare insert Statement
            SQL_INSERT = "INSERT INTO " & tablename & "("
            j = 0
            'Iterate over all fields
            For Each f In table.fields
                 Print #intF, vbTab & f.name & " " & getFieldType(f.Type, f.name = pkf)
                 Print #intF, IIf(j < table.fields.Count - 1, ",", "") & vbNewLine
                SQL_INSERT = SQL_INSERT & f.name
                SQL_INSERT = SQL_INSERT & IIf(j < table.fields.Count - 1, ",", "")
                j = j + 1
            Next
            'finalize SQL
            Print #intF, ");" & vbNewLine
            
            Print #intF, SQL_INSERT & ") " & vbNewLine & " VALUES "
            
            'Prepare Selecte Statement
            Dim sql_select As String
            sql_select = "select * from " & tablename & " ORDER BY " & Replace(pkf, ";", ",")
            'open Recordset
            Set rs = db.OpenRecordset(sql_select)
            'If Data is found
            If rs.RecordCount > 0 Then
                rs.MoveFirst
                'loop for all rows
                Do Until rs.EOF
                    Print #intF, vbNewLine & vbTab & "("
                    j = 0
                    'loop for all fields
                    For Each f In table.fields
                        Print #intF, "'" & cleanValue(IIf(IsNull(rs(f.name).value), "", rs(f.name).value)) & "'" & IIf(j < table.fields.Count - 1, ",", "")
                        j = j + 1
                    Next
                    Print #intF, ")"
                    
                    rs.MoveNext
                    If Not rs.EOF Then
                        Print #intF, ","
                    End If
                Loop
                Print #intF, vbNewLine & ";" & vbNewLine & vbNewLine
            End If
             
            Close #intF
    
        End If
        Set rs = Nothing
    Next
   

End Sub

whats "getFieldType" please, as a function it doesn't exist