[AccessD] Access with a MySQL database

Paul Rodgers wrote:

>Hi Arthur
>
>I'd be very much obliged if you could let us/me know  when your words on
>MsSql are published on a website.
>
>Thanks very much for the info here which is most useful. 
>
>Cheers
>
>paul
>
>-----Original Message-----
>From: Arthur Fuller [mailto:artful at rogers.com]
>Sent: 28 October 2004 02:54
>To: Access Developers discussion and problem solving
>Subject: Re: [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.
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>  
>
Thanks for asking. It shouldn't be a whole lot longer now; we're 
proofing it currently. (We = my long-time partner Peter Brawley and I.) 
I'm not sure how much of the "book" will be of interest to Access 
developers. One chatper in the ODBC section takes you step by step 
through porting Northwind to MySQL and then modifying the Access app to 
talk to the MySQL Northwind database. Actually there's not much to it, 
and I can thumbnail it here.

There's really only one thing to worry about. Before converting, it's 
useful to add a timestamp column to each table -- just make it a 
DateTime column and default it to "=Now()".

1. Install MySQL and make sure it works.
2. Create a Northwind database in MySQL.
3. Install MyODBC.
4. Copy Northwind.MDB to NorthwindMySQL.MDB. Delete all the tables.
5. Create a DSN that uses MyODBC and points to the Northwind MySQL database.
6. Open the NorthwindMySQL.mdb and then the linked tables manager and 
point to the DSN.
7. Import all the tables.
8. You'll have problems with a few queries. I forget which ones offhand 
and don't have the chapter in front of me, but the chapter details which 
ones need changing and specifies the changes.

Arthur