MS Access and MySQL

(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 weren’t 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 Browne’s comprehensive list of Access 2007 issues, we marched on.

Something New

MySQL was
completely new to us. Moreover, while we knew an updated back-end would be
needed, we didn’t 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:

Tools

Bullzip Access
to MySQL:
http://www.bullzip.com/products/a2m/info.php

Dreamcoder MySQL
administrator:
http://www.sqldeveloper.net/download.html

MySQL ‘official’
GUI tools (Administrator, Migration Tool and Query Browser):
http://dev.mysql.com/downloads/gui-tools/5.0.html

Advice

Most of the
advice was from a MySQL point of view, but we found these all worth reading.

University College London ‘Using MySQL from … Access’:
http://www.ucl.ac.uk/is/mysql/access/

Peter Lavin ‘An
Access Front-End to MySQL’:
http://www.aspfree.com/c/a/Microsoft-Access/An-Access-Front-End-to-MySQL/

Roland Bouman
“Doing MS Access’:
http://rpbouman.blogspot.com/2005/12/doing-ms-access.html.

The MySQL Manual
has a section on ODBC and Access:
http://dev.mysql.com/doc/refman/5.1/en/myodbc-examples-tools-with-access.html.

Allen Browne’s comprehensive list of Access 2007
issues http://allenbrowne.com/Access2007.html

On with the Story

As it’s unlikely you’ll find yourself in
our situation, for the rest of this narrative we’ll assume you are an Access
developer who wants to migrate an Access back-end to a local MySQL server for
development and testing purposes. You’ll 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). We’ve used 5.1 and 6.0 without any problems. Then
install all three.

Points to note during installation:

  • Your firewall will have to be set to allow
    connections through Port 3306.
  • We chose to run MySQL as a service (command line
    is an option).
  • MySQL installation defaults to username/login = root
    and server = localhost.
  • MySQL offers a choice of storage engine/table
    type – the main ones are MySAM or InnoDb. The latter seems more like Access,
    with transactions and foreign keys, so at the moment we’re leaning that way.
  • User and security options are available. Don’t
    forget to record any passwords.

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:

  • MySQL does not recognise Access functions used
    as field defaults (eg Now() and Date() in Date/Time
    fields): they may be dropped or the table rejected. Recommend you remove them
    before transfer/export.
  • Access autonumber fields are not identical
    to MySQL auto_increment. Bullzip will
    convert on transfer, ODBC will not.
  • Access data types will be converted to similar
    MySQL data types. However, there is a greater range in MySQL (refer to the
    Manual) and you may wish to change them later.
  • An Access ‘ole object’ becomes a ‘blob’ in MySQL. Due to an OLE Server problem, we changed blobs to longtext
    (memo in Access).

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.

Figure 1. Dreamcoder ‘Database – Connect’

Figure 1. Dreamcoder ‘Database – Connect’

Open each table in turn and check:

  • autonumber fields(columns) are auto_increment.
  • There is a primary key (one or more fields) for
    each table (in Dreamcoder, Create – New Constraint). Otherwise, you’ll
    have to nominate when linking and/or the linked table will not be updateable.
  • Field defaults are correct. We checked for
    consistency with front-end data entry defaults.
  • Whether Null
    should be allowed or not.
  • Data types are as desired.
  • There is a timestamp field with default current_time (usually the last
    field in the table).

Figure 2. Edit Tables

Figure 2. Edit Tables

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.

Figure 3. Backup MySQL

Figure 3. Backup MySQL

Step 5 – Link Front-End to MySQL

Open your Access front-end. If you are
already linked to an Access backend, you can’t 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
.

Figure 4. ODBC Data Source

Figure 4. ODBC Data Source

Performance

Observations so far:

  • Access 97 with a MySQL backend was significantly
    faster than Access 2007 with the same backend.
  • The original combo and list box row sources used
    the row source query builder. For some reason replacing them with user-created
    queries improved speed dramatically.
  • In the current configuration, Access 2007 with
    an Access backend is a little faster than with the MySQL backend.

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 we’ll be attending MySQL training sessions.

Why migrate to MySQL?

Listed benefits include:

  • Cost (free).
  • Open source.
  • Scalability (not subject to Access’s 2GB limit).
  • Heavy duty multiple user capability.
  • Security (secure password and privilege system).
  • Portability (Windows, Linux, …) for your back-end
    data
  • Connectivity (Access front-end, or accessible
    over the Web using a range of client programs). Note that you had better
    discuss security with a web professional before doing this.

Why stick with Access?

  • Easier to develop and maintain because you only
    need to know the one tool.

Conclusion

The client was happy with his MySQL back-end
and we’ve 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

Garry Robinson
Garry Robinson
Garry Robinson was the editor of www.vb123.com and the Access Unlimited Newsletter. He wrote a book on Microsoft Access Protection and Security and has written many articles for the Smart Access Magazine. Amongst Garry's online contributions is an Access 2007 Security paper for MSDN at http://msdn2.microsoft.com/en-us/library/bb421308.aspx. When Garry isn't working, he likes playing golf, snorkeling and being dragged along to kids soccer and kids basketball and kids golf and kids surf lifesaving and the second son hasn't even started sports yet!

Latest Articles