Conversion of Access97 to MySQL

[Al Kolff]

Found this at http://icc.ucdavis.edu/iccdocs/joes.htm
It does work and is so much easier than anything else I tried.

PROBLEM: How to move all tables in an Access Database to MySql (data and
structure at once.)
BEST WAY: Don't mess with SQL export scripts. Establish an ODBC DSN
connection to MySQL using MyODBC, then use this run this tiny code in an MS
Access module (replace "NAMEOFDSNCONNECTIONHERE" with the name you specify
under the ODBC DSN tab in the ODBC control panel):

Function ExportToSQL()
Dim dbName As String, db As Database, i As Integer, nameext As String
Set db = CurrentDb()
nameext = "DATABASE=" & InputBox("Enter the name of the MySQL database for
this export.
_All tables except system and attached tables will be exported! These will
overwrite existing
_tables in MySQL so be sure!!!")
If nameext = "DATABASE=" Then End
For i = 0 To db.TableDefs.Count - 1
If db.TableDefs(i).Connect = "" Then 'if not an attached table
If Left(db.TableDefs(i).Name, 4) <> "MSys" Then
DoCmd.TransferDatabase acExport, "ODBC Database", _
"ODBC;DSN=NAMEOFDSNCONNECTIONHERE;UID=root;PWD=;LA NGUAGE=us_english;" _
& nameext, acTable, db.TableDefs(i).Name, db.TableDefs(i).Name
End If
End If
Next i
db.Close: Set db = Nothing
End Function

--
God Bless you,
Al Kolff

"There is a God we want, and there is a God who is. They are not the same
God. The turning point of our lives is when we stop seeking the God we want
and start seeking the God who is."
Patrick Morley
Seven Seasons of the Man in the Mirror
p184

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.721 / Virus Database: 477 - Release Date: 7/16/04