How Can I Compact an Access Database?

Hey, Scripting Guy! How can I compact an Access database using a script?

-- DA

Hey, DA. We have to tell you the truth: we are thrilled and excited about getting the chance to answer your scripting question. To be honest, that isn’t always the case, most days we’d look at a question like this and think, “Compact an Access database? That sounds hard; why would we want to do that?’” But that was before last night, when the Scripting Guy who writes this column heard the most dreaded words in the English language: “Dad, can you help me with my math homework?”

 Note. If you don’t have children, there are a few rough equivalents to “Can you help me with my math homework?” including “Could I see you in my office for a moment?” and “I hope you didn’t cut a piece of that cake that I had sitting on the counter.”

As it turns out, compacting an Access database is way easier that determining the real zeroes of polynomial functions. For one thing, the Scripting Guy who writes this column had only the vaguest memories of polynomial functions. For another, back when he took high school math the zero hadn’t even been invented yet; in fact, no one had ever found a use for zero, at least not until they had to start scoring the Scripting Guy’s math homework. Suddenly, there was a need for zero after all!

 Note. Over time, of course, people found more uses for zero; for example, it turns out to be incredibly important when calculating the Scripting Editor’s batting average.

Somehow or another, the Scripting Guy and his son managed to slog through the assignment; in fact, as near as they could tell they even managed to come up with the right answers to boot. (The Scripting Guy who writes this column had forgotten that homework is much harder if you try to get the right answers.) Having experienced a taste of life outside the world of scripting, however, the Scripting Dad vowed that he would never again complain about having to answer scripting-related questions. Let’s face it: scripting questions are nothing compared to determining the real zeroes of polynomial functions.

If you don’t believe us (and it is hard to believe that anything could be easier than determining the real zeroes of polynomial functions), take a look at this piece of code, a script that compacts (and, if necessary, repairs) the Access database C:\Scripts\Test.mdb:

```Const CreateLog = True
Set objAccess = CreateObject("Access.Application")
errReturn = objAccess.CompactRepair _
("c:\scripts\test.mdb", "c:\scripts\test2.mdb", CreateLog)
Wscript.Echo "Compact/repair succeeded: " & errReturn
```

Granted, it’s not as exciting as factoring x4 + 29x + 100, although it’s close. We start out by defining a constant named CreateLog and setting the value to True. When we compact the database we’ll use this constant to tell the script to create a log file if any corruption is detected in the database. As far as we know (and, to be honest, our knowledge here rivals our knowledge of synthetic division) if a database is corrupt the script will create a log file in the same folder as the corrupted database. If the database is not corrupt then no log file will be created.

Of course, the Scripting Guys would never have anything to do with a corrupted database; therefore we can’t verify that this is actually the case. But that’s what the documentation says, and when has Microsoft documentation ever been wrong?

 Note. Don’t bother sending in examples of when Microsoft documentation has been wrong. After all, our email Inbox doesn’t have unlimited storage capacity.

After defining the constant we create an instance of the Access.Application object. We then use this line of code to compact (and, if necessary, repair) the database C:\Scripts\Test.mdb:

```errReturn = objAccess.CompactRepair _
("c:\scripts\test.mdb", "c:\scripts\test2.mdb", CreateLog)
```

As you can see, the CompactRepair method takes three parameters: the path to the database we want to compact (C:\Scripts\Test.mdb); the path to the new, compacted database we’re about to create (C:\Scripts\Test2.mdb); and the constant CreateLog. When we execute this line of code the script attempts to compact the database, with the results (True if the operation succeeded, False if the operation failed) being stored in the variable errReturn. All we have to do now is echo back the value of errReturn and we’re done:

```Wscript.Echo "Compact/repair succeeded: " & errReturn
```

That was easy, wasn’t it? However, there is one drawback to this script: if the file C:\Scripts\Test2.mdb already exists then the script will fail. That’s because CompactRepair will not overwrite an existing file. With that in mind we offer a slightly-modified version of the script, one that uses the FileSystemObject to check for the existence of Test2.mdb and, if necessary, deletes it. Here’s what that modified script looks like:

```Const CreateLog = True
Set objAccess = CreateObject("Access.Application")
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists("c:\scripts\test2.mdb") Then
objFSO.DeleteFile("c:\scripts\test2.mdb")
End If
errReturn = objAccess.CompactRepair _
("c:\scripts\test.mdb", "c:\scripts\test2.mdb", CreateLog)
Wscript.Echo "Compact/repair succeeded: " & errReturn
```

Admittedly, it would be a bit of a stretch to say that the Scripting Guy who writes this column enjoyed being reacquainted with polynomial functions. However, he was intrigued by the opportunity to work with imaginary numbers again. When dealing with polynomial functions you sometimes have to take the square root of a negative number. That’s a problem: negative numbers don’t have square roots. But mathematicians aren’t deterred by that: if they can’t use real numbers then they simply use imaginary numbers instead. The Scripting Guys are currently working on a scripting equivalent: if you can’t use a real scripting method to carry out a task we’re trying to develop imaginary scripting methods that you can use instead. We’ll keep you posted on that.

 Note. Of course, we’ll likely report the results in an imaginary scripting column, which means we aren’t totally sure how you’ll find that information. But we’ll worry about that later.

1. Anonymous says:

Get Access File Recovery Tool that easily repairs corrupt MS Access (.MDB or .ACCDB) database file and then recover tables, queries, forms, macros, reports, etc. .It supports MS Access 2010, 2007, 2003, 2002 & 2000 Etc.

http://www.filesrecoverytool.com/access-file-recovery.html

2. Ashish Kr says:

It is a very well written article about Compact an Access Database manually. For any problem face after Compact process and your database corrupted. Don’t hesitate, just try few method. Here is the complete guide to perform MS Access Database recovery
: http://msofficerecovery.blogspot.com/p/access-repair.html

3. RonaldGomez says:

You can try to use Access file recovery software to recover your lost and damaged data from .mdb files. Go to
http://www.recoverfilesdata.com/access-file-recovery-software.html to know more about access file recovery software.

4. Harry says:

Pardon my newbie ignorance.. but how do I actually run this code please? In powershell.. if I use the script above.. I get all sorts of errors.. what do I need to do to get it working? I’ve tried running it directly.. and I’ve tried saving it as a .ps1

5. Alice Jolly says:

It is a very effective method to Compact an Access Database but even after applying this method, you are unable to Compact an Access Database then you can use the software MS Access Repair Tool. It is a complete guide for Compacting and Repairing your