Access Bloat?

An odd thing I’ve discovered while working With Access 2000/XP & SQL Server 2000:

The base size of the database that runs The Pencilcase is 9.8MB as created in AccessXP. However, after having scaled it up to SQL Server 2000 for a project, then scaled that DB back down to Access for another project, the new Access file is about 1.9MB. Which is a sizeable difference, seeing as it contains exactly the same difference. Further tests of upscaling & downscaling other databases through the process revealed similar results.

Does Access just store that much extra information about a file? It seems quite strange to me that it would need to. Or perhaps I’m losing information somewhere – I know when I upscale I seem to lose relationship information (although indexing seems to upscale fine). Perhaps when I downscale I lose this also. Although, it must be said, I’ve yet to notice any difference in behaviour in using Access through ODBC whether I’ve taken the time to create relationships or not. I think in Access they are perhaps a modeling tool rather than a data-integrity tool.

Anyone else able to shine any light onto this mystery?

6 Replies to “Access Bloat?”

  1. Hey Steve. I don’t have any revelations about this phenomenon, but I do have experience with it. When I had to upgrade databases created with Access ’98 to Access 2000, the file sizes decreased, usually dramatically like yours. As for the “modeling tool” and “data-integrity tool” stuff, you’re geekier than me ’cause I don’t know what that stuff means. Then again, I’m not a database programmer extraordinaire like yourself 🙂

  2. Hey Steve. I don’t have any revelations about this phenomenon, but I do have experience with it. When I had to upgrade databases created with Access ’98 to Access 2000, the file sizes decreased, usually dramatically like yours. As for the “modeling tool” and “data-integrity tool” stuff, you’re geekier than me ’cause I don’t know what that stuff means. Then again, I’m not a database programmer extraordinaire like yourself 🙂

  3. Not too much. It’s telling that the DB doesn’t grow at all (except by a couple of bytes) as I enter content. So far, I’ve added..lessee…10 000 rows across the various tables (in the largest) and I think it’s only grown by 1Kb. The small version of it grows more noticeably as I add content. It’s all really quite odd.

  4. Not too much. It’s telling that the DB doesn’t grow at all (except by a couple of bytes) as I enter content. So far, I’ve added..lessee…10 000 rows across the various tables (in the largest) and I think it’s only grown by 1Kb. The small version of it grows more noticeably as I add content. It’s all really quite odd.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.