Converting MS Access to MySQL with relationships

Posted on March 16, 2012 Comments

I have used tools like MySQL Migration Toolkit (*) and Bullzip’s Access To MySQL utility, both do a excellent job but without relationships. We can spend a lot of hours to identifying and creating relationships until now:

I have write a VBA script to identify MS-Access relationships and create MySQL code with SQL-CREATE sentences, it will be useful after migration process using any free tool:

'Put this function in new/existing MS-Access module.
'
' Version History:
'
' 2014-02-09 - Seamus Casey
' a modification to Ivan's handy Access to MySQL relationship/constraint generator
'
' changes include:
'  1) skip Access system tables (TableDefAttributeEnum.dbSystemObjec)
'  2) add support for cascading updates/deletes
'

Public Sub printRelations()
    Dim sql, fk As String
    Dim I, J As Integer
    Dim db As Database
    Dim Table As TableDef
    Dim TableName As String
 
    ' grab a reference to this once, otherwise when we retrieve a table below,
    ' we will get an 'Object Invalid or No Longer Set' error.
    Set db = CurrentDb
 
    For I = 0 To db.Relations.Count - 1
 
        Set Table = db.TableDefs.Item(db.Relations(I).Table)
 
        If ((Table.Attributes And TableDefAttributeEnum.dbSystemObject) = 0) Then
 
           sql = "ALTER TABLE `" & db.Relations(I).ForeignTable & _
               "` ADD CONSTRAINT `" & db.Relations(I).Name & "` FOREIGN KEY ("
           fk = "("
           For J = 0 To db.Relations(I).Fields.Count - 1
               sql = sql & "`" & db.Relations(I).Fields(J).ForeignName & "` ,"
               fk = fk & "`" & db.Relations(I).Fields(J).Name & "` ,"
           Next J
 
           sql = Left(sql, Len(sql) - 1)
           fk = Left(fk, Len(fk) - 1)
           fk = fk & ")"
           sql = sql & ") REFERENCES `" & db.Relations(I).Table & "`" & fk
 
           If (db.Relations(I).Attributes And RelationAttributeEnum.dbRelationUpdateCascade) Then
               sql = sql & " ON UPDATE CASCADE"
           End If
 
           If (db.Relations(I).Attributes And RelationAttributeEnum.dbRelationDeleteCascade) Then
               sql = sql & " ON DELETE CASCADE"
           End If
 
           sql = sql & ";"
 
           Debug.Print sql
        End If
    Next I
End Sub

Gist Source

To run the adobe code go to intermediate windows (Ctrl+G) and execute: printRelations and copy the generated SQL code.

Enjoy!

(*)MySQL Migration Toolkit is discontinued but is still available from mirrors like:

http://mirrors.dotsrc.org/mysql/Downloads/MySQLGUITools/

Related Posts :

Comments

  1. hi, great tool! i’m getting the following error, though: Compile error: Expected variable or procedure, not module. what am i doing wrong? am using msa2010. thanks much!

  2. Hello Ivan!

    First, I want to say that this is extremely useful!
    I found your code via StackOverflow.
    I am using the BullZip migration tool, which is great, except for the relationship/constraint creation.

    I have made a couple modifications to this:
    1) skip Access system tables (TableDefAttributeEnum.dbSystemObjec)
    2) add support for cascading updates/deletes

    To ensure proper formatting, I put the code in PasteBin:
    http://pastebin.com/y6rrMEvK

    Cheers,
    Seamus

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" cssfile="">