Mailinglisten-Archive |
This is a multi-part message in MIME format.
------=_NextPart_000_0015_01C0C387.5650C8B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
> Ich suche nach einem Tool, dass die SQL-Syntax, die MS Access verwendet in
> eine für mySQL verständliche Version umwandelt. Hat irgendjemand eine
Ahnung
> wo man soetwas findet??
Hallo,
Anbei ein kleines script, welches auch funktioniert (access 2000 --> mysql)
Die Anleitung steht im text selber. Bitte genau beachten.
Bei weiteren Fragen mir bitte eine kurze Mail schicken.
--
Best Regards / Beste Gruesse
Denis Mettler
http://www.denis-mettler.de
------=_NextPart_000_0015_01C0C387.5650C8B0
Content-Type: text/plain;
name="convert_access2k_mysql.txt"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="convert_access2k_mysql.txt"
'Exports the database contents into a file in mysql format
'IS NOT SELECTIVE! (exports ALL tables)
=20
'INSTRUCTIONS
=20
'Paste this function into a Microsoft Access module of a database =
that 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.
=20
'Access 2000
'------------
'Under Tools, References ensure you have the following selected:
'Visual Basic for Applications
'Microsoft Access 9.0 Object Library
'Microsoft DAO 3.6 Object Library
'OLE Automation
'Microsoft Visual Basic for Applications Extensibility 5.3
=20
'*** Unselect ActiveX Data Objects references!!!
=20
=20
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 =3D CurrentDb()
=20
'Open the file to export the defintions and data to. Change this to =
suit your needs ****
=20
Open "c:\temp\dump.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-2000"
Print #1, ""
'Go through the table definitions
For i =3D 0 To dbase.TableDefs.Count - 1
=20
=20
' Let's take only the visible tables
=20
If ((dbase.TableDefs(i).Attributes And DB_SYSTEMOBJECT) Or =
(dbase.TableDefs(i).Attributes And DB_HIDDENOBJECT)) Then
=20
Else
=20
' We DROP the table if it already exists
' and then create it again
=20
tname =3D "" & dbase.TableDefs(i).Name
=20
'remove spaces from tablename
=20
For j =3D 1 To Len(tname)
If j < Len(tname) Then
If Mid$(tname, j, 1) =3D " " Then
s =3D Left$(tname, j - 1)
s =3D s & "" & Right$(tname, Len(tname) - j)
j =3D j + 1
found =3D True
tname =3D s
End If
End If
Next j
=20
'restrict tablename to 19 chars
=20
tname =3D Left$(tname, 19)
=20
=20
'comment out these lines if the table doesn't exist or else =
create it first
=20
Print #1, ""
Print #1, ""
Print #1, "DROP TABLE IF EXISTS " & tname & ";"
=20
=20
Print #1,
Print #1, "CREATE TABLE " & tname & " ("
=20
=20
' Step through all the fields in the table
=20
For fd =3D 0 To dbase.TableDefs(i).Fields.Count - 1
=20
=20
Dim tyyppi As String, pituus As Integer, comma As String
Select Case dbase.TableDefs(i).Fields(fd).Type
Case DB_BOOLEAN
tyyppi =3D "SMALLINT"
Case DB_INTEGER
tyyppi =3D "SMALLINT"
Case DB_BYTE
tyyppi =3D "TINYBLOB"
Case DB_LONG
If dbase.TableDefs(i).Fields(fd).Attributes =3D =
17 Then
tyyppi =3D "INT AUTO_INCREMENT"
Else
tyyppi =3D "INT"
End If
Case DB_DOUBLE
tyyppi =3D "DOUBLE"
Case DB_SINGLE '
tyyppi =3D "REAL"
Case DB_CURRENCY
tyyppi =3D "DOUBLE (8,4)"
Case DB_TEXT
pituus =3D dbase.TableDefs(i).Fields(fd).Size
tyyppi =3D "CHAR (" & pituus & ")"
Case dbAutoIncrField
' tyyppi =3D "INT NOT NULL AUTO_INCREMENT"
=20
'Access Date fields are set as the mysql date type - =
you can change this to
'DATETIME if you prefer.
=20
Case DB_DATE
tyyppi =3D "DATE"
Case DB_MEMO, DB_LONGBINARY
tyyppi =3D "BLOB"
=20
End Select
=20
=20
'Print the field definition
=20
'remove spaces from fieldname
=20
stuff =3D "" & dbase.TableDefs(i).Fields(fd).Name
=20
'we had a table called Index which mysql doesn't like
=20
If stuff =3D "Index" Then stuff =3D "Indexm"
=20
For j =3D 1 To Len(stuff)
If j < Len(stuff) Then
If Mid$(stuff, j, 1) =3D " " Then
s =3D Left$(stuff, j - 1)
s =3D s & "" & Right$(stuff, Len(stuff) - j)
j =3D j + 1
found =3D True
stuff =3D s
End If
End If
Next j
=20
stuff =3D Left$(stuff, 19)
=20
'not null
k =3D 0
=20
For Each idx In dbase.TableDefs(i).Indexes
If (dbase.TableDefs(i).Indexes(k).Fields(0).Name =3D =
dbase.TableDefs(i).Fields(fd).Name) Then
tyyppi =3D tyyppi & " NOT NULL"
Exit For
End If
k =3D k + 1
Next
=20
'default value
=20
If (Not =
(IsNull(dbase.TableDefs(i).Fields(fd).DefaultValue)) And =
dbase.TableDefs(i).Fields(fd).DefaultValue <> "") Then
=20
If dbase.TableDefs(i).Fields(fd).Required =3D False =
Then
tyyppi =3D tyyppi & " NOT NULL "
End If
=20
If Left$(dbase.TableDefs(i).Fields(fd).DefaultValue, =
1) =3D Chr(34) Then
tyyppi =3D tyyppi & " DEFAULT '" & =
Mid$(dbase.TableDefs(i).Fields(fd).DefaultValue, 2, =
Len(dbase.TableDefs(i).Fields(fd).DefaultValue) - 2) & "'"
Else
tyyppi =3D tyyppi & " DEFAULT " & =
dbase.TableDefs(i).Fields(fd).DefaultValue
End If
=20
End If
=20
'print out field info
comma =3D ","
=20
If fd =3D dbase.TableDefs(i).Fields.Count - 1 Then
=20
If dbase.TableDefs(i).Indexes.Count =3D 0 Then
comma =3D ""
Else
comma =3D ","
End If
End If
=20
Print #1, " " & stuff & " " & tyyppi & comma
=20
Next fd
=20
=20
'primary key and other index declaration
=20
k =3D 0
=20
=20
For Each idx In dbase.TableDefs(i).Indexes
=20
'Check Primary property
=20
k =3D k + 1
=20
If idx.Primary Then
istuff =3D " PRIMARY KEY ("
Else
istuff =3D " KEY ("
End If
=20
f =3D 0
=20
For Each fld In idx.Fields
f =3D f + 1
iname =3D fld.Name
=20
For j =3D 1 To Len(iname)
If j < Len(iname) Then
If Mid$(iname, j, 1) =3D " " Then
s =3D Left$(iname, j - 1)
s =3D s & "" & Right$(iname, Len(iname) - j)
j =3D j + 1
found =3D True
iname =3D s
End If
End If
Next j
=20
istuff =3D istuff & iname
=20
If f < idx.Fields.Count Then
istuff =3D istuff & ","
End If
=20
Next fld
=20
If k < dbase.TableDefs(i).Indexes.Count Then
=20
Print #1, istuff & "),"
Else
Print #1, istuff & ")"
=20
End If
=20
=20
Next idx
=20
=20
=20
Print #1, ");"
Print #1, ""
=20
=20
Dim recset As Recordset
Dim row As String, it As String
Dim is_string As String, reccount As Integer, x As Integer
=20
Set recset =3D dbase.OpenRecordset(dbase.TableDefs(i).Name)
=20
reccount =3D recset.RecordCount
=20
=20
If reccount <> 0 Then
=20
' Step through the rows in the table
=20
recset.MoveFirst
Do Until recset.EOF
=20
row =3D "INSERT INTO " & tname & " VALUES ("
=20
' Go through the fields in the row
=20
For fd =3D 0 To recset.Fields.Count - 1
=20
is_string =3D ""
stuff =3D "" & recset.Fields(fd).Value
=20
Select Case recset.Fields(fd).Type
Case DB_BOOLEAN
=20
'true fields are set to 1, false are set to 0
=20
If recset.Fields(fd).Value =3D True Then
stuff =3D "0"
Else
stuff =3D "1"
End If
=20
Case DB_TEXT, DB_MEMO, 15, DB_LONGBINARY
is_string =3D "'"
Case DB_DATE
is_string =3D "'"
=20
'format date fields to YYYY-MM-DD. You may want =
to add time formatting as
'well if you have declared DATE fields as =
DATETIME
=20
If stuff <> "" And Not (IsNull(stuff)) Then
stuff =3D Format(stuff, "YYYY-MM-DD")
End If
Case Else
=20
'default empty number fields to 0 - comment this =
out if you want
=20
If stuff =3D "" Then
stuff =3D "0"
End If
End Select
=
=20
'**** escape single quotes
x =3D InStr(stuff, "'")
=20
While x <> 0
s =3D Left$(stuff, x - 1)
s =3D s & "\" & Right$(stuff, Len(stuff) - x + =
1)
stuff =3D s
x =3D InStr(x + 2, stuff, "'")
Wend
=20
'**** convert returns to <br>'s
=20
x =3D InStr(stuff, Chr(13))
=20
While x <> 0
s =3D Left$(stuff, x - 1)
If x =3D Len(stuff) Then
s =3D s & "<br>"
Else
s =3D s & "<br>" & Right$(stuff, Len(stuff) =
- x - 1)
End If
=20
stuff =3D s
x =3D InStr(x + 2, stuff, Chr(13))
Wend
=20
row =3D row & is_string & stuff & is_string
=20
If fd < recset.Fields.Count - 1 Then
row =3D row & ","
End If
Next fd
=20
' Add trailers and print
=20
row =3D row & ");"
Print #1, row
=20
' Move to the next row
=20
recset.MoveNext
Loop
=20
recset.Close
Set recset =3D Nothing
=20
End If
End If
Next i
=20
=20
=20
Close #1
=20
dbase.Close
Set dbase =3D Nothing
End Function
------=_NextPart_000_0015_01C0C387.5650C8B0--
---
*** Weitere Infos zur Mailingliste und MySQL unter http://www.4t2.com/mysql
php::bar PHP Wiki - Listenarchive