Solved: 2007 compact/repair not replacing file

[eed]

Hi, all,

I have just started working with a trial version of Access 2007 to assess the impact to our existing databases from a potential software upgrade and/or file conversion. I'm experiencing one especially bothersome thing.

When I use the "Compact and Repair" menu command, it creates another file called "Database.mdb," which is fewer MBs than the original file (as you would expect after compact/repair). But, Access never replaces the original file with the compacted file as I am accustomed to it doing (by deleting MyFile.mdb and then renaming Database.mdb to MyFile.mdb).

Yes, I can delete the original and rename the copy myself, but that gets annoying. Does anyone have any advice as to why this is not happening automatically?

Thanks in advance,
eed

[asingh]

While you data base is open..is Access creating the lock file. .LDB, format. It should reside in the parent folder of the access data base...??

Ideally Access should not create a 2nd copy...after the compact and repair is over.

Does the master data base close and open..after the compact and repair is over...if it is not doing this....the compact and repair is not over...!

thanks and regards,

asingh

[eed]

- Okay, so I open my file, working_SATool.mdb.
- Access automatically creates the lock file, working_SATool.ldb, in the same directory.
- I select the Compact/Repair menu command.
- The .ldb lock file disappears.
- The file Database.mdb appears in the same directory.The original file, working_SATool.mdb, reopens and the lock file, working_SATool.ldb, is created.
- The file Database.mdb remains in the parent directory, and *it* has the compacted file size, while the original file working_SATool.mdb has the same file size as before the compact.

...Does that help? Any ideas? Thanks!!

[asingh]

The 2nd data base..the temp one created while the compact repair is initiated...should not remain in the folder after the activity is over. This is weird..I tested around 3 of my data bases....and each time after the compact repair is over..the temp data base disappears.

Also do you have Microsoft Access Project files on your system. .ADP. I was reading on the net..these sometime cause problems..for normal .MDP compact repair...!

regards,

asingh

[eed]

Also do you have Microsoft Access Project files on your system. .ADP. I was reading on the net..these sometime cause problems..for normal .MDP compact repair...!

No, I don't have any .ADP files. I will keep in mind for the future, though, that these might affect MDB compact; thanks for that tip.



The 2nd data base..the temp one created while the compact repair is initiated...should not remain in the folder after the activity is over. This is weird..I tested around 3 of my data bases....and each time after the compact repair is over..the temp data base disappears.

I have been looking at it more closely, and I noticed an additional twist. If a file is sitting on my desktop, the temp file disappears correctly. The problem only seems to occur when I am working in a directory located on a network server (at work, even our My Documents folder is actually located on a server, so most of my files are not locally stored). So it must have something to do with Access trying to compact over the network? I never had trouble compacting across the network with Access 2003, but it seems like that might be the source of the problem here with Access 2007.

~ eed

[mattj]

There are known issues when performing a C&R via a network connection. Best practice is to either log onto the server or shared computer, or to copy the db to your local pc, perform the C&R, and then move back to the shared location.
Even with Access 2003, I strongly recommend that you discontinue perforing the C&R via a network connection.

Matt

[eed]

Thanks, asingh and matt, for your input. I am going to mark this Solved; I am concluding that the network is the source of my problem, and I will try to C&R locally only. Thanks!!