Compacting mdb may need few tries

[FuzMic]

Hi guys

I have this experience when i try compact an opened .mdb. Quite frequently access02 or access03 will say that the mdb is in use or the temporary transit mdb is corrupted.

If i try again, at most on 3rd try, the .mdb can be compacted without any problem. Hence there is no real issue since i can compact it at the end.

Just wondering why; any idea?

 

[apr pillai]

When you try to compact an open database;

1. Access closes the active database
2. creates a new database (say db1.mdb)
3. copies all the objects (except temporary and obsolete objects) from the closed database into db1.mdb
4. deletes the original database.
5. renames db1.mdb to original name.
6. opens it immediately after the above actions.

If the database is in use by more than one user, the compacting action will fail.
If you shut-down your computer without closing your Access Database then the .ldb file (keeps the users list, normally deletes automatically while the database is closed) remains on the disk. Next time when you open the database it uses the old .ldb file and the userID is added to it. When you attempt to Compact the Database it will give you an error stating that the database is in use by more than one user. Check database folder for .ldb file.

 

[FuzMic]

Pillai
Thanks for the clear response, fully digested. Funny though, i) there is no leftover .ldb ii) any other users in the system, YET it can happen. Could there be something else, like OS issues?

 

[JHB]

Is it only this database that act so, or all?
What version do you use - both Windows and MS-Access?
Have you tried to compact it on another computer?

 

[FuzMic]

JHB Thanks

1 occur frequently in all .mdb access.02 after making changes in the forms. I am referring to the Front End .mdb which is holding all the forms, tables at the BEnd are linked to it on opening the .mdb, not before. Compacting is before running the FEnd.
2 Some times a reopening of the .mdb is required.
3 winxp, win7 both manifest same problem from time to time
4 compacting in access.02 & access.03 both manifest same problem
5 In all PCs, not just one!

As i thought the .ldb may exist, but compacting is still possible after few tries. Any way the .ldb has zero size, so still hoping for deeper insight. Hopefully waiting.