{"id":76,"date":"2012-03-16T21:53:23","date_gmt":"2012-03-17T02:53:23","guid":{"rendered":"http:\/\/en.latindevelopers.com\/ivancp\/?p=76"},"modified":"2014-03-19T20:17:43","modified_gmt":"2014-03-20T01:17:43","slug":"ms-access-to-mysql-with-relationships","status":"publish","type":"post","link":"http:\/\/en.latindevelopers.com\/ivancp\/2012\/ms-access-to-mysql-with-relationships\/","title":{"rendered":"Converting MS Access to MySQL with relationships"},"content":{"rendered":"

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:<\/p>\n

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:<\/p>\n

'Put this function in new\/existing MS-Access module.\r\n'\r\n' Version History:\r\n'\r\n' 2014-02-09 - Seamus Casey\r\n' a modification to Ivan's handy Access to MySQL relationship\/constraint generator\r\n'\r\n' changes include:\r\n'  1) skip Access system tables (TableDefAttributeEnum.dbSystemObjec)\r\n'  2) add support for cascading updates\/deletes\r\n'\r\n\r\nPublic Sub printRelations()\r\n    Dim sql, fk As String\r\n    Dim I, J As Integer\r\n    Dim db As Database\r\n    Dim Table As TableDef\r\n    Dim TableName As String\r\n    \r\n    ' grab a reference to this once, otherwise when we retrieve a table below,\r\n    ' we will get an 'Object Invalid or No Longer Set' error.\r\n    Set db = CurrentDb\r\n    \r\n    For I = 0 To db.Relations.Count - 1\r\n    \r\n        Set Table = db.TableDefs.Item(db.Relations(I).Table)\r\n        \r\n        If ((Table.Attributes And TableDefAttributeEnum.dbSystemObject) = 0) Then\r\n           \r\n           sql = \"ALTER TABLE `\" & db.Relations(I).ForeignTable & _\r\n               \"` ADD CONSTRAINT `\" & db.Relations(I).Name & \"` FOREIGN KEY (\"\r\n           fk = \"(\"\r\n           For J = 0 To db.Relations(I).Fields.Count - 1\r\n               sql = sql & \"`\" & db.Relations(I).Fields(J).ForeignName & \"` ,\"\r\n               fk = fk & \"`\" & db.Relations(I).Fields(J).Name & \"` ,\"\r\n           Next J\r\n    \r\n           sql = Left(sql, Len(sql) - 1)\r\n           fk = Left(fk, Len(fk) - 1)\r\n           fk = fk & \")\"\r\n           sql = sql & \") REFERENCES `\" & db.Relations(I).Table & \"`\" & fk\r\n           \r\n           If (db.Relations(I).Attributes And RelationAttributeEnum.dbRelationUpdateCascade) Then\r\n               sql = sql & \" ON UPDATE CASCADE\"\r\n           End If\r\n           \r\n           If (db.Relations(I).Attributes And RelationAttributeEnum.dbRelationDeleteCascade) Then\r\n               sql = sql & \" ON DELETE CASCADE\"\r\n           End If\r\n           \r\n           sql = sql & \";\"\r\n    \r\n           Debug.Print sql\r\n        End If\r\n    Next I\r\nEnd Sub<\/pre>\n
Gist Source<\/a><\/strong><\/p>\n
To run the adobe code go to intermediate windows (Ctrl+G) and execute: printRelations<\/code> and  copy the generated SQL code.<\/p>\n
\"\"<\/a><\/p>\n
Enjoy!<\/p>\n
(*)MySQL Migration Toolkit is discontinued but is still available from mirrors like: 
\nhttp:\/\/mirrors.dotsrc.org\/mysql\/Downloads\/MySQLGUITools\/<\/p>\n
Share this:<\/h3>