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...


Joshua

6 comments:

  1. I want to distribute an Acess 2010 application with a split database. In the best of all worlds, I would like both databases to compact and repair automatically (part of autoexec or the quit macro). Users will be using Access runtime, they will most likely not have full MS-Access installed on their machines so they can't run compact and repair on either database. Do you know of any code that will do that?

    ReplyDelete
  2. This used to work in Access:

    Function CompactThisDB()
    Dim conCompact As Office.CommandBarControl
    Set conCompact = CommandBars.FindControl(ID:=2071)
    conCompact.accDoDefaultAction
    End Function

    But now it says cannot compact in code or VB. Does anyone have any thoughts?

    ReplyDelete
  3. For working out present condition you may use recovery for access. It has many various features such as working under all Windows OS, using modern methods of recovering mdb data.

    ReplyDelete
  4. Get Access File Recovery Software is an efficient utility to bring all the corrupt or inaccessible ACCDB and MDB files back to life and fix all the minor and major database bugs and errors. It supports Microsoft Access version 95/97/2000/XP/2003/2007/2010/2013. The GUI of the software is very simple and new users easy to use. To download : http://www.filesrecoverytool.com/access-file-recovery.html

    ReplyDelete
  5. Thanks, this is something that has plagued me for a long time, never noticed the Open Exclusive option, but it works every time!

    ReplyDelete
  6. I would like to suggest this application, which will repair your access data in trial version and see you the preview of recover data.

    Try:- http://www.mannatsoftware.com/stellar-phoenix-access-recovery.html

    ReplyDelete