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?”

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!

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 x⁴ + 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?

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.