[AccessD] Access with a MySQL database

MartyConnelly wrote:

> Something I came across from an ASP perspective
> http://www.aspfree.com/c/a/Microsoft-Access/An-Access-Front-End-to-MySQL/
>
> Barbara Ryan wrote:
>
>> Does anyone have any experience using an Access application as a 
>> front-end to a MySQL database back-end?  My client is interested in 
>> converting the Access back-end to either SQL Server or MySQL.  They 
>> currently do not own SQL Server but are using MySQL in their web 
>> application.
>>
>> Any thoughts (advantages, limitations, etc.)?
>>
>> Thanks,
>> Barb Ryan
>>  
>>
>
Hmph. I thought I answered this already, but it must have been a senior 
moment. I have been there and done that extensively, for the book (that 
was cancelled, and will soon re-emerge as a web site) my partner Peter 
Brawley and I wrote about MySQL.

In brief, the basic setup is dead simple. Download MyODBC from MySQL.com 
and install it. Copy your current app to a new file and start there. You 
also have to get the data from your current database to MySQL but for 
the moment I'll jump over that issue.

Run the linked tables wizard and choose odbc as the file type and then 
create a connection to your MySQL database. Check each table in the 
database manager before proceeding. Attempt to add a row. If you get a 
new row with everything marked #deleted#, then add a DateTime column to 
every such table and set its default to Now(). Repeat until success. Now 
load the forms one by one and see if they work.

That should do it. I may have skipped over a few trivialities but that's 
the nut.

Now back to the import problem. Assuming that all you have is Access and 
MySQL, you can do it this way: create the aforementioned MySQL database, 
then the MDB and the ODBC hook to it, then choose File/Get External 
Data/Import and then select the current Access database. Select all the 
tables and go. You may have data-type-conversion problems. Don't expect 
the first pass to be 100% successful. See what went wrong and fix it 
first, until finally the import works 100%.

After that, your data will all be in MySQL. You may at that point choose 
to revise your indexing strategies, or not. Compare the versions for 
performance.

HTH,
A.