accdb vs mdb. Which is faster/better?

Question

Does anyone have any experience of using Access 2007 with the prior versions of Access (i.e. mdb files).

We have been upgraded, but are still using the mdb format. Some of our code (in particular Docmd.TransferDatabase acImportReport) are now incredibly slow.

I tried an initial test and converted our client mdb to accdb and the above TransferDatabase seemed to perform better.

I am now wondering whether we should bite the bullet and convert all the other files that make up the application. There is no need for replication or user-level security that appear to be the only limitations of the new version from the old. We have one client mdb, three backend databases and then 3000 mdbs each containing a single report (don't ask!).

Does the new version perform any faster than the old one - particularly over an already struggling network)?

Answer 1

Do anything new in accdb (interface wise); any new databases create in MS SQL Server.

Leave the existing stuff in mdb; if it is working now, why mess with it. Hardware upgrades will compensate for any performance degradation you are experiencing.

Sooner or later MS will announce they will stop supporting mdb in their current version of MS Access; at which point it is worth it to triage and start converting to the new format. But don't make the database accdb. Move it to something like MS SQL Server.

The reason why I would wait until you are forced by MS is that it is unlikely you will get approval from the purse string holders to make those changes now; but when you forced too, their purse strings loosen up.

Answer 2

I doubt very much that there is a lot of performance improvement in ACCDB vs MDB although I suppose individual functions could be better or worse. I say this because the biggest bottleneck is usually network related.

I'd suggest running timing tests though. Compare the two. Make sure the MDB is in A2007 format though. Exit the MDB/ACCDB between tests as otherwise Access may cache data. Run the test several times and if any major differences keep rerunning it until you get three runs with about the same time.

Just curious though. WHy do you folks do the Docmd.TransferDatabase acImportReport so much? Or is that part of your 3000 MDBs with reports in them?