can someone explain why/how a new database gets created whenever I close one
of my access databases. I am using access 2007 and the new database is being
created and saved in the same directory as the one which I open....and much
smaller. does this have something to do with the fact that I am compacting
upon closing?
Yes. There's something else wrong as well, though - perhaps you don't have
Windows privileges to delete or rename files on the folder.
Compaction works by copying the database to a new database - named db1.mdb
unless there is a db1.mdb already, or db2, db3 etc. as needed; deleting the
old database; and renaming the copy. If you're seeing a db1 appear then the
compaction is not completing. Check to see if you have a (possibly hidden?)
table named something like tblCompactErrors.
I'd be STRONGLY inclined to avoid using Compact On Close at all. It's pretty
much useless for frontends, and will usually not run for backends (it will run
only when the LAST user exits), and when it DOES run, it will interfere if a
new user logs on in the process. You can use Windows Scheduler to run a
compact of the backend at some time when you can be confidant that there is
nobody logged on (3am Monday morning maybe?) instead.
John W. Vinson [MVP]
