Has anyone converted M$ Access to MySQL?

jkinz at rcn.com jkinz at rcn.com
Thu Sep 5 22:14:26 EDT 2002


On Thu, Sep 05, 2002 at 07:40:01PM -0000, Bill Horne wrote:
> TWIMC,
> 
> I'm doing volunteer work at an organization which may 
> choose to convert an existing M$ Access database to 
> open source, such as MySQL or Postgresql.
> 
> If anyone on the list has had experience with such a 
> change, please email me privately to discuss.
> 
Bill I've used Postgresql and its pretty good, didn't have to convert from
access though.
Here's some info on putting access => Postgresql:
http://www.linuxjournal.com/article.php?sid=5416


or this script to dump an access Db to mysql format:
from http://www.linuxselfhelp.com/mysql/manual_Contrib.html

<<<<<<<<<<<<<<   WARNING: UNWRAPPED TEXT        >>>>>>>>>>>>>>
Function export_mysql()
    
    ' Exports the database contents into a file in mysql format
    ' IS NOT SELECTIVE! (exports ALL tables)
    
    ' version 1.00  August 1997
    
    ' INSTRUCTIONS
    
    'Paste this function into an Access module of a database which has the
    'tables you want to export. Create a macro with the function RunCode and the
    'argument export_mysql (). Run the macro to start the export.
    
    
    Dim dbase As DATABASE, tdef As Recordset, i As Integer, fd As Integer, tname As String, j As Integer, iname As String
    Dim s As String, found As Integer, stuff As String, idx As Index, k As Integer, f As Integer, fld As Field, istuff As String

    Set dbase = CurrentDb()
    
    'Open the file to export the defintions and data to. Change this to suit your needs ****
    
    Open "c:\temp\mysqldump.txt" For Output As #1

    Print #1, "# Converted from MS Access to mysql "
    Print #1, "# by Brian Andrews, (c) InforMate (www.informate.co.nz), brian at informate.co.nz, 1997"
    Print #1, ""

    'Go through the table definitions


    For i = 0 To dbase.TableDefs.Count - 1
    
         
        ' Let's take only the visible tables
        
        If ((dbase.TableDefs(i).Attributes And DB_SYSTEMOBJECT) Or (dbase.TableDefs(i).Attributes And DB_HIDDENOBJECT)) Then
        
        Else
            
            ' We DROP the table if it already exists
            ' and then create it again
            
                    tname = "" & dbase.TableDefs(i).Name
                    
                       'remove spaces from tablename
            
                        For j = 1 To Len(tname)

                        If j < Len(tname) Then

                        If Mid$(tname, j, 1) = " " Then


                        s = Left$(tname, j - 1)
                        s = s & "" & Right$(tname, Len(tname) - j)
                        j = j + 1
                        found = True
                        tname = s

                        End If
                        End If

                        Next j
            
            'restrict tablename to 19 chars
            
            tname = Left$(tname, 19)
            
            
            'comment out these lines if the table doesn't exist or else create it first
            
            Print #1, ""
            Print #1, ""
            Print #1, "DROP TABLE " & tname & "\g"
            
            
            Print #1,
            Print #1, "CREATE TABLE " & tname & "("
            
          
            ' Step through all the fields in the table
            
            For fd = 0 To dbase.TableDefs(i).Fields.Count - 1
            
                        
                Dim tyyppi As String, pituus As Integer, comma As String
                Select Case dbase.TableDefs(i).Fields(fd).Type
                    Case DB_BOOLEAN
                        tyyppi = "SMALLINT"
                    Case DB_INTEGER
                        tyyppi = "SMALLINT"
                    Case DB_BYTE
                        tyyppi = "TINYBLOB"
                    Case DB_LONG
                        tyyppi = "INT"
                    Case DB_DOUBLE
                        tyyppi = "DOUBLE"
                    Case DB_SINGLE '
                        tyyppi = "REAL"
                    Case DB_CURRENCY
                        tyyppi = "DOUBLE (8,4)"
                    Case DB_TEXT
                         pituus = dbase.TableDefs(i).Fields(fd).Size
                         tyyppi = "CHAR (" & pituus & ")"
                    Case dbAutoIncrField
                        tyyppi = "INT NOT NULL AUTO_INCREMENT"
                    
                    'Access Date fields are set as the mysql date type - you can change this to
                    'DATETIME if you prefer.
                    
                    Case DB_DATE
                         tyyppi = "DATE"
                    Case DB_MEMO, DB_LONGBINARY
                         tyyppi = "BLOB"
                                     
                End Select
                
            
                'Print the field definition
                    
                'remove spaces from fieldname
                
                       stuff = "" & dbase.TableDefs(i).Fields(fd).Name
                
                'we had a table called Index which mysql doesn't like
                
                        If stuff = "Index" Then stuff = "Indexm"
                        
                        For j = 1 To Len(stuff)

                        If j < Len(stuff) Then

                        If Mid$(stuff, j, 1) = " " Then


                        s = Left$(stuff, j - 1)
                        s = s & "" & Right$(stuff, Len(stuff) - j)
                        j = j + 1
                        found = True
                        stuff = s

                        End If
                        End If

                        Next j
               
               stuff = Left$(stuff, 19)
               
               'not null
                If dbase.TableDefs(i).Fields(fd).Required = True Then
                    tyyppi = tyyppi & " NOT NULL "
                End If
                    
                'default value
                
                If (Not (IsNull(dbase.TableDefs(i).Fields(fd).DefaultValue)) And dbase.TableDefs(i).Fields(fd).DefaultValue <> "") Then
                    
                    If dbase.TableDefs(i).Fields(fd).Required = False Then
                        tyyppi = tyyppi & " NOT NULL "
                    End If
                    
                    If Left$(dbase.TableDefs(i).Fields(fd).DefaultValue, 1) = Chr(34) Then
                        tyyppi = tyyppi & " DEFAULT '" & Mid$(dbase.TableDefs(i).Fields(fd).DefaultValue, 2, Len(dbase.TableDefs(i).Fields(fd).DefaultValue) - 2) & "'"
                    Else
                        tyyppi = tyyppi & " DEFAULT " & dbase.TableDefs(i).Fields(fd).DefaultValue
                    End If
                    
                End If
                
            'print out field info
                comma = ","
                
                If fd = dbase.TableDefs(i).Fields.Count - 1 Then
                
                    If dbase.TableDefs(i).Indexes.Count = 0 Then
                        comma = ""
                    Else
                        comma = ","
                    End If
                End If
                
                Print #1, "     " & stuff & " " & tyyppi & comma
            
            Next fd
                  
                
            'primary key and other index declaration
               
               k = 0
               
               
               For Each idx In dbase.TableDefs(i).Indexes
            
            'Check Primary property
            
               k = k + 1
               
                If idx.PRIMARY Then
                    istuff = "     PRIMARY KEY ("
                Else
                    istuff = "     KEY ("
                End If
                
                    f = 0
                    
                    For Each fld In idx.Fields
                        f = f + 1
                        iname = fld.Name
                        
                        For j = 1 To Len(iname)

                        If j < Len(iname) Then

                        If Mid$(iname, j, 1) = " " Then


                        s = Left$(iname, j - 1)
                        s = s & "" & Right$(iname, Len(iname) - j)
                        j = j + 1
                        found = True
                        iname = s

                        End If
                        End If

                        Next j
                        
                        istuff = istuff & iname
                        
                        If f < idx.Fields.Count Then
                            istuff = istuff & ","
                        End If
                        
                    Next fld
                    
                    If k < dbase.TableDefs(i).Indexes.Count Then
                    
                        Print #1, istuff & "),"
                    Else
                        Print #1, istuff & ")"
                                          
                    End If
                    
                
                Next idx
    
                                          
                
            Print #1, ")\g"
            Print #1, ""
        
               
            Dim recset As Recordset
            Dim row As String, it As String
            Dim is_string As String, reccount As Integer, x As Integer
            
            Set recset = dbase.OpenRecordset(dbase.TableDefs(i).Name)
            
            reccount = recset.RecordCount
            
            
            If reccount <> 0 Then
            
            ' Step through the rows in the table
            
            recset.MoveFirst
            Do Until recset.EOF
                 
                row = "INSERT INTO " & tname & " VALUES ("
                
                ' Go through the fields in the row
                
                For fd = 0 To recset.Fields.Count - 1
                           
                    is_string = ""
                    stuff = "" & recset.Fields(fd).Value
                    
                    Select Case recset.Fields(fd).Type
                    Case DB_BOOLEAN
                        
                        'true fields are set to 1, false are set to 0
                        
                        If recset.Fields(fd).Value = True Then
                            stuff = "0"
                        Else
                            stuff = "1"
                        End If
                        
                    Case DB_TEXT, DB_MEMO, 15, DB_LONGBINARY
                        is_string = "'"
                    Case DB_DATE
                        is_string = "'"
                        
                        'format date fields to YYYY-MM-DD. You may want to add time formatting as
                        'well if you have declared DATE fields as DATETIME
                        
                        If stuff <> "" And Not (IsNull(stuff)) Then
                            stuff = Format(stuff, "YYYY-MM-DD")
                        End If
                    Case Else
                        
                        'default empty number fields to 0 - comment this out if you want
                        
                        If stuff = "" Then
                            stuff = "0"
                        End If
                    End Select
                                                                          
              '**** escape single quotes

                    x = InStr(stuff, "'")
                    
                    While x <> 0
                        s = Left$(stuff, x - 1)
                        s = s & "\" & Right$(stuff, Len(stuff) - x + 1)
                        stuff = s
                        x = InStr(x + 2, stuff, "'")
                    Wend
                    
                 '**** convert returns to <br>'s
                 
                 x = InStr(stuff, Chr(13))
                    
                    While x <> 0
                        s = Left$(stuff, x - 1)
                        s = s & "<br>" & Right$(stuff, Len(stuff) - x - 1)
                        stuff = s
                        x = InStr(x + 2, stuff, Chr(13))
                    Wend
                    
                    row = row & is_string & stuff & is_string
                    
                    If fd < recset.Fields.Count - 1 Then
                        row = row & ","
                    End If
                Next fd
                
                ' Add trailers and print
                
                row = row & ")\g"
                Print #1, row
                
                ' Move to the next row
                
                recset.MoveNext
            Loop
        
            recset.Close
            Set recset = Nothing
        
        End If
    End If
    Next i
    
  
    
    Close #1
    
    dbase.Close
    Set dbase = Nothing

End Function

-- 
Jeff Kinz, Director, Emergent Research,  Hudson, MA.  "jkinz at ultranet.com" 
copyright 1995-2002.  Use restricted to non-UCE uses. Any other use is an 
acceptance of the offer at http://www.ultranet.com/~jkinz/policy.html.
"jkinz at rcn.com" copyright 2002.  Use is restricted. Any use is an 
acceptance of the offer at http://users.rcn.com/jkinz/policy.html.

    ()B¬_                                    -o)
    //\         eLviintuaxbilse            /\\    
    V_/_                                  _\_V   



More information about the Discuss mailing list