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

A quick test on a database with 187 medium-length scholarly references showed a reduction in size from 1,671,168 bytes to 1,306,624 as a .accdb file.

Here's an article with some pros & cons:

Access File Formats: ACCDB vs MDB

They point out with .accdb's, you can:

• Include attachments in your database
• Use multivalued fields
• Allow SharePoint and Outlook to trust these files, due to "[i]mprovements in the database security model [which] allow for the security validation of database files"
• Have supposed cryptography improvements due to use of the Windows Cryptographic API

Here's a M$ article that tells how to do attachments as database record fields:

Attach files and graphics to the records in your database

The first article also says: "There are also two limitations that you should consider when using ACCDB. ACCDB databases do not support user-level security or replication." I made a copy of a database, and it opened, no problem - even in a different directory; so I don't know what you are missing out on in terms of replication.

Note: I was unable to Save As to convert it to a .accdb file, as given in the first article. I did File | Save & Publish, and it let me.

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?

Answer 3

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.