accdb vs mdb. Which is faster/better?

views:

781

answers:

+3 Q:

accdb vs mdb. Which is faster/better?

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)?

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.

CodeSlave 2009-07-25 15:41:04

Aaah. We would love to move to SQL Server, but we are working within the business area rather than the technology area. RAD developing if you like so we are limited to desktop tools such as Excel and Access...50+ users on a 500MB mdb over a shocking network - I am glad I am not one of the end users!

MT 2009-07-26 14:17:22

I say use ACCDB if ACCDB has features you really need (like Sharepoint integration). If not, stay with MDB even for new projects, because you can then run it on all previous versions of Access back to 2000, rather than worrying about everyone getting their workstations upgraded to A2007.

David-W-Fenton 2009-07-26 21:29:39

Thanks David (and re "native" vs "prior"). Everyone has been upgraded...and now they are looking at perhaps getting more RAM for all the PCs as well.

MT 2009-07-27 08:07:49

MT, SQL Server isn't necessarily the business solely of the tech area, just because it has the word Server in its name. Putting it on SQL Server may also improve things for them as well. Especially if the queries can run on the server before the data is passed back to the client.

CodeSlave 2009-07-27 14:18:55

@David W. Fenton: the accdb format is receiving bug fixes that the mdb format is not e.g. the DECIMAL sort bug (http://support.microsoft.com/kb/837148) and MS doesn't publicize the fact so how would you ever know? That's why it's always best to go with the new format for new projects. The mdb format is officially deprecated other than for pre-Access2007 projects.

onedaywhen 2009-08-02 14:16:47

@CodeSlave: Most places that refuse to get SQL server do so because the business people can't (or won't) make room in their budget for a proper DB backend when they already have Access.

A. Scagnelli 2009-08-05 21:08:58

@A. Scagnelli, That's why we have the Express and Developer editions. However, given that they are willing to make a $100 investment per use to upgrade MS Office to a version with MS Access (over 50+ users), what's another $20-$30 per user for MS SQL as well ($4000-$6000). True, the business people might not see this immediately, but... that's just part of the job.

CodeSlave 2009-08-05 21:48:36

+1 A:

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?

Tony Toews 2009-07-25 17:35:51

Spot on. It is part of our 3000 mdbs. We have an application that supports a lot of different reports for different clients. These are imported at runtime depending on the report that is being run. Originally these were all in one large mdb, but importing those was ridiculously slow once you had more than one connection to that database. Having separate databases for each rpt was much faster.

MT 2009-07-26 14:11:48

I think one of the guys played with making a library/mda for all the reports but without much luck. These reports have to be tweaked quite alot so we simply change the report on the network and the client picks up the new one. Personally I am not a big fan of the current set-up as we cannot use an mde, or even know that all of our code compiles.

MT 2009-07-26 14:12:21

ansaurus

tags:

views:

answers:

accdb vs mdb. Which is faster/better?

related questions