I’ve been migrating a few databases away from Access recently, moving towards more stable RDMS’.  I thought I’d quickly share 2 bits of code that I created to try and simplify the process.  These are geared towards exporting to MySQL, but can easily be adapted to any RDMS of your choosing with minor tweaks.

I didn’t like the idea of using random applications found online for which I have no clue what’s under the hood, how or what is being done with the data… So I set off to create a ‘simple’ VBA solution myself.

Both of these bits of code are not polished off (don’t even have error handling!), and don’t support all the data types, but for me that isn’t a concern as I don’t use attachments, multivalued fields, …  (One more reason never to use them in the first place, they make exporting much more complicated)

Exporting the Table

The second set of functions are used to export the table design so the table can be created in MySQL.

'Only works properly with single field PK Tables
Public Function MySQL_Local_GenCreateTableStatement(ByVal sTableName As String) As String
    Dim db                    As dao.Database
    Dim tdf                   As dao.TableDef
    Dim fld                   As dao.Field
    Dim result                As String
    Dim sPK                   As String

    Set db = CurrentDb
    Set tdf = db.TableDefs(sTableName)

    result = result & "DROP TABLE IF EXISTS `" & sTableName & "`;" & vbCrLf & vbCrLf
    result = result & "CREATE TABLE " & tdf.Name & "(" & vbCrLf

    ' Build field statements
    For Each fld In tdf.Fields
        result = result & MySQL_GenCreateField(db, sTableName, fld.Name)
    Next fld

    ' GET PK Field to define the INDEX
    result = result & MySQL_GenPKStatement(tdf)

    ' Remove trailing comma
    If Right(result, 1) = "," Then
        result = Mid(result, Len(result) - 1) & vbCrLf
    End If

    ' Return the result
    MySQL_Local_GenCreateTableStatement = result & ") ENGINE=innodb DEFAULT CHARSET=utf8;" & vbCrLf

    Set tdf = Nothing
    Set db = Nothing
End Function

Private Function MySQL_GenCreateField(ByVal db As DAO.Database, ByVal sTableName As String, ByVal sfld As String) As String
    Dim fld                   As DAO.Field
    Dim ADOXCat               As Object  'ADOX.Catalog
    Dim ADOXCol               As Object  'ADOX.Column
    Dim result                As String
    Dim i                     As Long

    Set fld = db.TableDefs(sTableName).Fields(sfld)

    Set ADOXCat = CreateObject("ADOX.Catalog")
    Set ADOXCat.ActiveConnection = CurrentProject.Connection
    Set ADOXCol = ADOXCat.Tables(sTableName).Columns(sfld)

    result = "  `" & fld.Name & "`"

    Select Case fld.Type
        Case dbDate, dbTime, dbTimeStamp
            result = result & " DATETIME"
        Case dbMemo    'Memo/Long Text, Hyperlinks
            result = result & " LONGTEXT"
        Case dbByte
            result = result & " TINYINT(3) UNSIGNED"
        Case dbInteger
            result = result & " INTEGER"
        Case dbLong
            result = result & " INTEGER"
            'result = result & " INT(11)"
        Case dbNumeric, dbDecimal
            result = result & " DECIMAL(" & ADOXCol.Precision & ", " & ADOXCol.NumericScale & ")"
        Case dbSingle, dbFloat
            result = result & " FLOAT"
        Case dbDouble
            result = result & " DOUBLE"
        Case dbGUID
            result = result & " GUID"
        Case dbBoolean
            result = result & " TINYINT(1)"
        Case dbCurrency
            result = result & " DECIMAL(19,4)"
        Case dbText
            result = result & " VARCHAR(" & fld.Size & ")"
        Case Else
            result = result & " ????"
    End Select

    If fld.DefaultValue <> "" Then
        If fld.Type = dbBoolean Then
            result = result & " DEFAULT " & IIf(fld.DefaultValue = "NO", 0, 1)
        Else
            result = result & " DEFAULT " & fld.DefaultValue
        End If
    End If

    If (fld.Attributes And dbAutoIncrField) Then
        result = result & " AUTO_INCREMENT"
    End If

    If fld.Required Then result = result & " NOT NULL"

    MySQL_GenCreateField = result & "," & vbCrLf

    Set ADOXCol = Nothing
    Set ADOXCat = Nothing
End Function

Public Function MySQL_GenPKStatement(ByVal tdf As dao.TableDef) As String
    Dim sPkFlds               As String
    Dim aPkFlds()             As String
    '    Dim i As Integer

    sPkFlds = Replace(tdf.Indexes("PrimaryKey").Fields, "+", "")
    aPkFlds = Split(sPkFlds, ";")
    If UBound(aPkFlds) = 0 Then
        MySQL_GenPKStatement = "  PRIMARY KEY (`" & sPkFlds & "`)" & vbCrLf
    End If
End Function

Then you can simply run it using:

? MySQL_Local_GenCreateTableStatement("Table1")

