{"id":66303,"date":"2006-10-09T13:18:00","date_gmt":"2006-10-09T13:18:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/10\/09\/how-can-i-compact-an-access-database\/"},"modified":"2006-10-09T13:18:00","modified_gmt":"2006-10-09T13:18:00","slug":"how-can-i-compact-an-access-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-compact-an-access-database\/","title":{"rendered":"How Can I Compact an Access Database?"},"content":{"rendered":"

\"Hey, \n

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

— DA<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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\u2019t always the case, most days we\u2019d look at a question like this and think, \u201cCompact an Access database? That sounds hard; why would we want to do that?\u2019\u201d But that was before last night, when the Scripting Guy who writes this column heard the most dreaded words in the English language: \u201cDad, can you help me with my math homework?\u201d<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. If you don\u2019t have children, there are<\/I> a few rough equivalents to \u201cCan you help me with my math homework?\u201d including \u201cCould I see you in my office for a moment?\u201d and \u201cI hope you didn\u2019t cut a piece of that cake that I had sitting on the counter.\u201d<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

As it turns out, compacting an Access database is way<\/I> 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\u2019t 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\u2019s math homework. Suddenly, there was a need for zero after all!<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. Over time, of course, people found more uses for zero; for example, it turns out to be incredibly important when calculating the Scripting Editor\u2019s batting average.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

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<\/I> 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\u2019s face it: scripting questions are nothing<\/I> compared to determining the real zeroes of polynomial functions.<\/P>\n

If you don\u2019t believe us (and it is<\/I> 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:<\/P>

Const CreateLog = True<\/p>\n
Set objAccess = CreateObject(“Access.Application”)<\/p>\n
errReturn = objAccess.CompactRepair _\n    (“c:\\scripts\\test.mdb”, “c:\\scripts\\test2.mdb”, CreateLog)<\/p>\n
Wscript.Echo “Compact\/repair succeeded: ” & errReturn\n<\/PRE>\n
Granted, it\u2019s not as exciting as factoring x4<\/SUP> + 29x + 100, although it\u2019s close. We start out by defining a constant named CreateLog and setting the value to True. When we compact the database we\u2019ll 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<\/I> corrupt then no log file will be created.<\/P>\n
Of course, the Scripting Guys would never have anything<\/I> to do with a corrupted database; therefore we can\u2019t verify that this is actually the case. But that\u2019s what the documentation says, and when has Microsoft documentation ever been wrong?<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Don\u2019t bother sending in examples of when Microsoft documentation has been wrong. After all, our email Inbox doesn\u2019t have unlimited storage capacity.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
After defining the constant we create an instance of the Access.Application<\/B> object. We then use this line of code to compact (and, if necessary, repair) the database C:\\Scripts\\Test.mdb:<\/P>
errReturn = objAccess.CompactRepair _\n    (“c:\\scripts\\test.mdb”, “c:\\scripts\\test2.mdb”, CreateLog)\n<\/PRE>\n
As you can see, the CompactRepair<\/B> 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\u2019re 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\u2019re done:<\/P>
Wscript.Echo “Compact\/repair succeeded: ” & errReturn\n<\/PRE>\n
That was<\/I> easy, wasn\u2019t it? However, there is<\/I> one drawback to this script: if the file C:\\Scripts\\Test2.mdb already exists then the script will fail. That\u2019s 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\u2019s what that modified script looks like:<\/P>
Const CreateLog = True<\/p>\n
Set objAccess = CreateObject(“Access.Application”)\nSet objFSO = CreateObject(“Scripting.FileSystemObject”)<\/p>\n
If objFSO.FileExists(“c:\\scripts\\test2.mdb”) Then\n    objFSO.DeleteFile(“c:\\scripts\\test2.mdb”)\nEnd If<\/p>\n
errReturn = objAccess.CompactRepair _\n    (“c:\\scripts\\test.mdb”, “c:\\scripts\\test2.mdb”, CreateLog)<\/p>\n
Wscript.Echo “Compact\/repair succeeded: ” & errReturn\n<\/PRE>\n
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<\/I> 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\u2019s a problem: negative numbers don\u2019t have<\/I> square roots. But mathematicians aren\u2019t deterred by that: if they can\u2019t use real numbers then they simply use imaginary numbers instead. The Scripting Guys are currently working on a scripting equivalent: if you can\u2019t use a real scripting method to carry out a task we\u2019re trying to develop imaginary scripting methods that you can use instead. We\u2019ll keep you posted on that.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Of course, we\u2019ll likely report the results in an imaginary scripting column, which means we aren\u2019t totally sure how you\u2019ll find<\/I> that information. But we\u2019ll worry about that later.<\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019t always the case, most days we\u2019d look at a question like this and […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[54,49,3,5],"class_list":["post-66303","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-access","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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\u2019t always the case, most days we\u2019d look at a question like this and […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66303","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=66303"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66303\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=66303"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66303"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66303"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}