since nobody answers me, got a new quesition.

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?

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

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/

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]

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]

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

<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

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]