'Doing' MS Access

I've just come home visiting one of my Inter Access co-workers.

Barbara is a very capable Oracle Developer, Information Analyst and BI Consultant. She enjoys the craftmanship so much that she still finds time to help out friends managing their little IT solutions.

For example, one of her friends owns a sports clothing store. She built them an order entry system in that popular little Database by Microsoft known as Access.

As you might expect, the system has grown over time, and has had too little administrative attention. Now, it's about 30Mb of data. This may not seem much, until you try to run a query. Size really is a relative measure.

What's worse, the number of users has grown too. Again, it may not seem much, but five users that are simultaneously working with one Access database is really about the limit. At least, it seems to be the case here.

She decided that it might be a good idea to try porting the application's backend to MySQL, and she asked me if I had any experience in porting such an application. 'Yeah..' I said, lying through my teeth of course, '..sure I have. I do that stuff all the time.'. 'Lyer..', she said, '..but do you think you can give me a hand?'.

Of course, I said yes. I really never did this, although I was aware of the builtin functionalities of the migration toolkit to do the legwork. And I'm always eager to goahead and try these kinds of things, especially since this is sort of a real, mission-critical application. (Just because we're doing this in our spare time, and that this concerns only a small sportsclothes shop does not change the fact that this application is mission-critical and that the migration should be planned and executed carefully).

Luckily, the application was already built in two separate chunks. The front end is an Access application: It contains some stored Queries ('Views'), some Forms and some VB code. The backend is also an access database. This one is worth 35 Mb of base tables. The front end communicates with the backend through a so-called 'linked table'. (For the MySQL techies: this offers the same functionality as the FEDERATED storage engine in MySQL)

In our first endeavours, we used the MySQL Migration Toolkit (download: here) to reverse engineer the database schema and to load the data into MySQL. This really went remarkebly well. We used default options in most of the cases, and I can only conclude that this is a very good way to import an access database into MySQL.

Then, we used the MySQL ODBC driver to link the existing Ms Access front end to the MySQL backend database. The immediate first impression regarding this solution is that query performance is much better than in the old situation, so that's a thumbs up for MySQL. And we didn't even do any tuning sofar.

A bit of a bummer is that we did encounter problems inserting data into tables with socalled AutoNumber columns (such a column is very much like an auto_increment in mysql).
In the fist place, the migration toolkit did NOT automatically map those to mysql auto_increment columns. This was fixed soon enough though. We just added the auto_increment attribute ourselves.

A more serious problem was that Access did not synchronize well upon insertion of a new row. The row would be put into the database, but immediately after insertion, the access front end would show the row as #deleted#. Refreshing the view on the data from inside Access would show the row, proving the insert did indeed take place, and proving access is capable of retrieving the newly inserted row.

We did some searching on the MySQL Access Migration forum but alas, to no avail. Lucky for us, a bit of browsing around brought us to the -unbeatable - reference manual. The "deleted" problem is described there exactly, and a workaround is ginven there too.

(By the way, the manual has an entire section devoted to using the MySQL ODBC driver in combination with Microsoft Access)

We had to change just two things. First, we had to enable the Return matching rows option in the MyODBC configuration dialog. This is available from the ODBC Driver manager. Then, we had to add a timestamp column to the tables:

    alter table table1
    add dummy timstamp
    default current_timestamp
    on update current_timestamp

...and that fixed the problem.

A point worth mentioning is that it really doesn't matter how you call that column. You don't even need to use it in your application. It just needs to be there, that's all.

Unfortunately, we are both at a loss as to explain why (and how) this works. We even did some ODBC tracing to see what happened. We couldn't find out how it works, but we could see something else that's interesting. After the INSERT, Access performs a SELECT, presumably to synchronize it's view on the data. Judging by the trace, that SELECT statement does NOT use the timestamp column in the WHEREclause.

Now, there still have to be some tests, but at least, we're very confident that this port to MySQL will be quite successful and without siginificant effort (thanks to the Migration toolkit)

Barbara promised she'd let me know if there's any progress or any troubles, so I will report them back in this blog, '...for the world to knwow...'