size difference in identical databases

[Susan L]

I have two Access 2003 data bases that should be identical. They both have
the same structure: two files with 28 fields per record. All data consists
of numbers (designated as 'double') There are no forms and two identical
queries on each one. They both have exactly the same number of records -
1,675,875. One is 1,808,508 KB. The other is 530,208 KB. I can't account
for any differences. The first takes much longer to load as well. Does
anyone have any ideas what else I might look for?

 

[Dirk Goldgar]

I have two Access 2003 data bases that should be identical. They both have
the same structure: two files with 28 fields per record. All data
consists
of numbers (designated as 'double') There are no forms and two identical
queries on each one. They both have exactly the same number of records -
1,675,875. One is 1,808,508 KB. The other is 530,208 KB. I can't
account
for any differences. The first takes much longer to load as well. Does
anyone have any ideas what else I might look for?

Are the two databases the same size after compacting?

 

[Dorian]

Do a compact and repair on each one and compare again.
Do you have identical option settings, startup options, references etc.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[ghetto_banjo]

Run a Compact & Repair on both of them. Access is notorious for
storing data not needed.


Tools --> Database Utilities --> Compact & Repair Database

 

[Tom Wickerath]

Hi Susan,

As others have already mentioned, try doing a Compact and Repair. What is
the file format of the databases in question? Is it the Access 2000 file
format, or the Access 2002-2003 file format? If the latter, you may not be
able to recover unused space by compacting. Here is an article that discusses
this in further detail:

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

On a *copy* of your database, you might want to try opening the database
using the undocumented /decompile switch first, followed by a compact and
repair and then re-compiling any VBA code.

Start > Run: msaccess /decompile

then navigate to your database and open it with the Shift key pressed down
(to prevent any startup code from running). The above command line will work
if you have just one version of Access installed (one version of
msaccess.exe). Otherwise, you will need to create a shortcut that includes
the full path to msaccess.exe followed by the /decompile switch.


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