Converting Access to MySQL
Luis
have going. I have a database in access 7.0 I would like to transfor all the
fields, tables, plus information it has to mysql running on a linux box (rh 7.0)
running mysql.
now how would i go about that?
is there a howto some were , plus is it hard to do.?
Thank you
Luis
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <mysql-th...@lists.mysql.com>
To unsubscribe, e-mail <mysql-unsubscribe-mysql=freebsd.csie...@lists.mysql.com>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Merlin
help email me direct (Remove spm filtering from email address)
Access 97 to rext then import. Past the script below as a module in access
then create a macro
RunCode export_mysql()
Quit
THEN SAVE DATABASE
NEXT RUN BATCHFILE (Copy underneath). I use Win NT, but sure you can
addapt...
Function export_mysql()
' 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 "d:\database\teldirt.txt" For Output As #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
THEN I RUN THIS BATCH FILE:
@echo off
REM Delete text files with previous data
d:
cd \database
if exist teldirt.txt del teldirt.txt
REM Create text files with Access 97 - Runs macro EXPORT in each MDB
call C:\Progra~1\Micros~1\Office\MSACCESS.EXE d:\database\teldirT.mdb /x
export
REM Move to the MySQL binaries directory
c:
cd\mysql\bin
REM Flush to make sure the databases are not being used
mysqladmin --user=root --password=8956095 flush-tables
REM Import data into the databases
mysql --user=root --password=xx TelDir < d:\database\teldirt.txt
REM All done, close command window!
exit
Luis <Lu...@paycom.net> wrote in message
news:9aaqpa$2r17$1...@FreeBSD.csie.NCTU.edu.tw...
Bryce Jackson
This is an easy one.
Go here ftp://ftp.informate.co.nz/pub/ and download one of the scripts, the
instructions are in the script. It will create a text file that has the sql
commands needed to create and fill your mySQL tables with your Access info.
You can exec the script from the mySQL prompt.
-bryce
on 4/2/01 3:52 PM, Luis at Lu...@paycom.net wrote:
> Hello to all, I was wondering if anyone could help me out with a little
> project i
> have going. I have a database in access 7.0 I would like to transfor all the
> fields, tables, plus information it has to mysql running on a linux box (rh
> 7.0)
> running mysql.
>
> now how would i go about that?
> is there a howto some were , plus is it hard to do.?
>
> Thank you
>
> Luis
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <mysql-th...@lists.mysql.com>
> To unsubscribe, e-mail
> <mysql-unsubscribe-bjackson=sockeyecr...@lists.mysql.com>
Luis
reading tells me to download MyODBC or i could make a txt file out of access
when exporting.
If i try to inport a all the fields from access to mysql do i have to create a
database plus tables that are the same as in windows?
I'm sorry i'm a newbie at all this
Luis
understand is how do i connect threw access now?
I'm a little confuse, can anyone help me understand how MyODBC works.
luis
Steve Ruby
>
> Good morning all, last night I installed MyODBC on my laptop. What i dont
> understand is how do i connect threw access now?
>
> I'm a little confuse, can anyone help me understand how MyODBC works.
>
> luis
It is an ODBC driver for Mysql.
Create a databsae in mysql
go to the ODBC control pannel
create a Datasource that usign my odbc and points to your mysql database
now you can use anything that understands ODBC to work with your database,
for example you can link it to access.
Hamid Hashemi Golpayegani
You must go to control panel of windows in Data Sources(ODBC) section and add your DSN of you new MySQL database and then you can explor the database by browsing your ODBC avilable databases . You can check MyODBC manual for this .
On Tue, Apr 03, 2001 at 06:55:52AM -1000, Luis wrote:
> Good morning all, last night I installed MyODBC on my laptop. What i dont
> understand is how do i connect threw access now?
>
> I'm a little confuse, can anyone help me understand how MyODBC works.
>
>
> luis
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <mysql-th...@lists.mysql.com>
> To unsubscribe, e-mail <mysql-unsubscribe-hamid=morv...@lists.mysql.com>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
--
Regards
============================================================
/ Seyyed Hamid Reza / WINDOWS FOR NOW !! /
/ Hashemi Golpayegani / Linux for future , FreeBSD for ever /
/ Morva System Co. / ------------------------------------- /
/ Network Administrator/ ha...@morva.net , ICQ# : 42209876 /
===========================================================
Luis
In the ODBC Data Source 32 bit I went in user dsn and added user data source
called irm the driver is mysql
not this is were i dont understand .
In the field that says Windwos DSN name I put "irm"
MySQL I put the ip address were mysql is located.
user name I put :root
the password : password
then i sayed ok.
after this what do I do.
Do i go to access and try to connect that way or how do i connect to the
database irm that i have in mysql.
Luis
Luis
Also if i just created a database in mysql called customer
would i put the that name in "MySQL database Name"
Scott Meesseman
way youre goin' >> Follow on screen inst.
scott
At 03:06 PM 4/3/01, you wrote:
>Ok i'm still a little bit lost. I have a database in MySQL called "irm"
>
>In the ODBC Data Source 32 bit I went in user dsn and added user data source
>called irm the driver is mysql
>
>not this is were i dont understand .
>
>In the field that says Windwos DSN name I put "irm"
>MySQL I put the ip address were mysql is located.
>user name I put :root
>the password : password
>
>then i sayed ok.
>
>after this what do I do.
>
>Do i go to access and try to connect that way or how do i connect to the
>database irm that i have in mysql.
>
>Luis
>
>
>---------------------------------------------------------------------
>Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <mysql-th...@lists.mysql.com>
>To unsubscribe, e-mail
><mysql-unsubscribe-richard=carso.ka...@lists.mysql.com>