Access 2002 conversion to mySQL

[cbhead]

What is the best and easiest way to convert records in an Access database to mySQL?
Any tips would be appreciated.

 

[jedraw]

Export the Access data in some standard format like CSV or fixedText from Access table involved via Export Command/Wizard to a file ie table1.txt

Either, export the data in the form of Insert Statements, or
edit the table1.txt file to make INSERT statements.

You'll need SQL Insert statements (general format is)
INSERT Into tablename(fld1, fld2,.... fld3) Values( value1, value2,...value3);

Create the table structure in MySQL

Then import that file (table1.txt) into the proper MySQL table using MySQL INSERT function. In MySQL, click the table name, click INSERT, then enter/browse to the table1.txt file and click GO.

Good luck

 

[cbhead]

Thanks for the tips, really useful so far.

Just a couple of things though:-

1. How do you export the table in the form of INSERT statements without manually changing the text each time? I have about 300 records to export.

2.Importing the table1.txt file. How is this done? Is it only possible if using the mySQL Admin tool or can it be done using the Command Prompt as well.

Thanks.

 

[Zych]

I deal a lot with Access and MySQL and I do it a little bit different. Please note: I just pretend to be a SQL person.

I installed the MySQL ODBC connector and just link the table into my Access database. After this you can do almost anything you want.

- Zych

 

[PCSAARON]

Zych has the right idea... I have ported many access databases to MySQL and the first thing is to create the tables yourself using MySQLCC or some other of your fancy to add/modify the tables... There is a program out there that will actually transfer all of your access tables to MySQL and transfer 5 records (eval version) of each table into the mySQL tables... Not bad because it creates the tables for you, however, be weary of these programs,,, sometimes the fields are what the AUTHOR thinks they should be... Certain lengths or even the type of field that they think it should be transferred to may not be exactly what you want...

So in a nutshet, make the tables by hand and use access to copy the records (after you link the tables) and then paste them right into the MySQL linked tables... Be weary of date fields, in mySQL they are different...

Aaron

 

[EJKoeze]

Can this also be used to synchonize an Access 2000 database with a remote Mysql database. I'm new to this kind of matter and am in the process of setting up a website for a foundation. We want to extract data to a webpage from an online database but the person that will be maintaining the database does not know anything about databases (other that populatiing it).

I would like to know of a way to sync the databases automatically.

EJK

 

[Zych]

I am not sure how you would do this since it is a remote application. When you link a database in Access it needs to be browsed to. You might be able to use a link accross a VPN. Then you can just export or the linked database to another database in Access and work with it while you are offline from the VPN. You would need to check with your ISP if you can use a VPN or not.

- Zych

 

[JCruz063]

Guys,
I'm new to MySQL and I read the online manual which directed me to install MyODBC, create an ODBC data source and use the export function from Access itself to export the tables (one at a time) to MySQL. This is quite a simple process and it doesn't require me to create the tables in MySQL first. However, the replies in this post do suggest that the tables be created first in MySQL prior to importing the data. Why is this the case? Am I going in the wrong direction? What is the problem with the way I'm doing it? Thanks!

JC

_________________________________________________
To get the best response to a question, read faq222-2244.