Compact Errors

[Lloyd]

I am developing a small Access97 database and attempted to
compact it. The process failed and I checked out some
websites that recommended that one use the jetcomp utility
from Microsoft. This seemed to be successful and when I
opened the application there was a MSysCompactError table
with a single -1017 error.

I then tried to run the Compact feature under Tools,
Database Utilities. The error table was deleted and the
database seems to function properly. My questions:

1) How does one check on the version of Jet that is
running?

2) Is there a way to know if my database is now "clean"?

3) How often is it recommended that a database be
compacted?

Thanks,

Lloyd

 

[Allen Browne]

Q1. JET version
Locate the JET dll, and you can read the version directly.
For Access 97, the file is msjet35.dll
For Access 2000 and later, the file is msjet40.dll
It is typically in Windows\System32.
When you find it, right-Click and choose Properties.
On the Version tab, you should see something like This:
3.51.3328.0
4.0.8618.0

If you want to get the versions programmatically, see:
http://www.mvps.org/access/api/api0065.htm


Q2. Not corrupt?
If the database repaired, and all tables, indexes, relations, and data are
still present, you are probably okay. It may be a good idea to create a new
(blank) database, and import everything: File | Get External | Import. This
forces Access to recreate everything, and is probably the best easy way to
know that things are okay.


Q3. When to compact
Compacting is useful after modifying objects (forms, reports, tables), or
deleting large amounts of data. During development, my practice is to back
up and compact twice a day, because everything is constantly being changed.
That way if something does go corrupt, we never lose more than half a day's
work.

After the database is developed and objects are not being modified, if the
data is relatively stable, compacting once very few months may be often
enough.

For suggestions on how to avoid corruption, see:
Preventing Corruption
at:
http://members.iinet.net.au/~allenbrowne/ser-25.html