[AccessD] Access 2007 - compacted

Hi Jim:

In order to increase speed of a database (even more so in a network type
environment) in many cases it is easier, when a record is deleted to just
turn it off so it is no longer displayed. 

Logically, the system should be able to go back and use the old record
position for a new record but if the system uses Sparse Storage, in
otherwards, only stores filled fields, the deleted record space is no longer
the same size. A similar situation may happen when a table is modified is
some way. There are a number of other situations that may cause the system
waste or leave unused space and until the database is compacted, unusable
space. Unlike memory management, like garbage collection, this process can
not happen in real time as given the potential of issues and time
constraints especially in a multi-user environment. So every record addition
or change is just added to the end of the table space.

In conclusion, compacting an active database, like an MS Access MDB, when it
is no being used should be a regular discipline. Note that the MDB database
was never designed to work in a large active multi-user environments but it
is just fine for a small Mom an Pop type businesses.     

If you are going to be building or maintaining a growing and/or active
system do yourself a favour and move to another BE database with greater
capabilities...

Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Hewson
Sent: Tuesday, March 05, 2013 6:33 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Access 2007 - compacted

I had something happen that I can't explain. I hope someone on this list
could shed some light on it.

I have a complex database, 77 tables (2 linked, 2 user system), 518
queries, 161 forms, 146 reports, 29 modules and 2 classes and approximatel
42,000 lines of code.

While I was creating this database the size increased.  The latest was over
92 MB.
I was doing some trouble shooting because one query wasn't working
correctly with one of the linked tables.
I got an error message... stating it couldn't find a field in the query and
then it shut down.
I reopened the database did the usual - compile, saved, etc. I fixed the
error and then the issue went away.
Then I did a compact and repair.
Everything is working, all the code is there, it's as if nothing happened.
Great! I'm back in business.

I checked the size of the it's down to 22.3 MB! How can that happen? - goes
from over 92 MB to 22.3 MB.
I'm happy the file size is down and everything works! It also seems to work
faster.
I'm confused.

Can that be explained?
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com