Wednesday, November 28, 2012

2003 verses 2000 format

I asked this question of Wayne Phillips from EverythingAccess.com
In your website you stated  "When using Access objects (e.g. forms, reports, modules etc.) consider upgrading Access 2000 databases to Access 2002/2003. This is due to a much improved file-system for storing these objects in Access 2002/2003."
I am working on a huge system in a2000 format.  Is it really worth the upgrade to 2003 ?
and Wayne answered

I generally go with 'if it ain't broke don't fix it' :)  But if it is broke, or you're experiencing regular corruption, I definitely would recommend it.  In Access 2000 the forms/reports/vba etc are all stored together in one big compound file called a DocFile, which is then itself split into 4000 byte or so chunks and each stored as records in the MSysAccessObjects table.  This DocFile adds an extra layer of abstraction (and complexity) to the storage of Access-objects which is not needed and degrades performance.  The 2002+ format removes the DocFile complexity and instead stores all the Access-objects data directly as separate records in the MSysAccessStorage table.  Furthermore, in my experience, the 2000 format is more prone to corruption issues.

Kind regards   Wayne

2 comments:

kimba said...

Why stop at 2003 instead of moving to 2010?

Tom Wickerath said...

If the file system is much improved, using the 2002-2003 file format, why did Microsoft introduce a bloat-related problem and not bother to fix it?

Database bloat is not stopped by compacting database with Access 2002 format
http://support.microsoft.com/kb/810415

I always release in the .mde format, so one of my last steps involves converting from the 2000 file format to the 2002-2003 file format, then creating the .mde in Access 2003. So, I guess I enjoy the best of both worlds: easier compaction during development, and apparently an improvement in speed in the .mde format.

Do things ever go honky during development? Yes, on occasion they do, but *usually* a simple /decompile, compact, recompile will restore functionality.


Tom Wickerath
Access MVP Alumni
4/1/86 to 3/31/2012