Database file size and linked tables....

[Robbro]

I have 1 spreadsheet with 5 tabs. I have created a database that creates
linked tables to those 5 tabs to pull the info in, then several queries that
feed a report. The spreadsheet is 606kb. The database size was 2 megs, then
I copied it over the network to a shared location for others to use, along
with the source spreadsheet. I opened the database to set it to use the
spreadsheet file in the shared location and not the one on my local drive,
then saved it. The database was 16 megs after doing this. Performance has
not seemed to decline really but I'm just wondering why and if there is a way
to get it back down to its 2 meg size?

 

[Jerry Whittle]

16 megs? That's a drop in the bucket. Access database files can be up to 2 GB!

The you could do a compact and repair; however, I'm betting that the first
time that you run about anything it that database, it will just grow back to
16 mb or so. If you are doing any sorting or grouping in a query, form, or
report, Access needs some file space to sort the records.

Don't worry. Be happy.

 

[John Spencer]

Compact and Repair may reduce the size.

Linking to a spreadsheet should have minimal effect on the size of the database.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Robbro]

Yea, I was just more curious as to why it stayed 2 megs while I developed it
then went 8x larger when I moved it. I have enabled the "compact on close"
option which I just found (I had been looking all over database tools for
compact or such) and it shrinks it down to 470kb, then after some use it goes
back up to 600-800kb range. Much much better than 16 megs for what it does
Does access keep all the query info when you close it? I'm just wondering
cause this database stores no information, just processes it when I run the
report. Even the 2 megs it originally was sorta blew my mind for storing no
info other than a report format and some queries. Its much more inline with
what I expected after the compact on close option was checked.

 

[Tom Wickerath]

What type of operations are you doing? If you are using DAO code, are you
explicitely closing recordsets, and setting them to nothing?

http://support.microsoft.com/search/default.aspx?mode=a&query=bloat&spid=1265

Are you creating temporary tables in your Access application (or
adding/deleting data from one or more tables)? You can use the idea of a
temporary work database to prevent unnecessary bloat to your application.
Here is an example that you can check out:

http://www.accessmvp.com/TWickerath/downloads/tmpwrkdb.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________