Wednesday, August 11, 2010

Compact and Repair on Access 2010 64-bit

I recently had some issues using Compact and Repair while using Access 2010 (64-bit version). I couldn't get my database to compact and repair, and kept getting the following error message:

Could not use 'C:\temp\myDB.mdb'; file already in use.

Of course Access help provided nothing, so hopefully this will help you. Turns out that you need to have EXCLUSIVE ACCESS to the file, so it is guaranteed that no one else is using it, in order to compact and repair it. Here is how you do that:

  1. Open Access, but not a specific database.
  2. On the File tab, click Open.
  3. Navigate your file system to the database you want.
  4. Instead of clicking the Open button, click the dropdown on the button.
  5. Choose Open Exclusive.
  6. With your database now open, choose File, and then Compact and Repair.
This allows you to have Exclusive access to the database, so running Compact and Repair doesn't affect some other user of the database. Of course, other users will have to be logged off of the database for you to have exclusive access.

Hope this helps some user out there...