and you get an output such as:

DROP TABLE IF EXISTS `Table1`;

CREATE TABLE Table1(
  `PK` INTEGER AUTO_INCREMENT,
  `GUIDVal` GUID DEFAULT GenGUID(),
  `ShortText` VARCHAR(255) NOT NULL,
  `LongText` LONGTEXT,
  `Number_LongInterger` INTEGER DEFAULT 0,
  `Number_Single` FLOAT DEFAULT 0,
  `Number_Double` DOUBLE DEFAULT 0,
  `Number_Decimal` DECIMAL(19, 2) DEFAULT 0,
  `Number_Byte` TINYINT(3) UNSIGNED DEFAULT 0,
  `DateTime_` DATETIME,
  `Currency_` DECIMAL(19,4) DEFAULT 0,
  `YesNo_` TINYINT(1) DEFAULT 0,
  `Hyperlink_` LONGTEXT,
  PRIMARY KEY (`PK`)
) ENGINE=innodb DEFAULT CHARSET=utf8;

Exporting the Table Data

The second function is to export the actual data so that it can be imported into the table by creating a series of INSERT statements for each row of data.

Public Function MySQL_GenDataInsertStatements(ByVal sTableName As String) As String
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim rsExport              As DAO.Recordset
    Dim aFieldTypes()         As String
    Dim sSQL                  As String
    Dim sSQLFields            As String
    Dim iNoFields             As Long
    Dim i                     As Long

    Set db = CurrentDb
    Set rs = db.OpenRecordset(sTableName, dbOpenSnapshot)

    iNoFields = rs.Fields.Count - 1    'zero base the count
    ReDim aFieldTypes(iNoFields)

    For i = 0 To iNoFields
        If i > 0 Then sSQLFields = sSQLFields & ", "
        Select Case rs.Fields(i).Type
            Case dbDate, dbTime, dbTimeStamp
                aFieldTypes(i) = "Date"
                sSQLFields = sSQLFields & "'"" & [" & rs.Fields(i).Name & "] & ""'"
            Case dbGUID, dbMemo, dbText    'this includes hyperlinks
                aFieldTypes(i) = "Text"
                If (rs.Fields(i).Attributes And dbHyperlinkField) = dbHyperlinkField Then
                    'Hyperlinks=>extract url
                    sSQLFields = sSQLFields & "'"" & HyperlinkPart(Nz([" & rs.Fields(i).Name & "]), 5) & ""'"
                Else
                    sSQLFields = sSQLFields & "'"" & [" & rs.Fields(i).Name & "] & ""'"
                End If
            Case dbByte, dbInteger, dbLong, dbNumeric, dbDecimal, dbSingle, dbFloat, dbDouble, dbCurrency
                aFieldTypes(i) = "Numeric"
                sSQLFields = sSQLFields & """ & [" & rs.Fields(i).Name & "] & """
            Case dbBoolean
                aFieldTypes(i) = "Boolean"
                sSQLFields = sSQLFields & """ & Replace([" & rs.Fields(i).Name & "], -1, 1) & """
        End Select
    Next

    sSQL = "SELECT ""INSERT INTO `" & sTableName & "` VALUES("
    sSQL = sSQL & sSQLFields
    sSQL = sSQL & ")"" AS InsertCmd FROM [" & sTableName & "]"

    Set rsExport = db.OpenRecordset(sSQL, dbOpenSnapshot)
    With rsExport
        If .RecordCount > 0 Then
            Do While Not .EOF
                Debug.Print rsExport![InsertCmd]
                .MoveNext
            Loop
        End If
    End With

    rsExport.Close
    Set rsExport = Nothing
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function

Then you can simply run it using:

? MySQL_GenDataInsertStatements("Table1")

and you get an output such as:

INSERT INTO `Table1` VALUES(1, '{EF50BB14-10C1-4145-BD8A-8C53EEB298B9}', 'ShortText', 'LongText', 12, 12.1, 235.775446121, 121.54, 0, '5/10/2022 11:56:00 AM', 0, 1, 'https://www.devhut.net')
INSERT INTO `Table1` VALUES(2, '{C3C43CBB-BD7E-4AB9-8E49-046B6F909AEE}', 'Some text', 'Longer text entry', 2578, 15.5546, 222421335.2255, 12.55, 0, '5/10/2022 7:27:00 PM', 2540, 0, 'https://www.google.ca')

Now these work fine for my needs, but could use more work to make them more complete. That said, I thought I’d share them in case they could help others and maybe give someone else a healthy starting point should they be trying to do the same thing.

Other Resources on the Subject

Based on a recent comment, here are a few links worth looking over if you don’t want to go the VBA route:

MySQL :: MySQL Workbench Manual :: 10.4 Microsoft Access Migration

How to use MySQL Workbench to migrate data from Access.

Access To MySQL

Bullzip Utility Source - Free software for printing PDF documents and converting Microsoft Access databases to MySQL.