db1.mdb file created when database compacts

[Guest]

Hi,
I have my databases configured to run compact and repair on shutdown. On
occasion when the database does it compact and repair process, a db1.mdb is
created and left behind after the database has finished its compact and
repair process. The next time the database is accessed and closed another
..mdb files is created this time db2.mdb. The file is incremented everytime.
Can anyone tell me the cause of this?

Thanks,
David.

 

[Tom van Stiphout]

On Tue, 30 Oct 2007 06:34:02 -0700, BigTroubleInLittleChina

Compacting works by creating a dbX.mdb, and copying all objects in it.
If all worked, the original db is deleted and the new one renamed.
If that process did not work for whatever reason, the dbX file may be
left behind. Take it as a sign that your compact did not work as well
as you expected, e.g. because the user got impatient and powered down
the machine while the compact was still in progress.

-Tom.

 

[John W. Vinson]

Hi,
I have my databases configured to run compact and repair on shutdown. On
occasion when the database does it compact and repair process, a db1.mdb is
created and left behind after the database has finished its compact and
repair process. The next time the database is accessed and closed another
.mdb files is created this time db2.mdb. The file is incremented everytime.
Can anyone tell me the cause of this?

Thanks,
David.

Check your database to see if there is a new CompactErrors table. As Tom says,
the db1 (or db2 or whatever) is the intermediate stage of compaction - the
errors table might be in it instead of in your main table.

The other reason that the file might persist is that the user running the
compact might not have Delete permissions on the folder containing the
database.

John W. Vinson [MVP]

 

[germaine.oliver]

If the db1, db2 are fully functional files, and you're working on the
network, then it may not be an error or permissions issue. If you're
using Access 2003 SP3, I think that it is a "bug" in SP3. I started
having this happen when they updated the SP, and apparently, we can't
uninstall the SP....

I don't know a workaround from code, except: 1. Do it on the local
drive (doesn't seem to be an issue there). 2) Figure out which db was
just created (the compacted one), rename (or delete) the original
file, and then rename dbx to the original file name. Both of these
solutions require some fancy coding (an external db, etc), so I'm not
doing that yet. I'm waiting to see if MS acknowledges and/or fixes the
bug...

 

[MacroCharlie]

I agree. this just started with SP3 which my org just installed. Does
Micorosoft plan on fixing this?

 

[Bibi]

We were having this same problem, and worked out that it was due to the fact
that a number of our databases are 'read-only' files. This means that when
Access attempts to replace the original with the compacted file it finds this
action forbidden and simply has to leave the dbx file instead. On the other
hand it is very useful for data security that the databases cannot be
accidentally replaced or deleted. So our solution is to leave them
'read-only' except while we are performing a compact and repair operation