Scripting

# How Can I Compact an Access Database?

ScriptingGuy1

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.