0

I just solved an issue after about a week of headaches: I had an Access 97 database which I could not open with anything (Access 2007, Excel 16 importing, etc). It would say "The operation is invalid without a current index" or "The current user account doesn't have permission to convert or enable this database." I would find out using a recovery program that this database did not have any set users in its security, which is bizarre.

I solved the problem from a 2003 post in this thread. I opened Excel 16, opened the Data tab, selected "From Other Sources", selected "From Microsoft Query", chose "New Data Source", supplied a name of my choosing, chose Microsoft Access Driver (*.mdb), and hit the "Connect" button.

A dialog opens where you can choose the MDB file. It also has another button, "Repair". If I hit "Repair" when selecting my problematic database, it then will work with any application without throwing either of the errors I was getting.

What I want to know is how to use this "Repair" function from the command line, if possible. I will need to include operations on this database as part of a batch file with 40 other databases that were not giving me any errors.

Is this possible?

pidgezero
  • 133
  • 1
  • 9

2 Answers2

0

You may try these VBA code lines to repair Database in different situations.

Syntax to Auto-Repair:

acCompactRepair "C:\Folder\Database.accdb", True

You may try this also:

  • Launch Access,
  • Open the database,
  • Sets the Compact On Close option to "True",

  • Finish with Quit.

    If you want to Repair Database,not the one you are working with:

Application.compactRepair sourecFile, destinationFile

If you want to Repair the Database you are working with:

Application.SetOption "Auto compact", True

Edited:

This Command can be used to compact Aceess database at command line or through a Batch File.

C:\Program Files\Microsoft Office\Office\MSAccess.exe" "C:\MyDatabase.mdb" /compact "C:\Backup.mdb"

Or this can be used also as set scheduled task.

  • Go to Control Panel then scheduled tasks to open the DB.

Set option Compact on Close.

Through Batch file: 

@echo off
taskkill /im MSACCESS.EXE /f
  • Save & Rename the file to *.bat
  • Schedule the batch file to Run.

Note, I would like to recommend, don't try to compact without a target database.

Rajesh Sinha
  • 8,995
  • 6
  • 15
  • 35
  • Thank you! I'm specifically trying to include the repair in a batch file - could I use this VBA code by running Access from the command line with the /X flag? The use case is that I will need to copy a future backup of this DB that doesn't have any macros saved to it, so I'd like to know if this is possible to automate with a fresh copy. – pidgezero Jun 21 '19 at 11:39
  • @pidgezero,, yes it;s possible to compact/repair Database as well as Backup using batch file. – Rajesh Sinha Jun 22 '19 at 04:05
0

I found what I was looking for - the /compact flag for msaccess.exe does exactly this.

msaccess.exe /compact "c:\path\to\my\database.mdb"

pidgezero
  • 133
  • 1
  • 9