access -> mysql

[sandeep_mur]

Hi,

I have a client with a huge database in access 2000. I intend to :

1. convert the Access db to MySQL db.

2. if possible continue using Access as a front end for the created MySQL db!

I tried using the MyODBC driver of MySQL but am unable to convert the Access db to mysql yet.. can anyone please post step by step instructions as to how i can achieve this??

If its possible at all to use MS access as the frontend (the client is not interested in phpMyAdmin) for the MySQL db, it wud be a great help..

thnx in adv,

cheers,sands

[Saggitarius]

Do this:
In access right click on your db you want to have in MySQL. Choose 'Export'. Than in file type choose 'Text file'. On the next window click 'Next'. Than radiobatton in 'Tab' and 'Text limiter' in 'None'. Than 'Next' and 'Done'.
Open MySQL console (often c:\mysql\bin\mysql.exe) and type:
use <your db>
load data infile "yourdisk:\yourpath\file.txt" into table yourtable;
That's all. 🙂

[sandeep_mur]

hi,

maybe a dumb question but i can only select tables and that too one at a time and export it.. and it results in exporting only the data...!!

guess dont work much with access..

also, any more tips on using access as the frontend for mysql??

thnx,
sands

[tribeof_1]

why would you use access as your frontend? because you can create the query screens? I saw some articles about linking the tables from access to mysql so that they both get updated.
Anyone can confirm this?

[BuzzLY]

Absolutely.

I am trying this myself, although I am trying to push my data / tables from Access to MySQL. I'm having problems with the primary keys being lost after exporting, but I'm working on it.

However, if you create your tables and relationships in MySQL, you can access them all in MS Access as linked tables. This way, you can build the queries you need in Access, but all the data is stored in MySQL.

I have only just begun working with this, and I am using Office XP Professional, so I don't know what older versions are able to do this. You first have to install MyODBC, which you can get from here. Once you have that set up, from within Access you simply click File...Get External Data>... Link Tables. It's pretty straightforward, and you can figure out how to do it by RTFM.

Seems pretty cool so far, and can be a nice way to edit your data if you don't want to create a PHP page to do so.