Tuesday, December 20, 2005

'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...'

7 comments:

Anonymous said...

Thanks for your advice. I have the same problems with MS-Access and MySQL and i did not yet been successful although I had added the TIMESTAMP fields.
I shall try once more...

Kind regards

Beat

Anonymous said...

Corrected some small typos . . .

alter table table1 add dummy timestamp default current_timestamp on update current_timestamp

rpbouman said...

Hi Anonymous,

thanks for spotting these! It's corrected now.

Anonymous said...

I have done everything said here
http://dev.mysql.com/doc/refman/5.0/en/myodbc-usagenotes-apptips.html#myodbc-usagenotes-apptips-microsoft
and it still shows #deleted#

MySQL ODBC is really amateurish: I have never seen that with any other ODBC driver !

rpbouman said...

Hi Anonymous,

let me see if I understand:
1) you can't get it to work
2) so it must be the ODBC driver's fault

Well I'm sorry if it doesn't work for you.

Have you tried posting at the MySQL forums at forums.mysql.com? There is an ODBC and an Access forum there too so you should be able to find some help there.

Thank you so much for patiently putting up with all these awful tools, and good luck,

Roland

Sam Hummel said...

Hi Roland,

The staff of my nonprofit has been using an MS Access database but given the geographically distributed nature of our staff, passing the database around is becoming unbearable. We don't have an intranet or a VPN, but we do have a dedicated LAMP web server. So, I thought we'd port the tables to MySQL and turn Access into an ODBC front-end for the data hosted in the MySQL server. Unfortunately, *everyone* has been warning us against doing that. This includes Microsoft/Access/VB developers and non-MS developers alike. They all claim that "it just doesn't work as advertised" and that they "have the scars to prove it." In your case, was this MySQL installation hosted locally or was it on an intranet or a remote server? Did you see any performance or other problems with multiple users? Is this sports clothing store still using this Access/MySQL solution?

Any real-world guidance is very much appreciated as it is hard to find (unbiased anyway).

rpbouman said...

Sam,

in our case, the Access db was on the local network. Unfortunately I lost track of this company, and I don't know if they were successful in the end in porting it.

Would it be very hard to rebuild your app? How badly are you dependent upon MS Access? there are some tools out there like Wavemaker that are designed to offer the same easy development experience as MS Access.

I hope this helps, sorry I couldnt answer your question directly.

SAP HANA Trick: DISTINCT STRING_AGG

Nowadays, many SQL implementations offer some form of aggregate string concatenation function. Being an aggregate function, it has the effe...