MS Access and MySQL
September 15, 2008
(By Ian Andrew from Canberra with a little bit of help from Garry Robinson.)
Setting the Scene
Last year we took on an elderly, undocumented Access 97 database linked to a MySQL back-end. The task was to fix existing problems, upgrade to Access 2007 and Vista, and bring the functionality in line with current business practices. It would involve an independent re-build (mainly off site) so that existing operations could continue unhindered.
Concurrent upgrading changes werent ideal: when a problem arose, we had to work out whether it was due to a flaw in the original database, or 97 to 2007 differences, or some aspect of the Access to ODBC driver to MySQL linkage. But we had no choice and with Allen Brownes comprehensive list of Access 2007 issues, we marched on.
MySQL was completely new to us. Moreover, while we knew an updated back-end would be needed, we didnt know whether it should remain MySQL or change to Access. So we wanted to keep both options open.
An early problem, before we could start work off-site, was that there were two sorts of links from the front-end to MySQL tables. In the first case, the tables were linked via the MySQL ODBC driver: we could re-link to the development back-end and queries, forms/reports and VBA references continued to function. In the second case, pass through queries and ADO connections referred directly to the on-site MySQL server and database: these would not work off-site. To simplify matters, the latter were changed to normal queries and DAO references to the linked tables.
Finding Out About MySQL
Our aim was to set up an off-site copy of the working MySQL database, an Access version of the same back-end, and a front-end that could link to either.
So how to do it. We started with Google. MS Access and MySQL found a range of references: a non-exhaustive list of tools and advice we found useful follows:
GUI tools (Administrator, Migration Tool and Query Browser):
Most of the advice was from a MySQL point of view, but we found these all worth reading.
University College London Using MySQL from
Peter Lavin An
Access Front-End to MySQL:
Doing MS Access:
The MySQL Manual
has a section on ODBC and Access:
Allen Brownes comprehensive list of Access 2007 issues http://allenbrowne.com/Access2007.html
On with the Story
As its unlikely youll find yourself in our situation, for the rest of this narrative well assume you are an Access developer who wants to migrate an Access back-end to a local MySQL server for development and testing purposes. Youll maintain your own solution and are happy to use the Community Server (free).
Step 1 Install MySQL
Go to http://dev.mysql.com/downloads/ to download the server, MySQL GUI tools and MySQL ODBC driver. You have a choice of server: 5.0, 5.1 or 6.0 (Alpha). Weve used 5.1 and 6.0 without any problems. Then install all three.
Points to note during installation:
We also installed Bullzip Access to MySQL and Dreamcoder for later use.
Step 2 Move Tables from Access to MySQL
Either use Bullzip to transfer back-end tables to MySQL, or open your database and export the table via ODBC.
Points to note:
Step 3 Modify MySQL Tables (Columns)
Open Dreamcoder (easier to use at this stage) or MySQL Administrator (one of the GUI tools). If you are used to SQL Server, think of these as the Enterprise or SQL server management studio express interfaces.
Connect to the transferred/exported database.
Open each table in turn and check:
Step 4 Setup Your MySQL Backup Systems
Open MySQL Administrator and back up your MySQL database. This will save the schema and data to a .sql file. If you want to set up the database on another computer, install a MySQL server on that machine then restore a copy of the .sql file to the new server.
Step 5 Link Front-End to MySQL
Open your Access front-end. If you are already linked to an Access backend, you cant use the linked table manager to change to an ODBC data source. So delete the links to the Access backend and link, via ODBC, to the MySQL back-end. Similarly, once linked to an ODBC data source, the linked table manager offers only a choice of ODBC sources.
When setting up the ODBC data source, in ODBC Configure Advanced Flags1, tick Return Matching Rows and Allow Big Results.
Observations so far:
Our next step is to test the effect on performance of using Access pass through queries and MySQL queries (views).
Questions and Answers
Did you need any special skills to work with MySQL?
No. Access developer experience plus the MySQL information available online and from the Manual was more than enough to get us started on this migration project. If we go beyond small-business use, no doubt well be attending MySQL training sessions.
Why migrate to MySQL?
Listed benefits include:
Why stick with Access?
The client was happy with his MySQL back-end and weve been able to re-develop it for him.
At its current size an Access backend also would be suitable, but there is no major benefit to be gained by developing it.
We have learnt a new server/database system that may be useful in developing future applications.
About my colleague
Ian Andrew is developer from NSW in Australia that has been helping me out for the last two years. In that time, Ian has worked with airlines, mining companies and a qualitative research firm and has racked up good Access solutions that are easy to use.
» See All Articles by Columnist Garry Robinson