#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Web Developer
    Posts
    6
    Rep Power
    0
    is there another way to convert some M.S.ACCESS databases to MySQL format? besides, the ODBC way? do I have to write a some script program to do it?
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    Perth West Australia
    Posts
    757
    Rep Power
    15
    Both your posts are on the same day! - patience will help no end in your programming as well as interacting with others!

    If all else fails - just export from access to a comma seperated file (via excel if you wish) then load data infile in MySQL/ use phpMyAdmin , or make a php routine to add the select statements to your csv and query the db that way.

    ------------------
    Simon Wheeler
    FirePages -DHTML/PHP/MySQL
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    The MySQL website has a nice colleciton of links to third-party tools, including ExportSQL, an Access module to dump a full database to a mySQL-runnable script. Much better than just exporting CVS, because it handles table creation, type conversion, etc...
    http://www.cynergi.net/exportsql/
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Web Developer
    Posts
    6
    Rep Power
    0
    thanks for your advices. I tried to compile the module your provided on that web-site, but for the simple statement:

    Dim cdb As Database

    I got VB complied error like this " User-defined type not defined"

    I am not familiar with VB, but I can find out the same statement in the help doucmentation of VB, could you please help me out with it?

    forgive my bad experience in VB.

    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rycamor:
    The MySQL website has a nice colleciton of links to third-party tools, including ExportSQL, an Access module to dump a full database to a mySQL-runnable script. Much better than just exporting CVS, because it handles table creation, type conversion, etc...
    http://www.cynergi.net/exportsql/
    [/quote]

  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Web Developer
    Posts
    6
    Rep Power
    0
    I added the DAO references and solved the problem. sorry for my stupid question, thanks anyway.


    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by seanma:
    thanks for your advices. I tried to compile the module your provided on that web-site, but for the simple statement:

    Dim cdb As Database

    I got VB complied error like this " User-defined type not defined"

    I am not familiar with VB, but I can find out the same statement in the help doucmentation of VB, could you please help me out with it?

    forgive my bad experience in VB.

    [/quote]

  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Seattle, WA USA
    Posts
    23
    Rep Power
    0
    I have blended a few code samples with my own stuff and built a module for exporting M$ Access to a SQL database. Once you create the YourFileName.sql file, you'll be able to redirect it to mysql.

    mysql < YourFileName.sql


    Note: Your table structure must be to SQL standards for the output to work. Access allows for sloppy table names, indexes...etc.

    Here is the module...just cut and paste it into a new module. The open dialog box will not work in Win2000..I'm woring on that one.

    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    Option Compare Database
    Option Explicit


    Type tagOPENFILENAME
    lStructSize As Long
    hWndOwner As Long
    hInstance As Long
    strFilter As String
    strCustomFilter As String
    nMaxCustFilter As Long
    NFilterIndex As Long
    strFile As String
    nMaxFile As Long
    strFileTitle As String
    nMaxFileTitle As Long
    strInitialDir As String
    strTitle As String
    Flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    strDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
    End Type

    Declare Function glr_apiGetOpenFileName Lib "comdlg32.dll" _
    Alias "GetOpenFileNameA" (ofn As tagOPENFILENAME) As Boolean
    Declare Function glr_apiGetSaveFileName Lib "comdlg32.dll" _
    Alias "GetSaveFileNameA" (ofn As tagOPENFILENAME) As Boolean
    Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

    Global Const glrOFN_READONLY = &H1
    Global Const glrOFN_OVERWRITEPROMPT = &H2
    Global Const glrOFN_HIDEREADONLY = &H4
    Global Const glrOFN_NOCHANGEDIR = &H8
    Global Const glrOFN_SHOWHELP = &H10
    Global Const glrOFN_NOVALIDATE = &H100
    Global Const glrOFN_ALLOWMULTISELECT = &H200
    Global Const glrOFN_EXTENSIONDIFFERENT = &H400
    Global Const glrOFN_PATHMUSTEXIST = &H800
    Global Const glrOFN_FILEMUSTEXIST = &H1000
    Global Const glrOFN_CREATEPROMPT = &H2000
    Global Const glrOFN_SHAREAWARE = &H4000
    Global Const glrOFN_NOREADONLYRETURN = &H8000
    Global Const glrOFN_NOTESTFILECREATE = &H10000
    Global Const glrOFN_NONETWORKBUTTON = &H20000
    Global Const glrOFN_NOLONGNAMES = &H40000
    Global Const glrOFN_EXPLORER = &H80000
    Global Const glrOFN_NODEREFERENCELINKS = &H100000
    Global Const glrOFN_LONGNAMES = &H200000


    Function GetDBDir() As String

    On Error GoTo GetDBDirErr

    Dim dbCurrent As Database
    Dim strDbName As String
    Dim strProcName As String

    strProcName = "GetDBDir"

    Set dbCurrent = CurrentDb
    strDbName = dbCurrent.Name

    Do While Right$(strDbName, 1) <> ""
    strDbName = Left$(strDbName, Len(strDbName) - 1)
    Loop

    GetDBDir = UCase$(strDbName)

    GetDBDirDone:
    On Error GoTo 0
    Exit Function

    GetDBDirErr:
    Select Case Err
    Case Else
    MsgBox "Error#" & Err.Number & ": " & Err.Description, vbOKOnly, strProcName
    Resume GetDBDirDone
    End Select

    End Function
    Function glrCommonFileOpenSave( _
    Optional ByRef Flags As Variant, _
    Optional ByVal InitialDir As Variant, _
    Optional ByVal Filter As Variant, _
    Optional ByVal FilterIndex As Variant, _
    Optional ByVal DefaultExt As Variant, _
    Optional ByVal FileName As Variant, _
    Optional ByVal DialogTitle As Variant, _
    Optional ByVal OpenFile As Variant) As Variant

    Dim ofn As tagOPENFILENAME
    Dim strFilename As String
    Dim strFileTitle As String
    Dim fResult As Boolean

    ' Give the dialog a caption title.
    If IsMissing(InitialDir) Then InitialDir = GetDBDir()
    If IsMissing(Filter) Then Filter = ""
    If IsMissing(FilterIndex) Then FilterIndex = 1
    If IsMissing(Flags) Then Flags = 0&
    If IsMissing(DefaultExt) Then DefaultExt = ""
    If IsMissing(FileName) Then FileName = ""
    If IsMissing(DialogTitle) Then DialogTitle = "Export To MySQL"
    If IsMissing(OpenFile) Then OpenFile = True

    ' Allocate string space for the returned strings.
    strFilename = Left(FileName & String(256, 0), 256)
    strFileTitle = String(256, 0)

    ' Set up the data structure before you call the function
    With ofn
    .lStructSize = Len(ofn)
    .hWndOwner = Application.hWndAccessApp
    .strFilter = Filter
    .NFilterIndex = FilterIndex
    .strFile = strFilename
    .nMaxFile = Len(strFilename)
    .strFileTitle = strFileTitle
    .nMaxFileTitle = Len(strFileTitle)
    .strTitle = DialogTitle
    .Flags = Flags
    .strDefExt = DefaultExt
    .strInitialDir = CurDir

    .hInstance = 0
    .strCustomFilter = ""
    .nMaxCustFilter = 0
    .lpfnHook = 0
    End With

    If OpenFile Then
    fResult = glr_apiGetOpenFileName(ofn)
    Else
    fResult = glr_apiGetSaveFileName(ofn)
    End If

    If fResult Then

    If Not IsMissing(Flags) Then Flags = ofn.Flags
    glrCommonFileOpenSave = glrTrimNull(ofn.strFile)
    Else
    glrCommonFileOpenSave = Null
    End If
    End Function

    Function glrAddFilterItem(strFilter As String, _
    strDescription As String, Optional varItem As Variant) As String

    If IsMissing(varItem) Then varItem = "*.*"
    glrAddFilterItem = strFilter & _
    strDescription & vbNullChar & _
    varItem & vbNullChar

    End Function

    Function glrTrimNull(ByVal strItem As String) As String
    Dim intPos As Integer

    intPos = InStr(strItem, vbNullChar)
    If intPos > 0 Then
    glrTrimNull = Left(strItem, intPos - 1)
    Else
    glrTrimNull = strItem
    End If

    End Function

    Sub ConvertAccess2MySQL()
    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
    Dim vOutputFile As Variant, sDBDir As String, sDBName As String, sFilter As String, LFlags As Long, sMsg As String

    Set dbase = CurrentDb()

    sDBDir = GetDBDir()
    sDBName = Mid$(dbase.Name, Len(sDBDir) + 1, (Len(dbase.Name) - Len(sDBDir)) - 4)
    'Do Until (Right$(sDBName, 1) = "/")

    sFilter = glrAddFilterItem(sFilter, "SQL (*.sql)", "*.sql")
    LFlags = glrOFN_HIDEREADONLY Or glrOFN_HIDEREADONLY Or glrOFN_NOCHANGEDIR

    'Opens a dialog box to prompt for new files (doesn't work with Win2000)
    vOutputFile = glrCommonFileOpenSave(OpenFile:=True, Filter:=sFilter, Flags:=LFlags, DialogTitle:="Loaction for new SQL text file.")


    If IsNull(vOutputFile) Then
    ' User pressed Cancel.
    sMsg = "You need to select an export location...process will stop."
    MsgBox sMsg, vbOKOnly + vbCritical, "Convert Access To MySQL"
    Exit Sub
    Else
    vOutputFile = glrTrimNull(vOutputFile)
    End If


    'Open the file to export the defintions and data to. Change this to suit your needs ****

    Open vOutputFile For Output As #1

    Print #1, "# Converted from MS Access to mysql "
    Print #1, "# Exported On:" & Now()
    Print #1, ""
    Print #1, "CREATE DATABASE " & sDBName & ";"
    Print #1, "USE " & sDBName & ";"
    '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

    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)



    Print #1, ""
    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 Sub

    [/code]


    ------------------
    Carl Sweetland
    carl@sweetlands.com
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    London, London, UK
    Posts
    3
    Rep Power
    0
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by seanma:
    is there another way to convert some M.S.ACCESS databases to MySQL format? besides, the ODBC way? do I have to write a some script program to do it?[/quote]

    Export each table as a tab delimited text file. Write a short script and use any one of the mysql programs (e.g. mysql < script, or for each table something like mysqlimport table_name).

    See mysql documentation.

    Jethro

  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    Garza Garcia, NL, Mexico
    Posts
    1
    Rep Power
    0
    1. Create your MySQL Database with all the required tables
    2. Open your existing Access database
    3. Attach or link (through ODBC) all the MySQL tables to your Access database
    4. Copy all records from your Access tables to your attached MySQL tables
    5. Done (you may wish to unlink the attached tables)

    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by seanma:
    is there another way to convert some M.S.ACCESS databases to MySQL format? besides, the ODBC way? do I have to write a some script program to do it?[/quote]

Similar Threads

  1. Random Questions grouped by answers
    By paCkeTroUTer in forum PHP Development
    Replies: 8
    Last Post: December 5th, 2003, 06:26 PM
  2. JS quiz with textbox answers?
    By hakukumo in forum HTML Programming
    Replies: 1
    Last Post: November 3rd, 2003, 10:47 PM
  3. A simple way to make a quiz with textbox answers?
    By hakukumo in forum Java Help
    Replies: 1
    Last Post: November 3rd, 2003, 06:17 AM
  4. Building Searchable Database For Tech Support Quick Answers
    By Killer1nstinct in forum MySQL Help
    Replies: 1
    Last Post: January 29th, 2002, 03:24 PM
  5. Replies: 5
    Last Post: September 12th, 2001, 06:21 PM

IMN logo majestic logo threadwatch logo seochat tools logo