How to compact MS Access database

16 April 2007 15:20 (EST)

This example works only if MS Access is installed on web server (but such case is not advised for security reasons):

<cfscript>
   caller.error = 0;
   if (not isDefined("attributes.database"))
      caller.error = caller.error + 1;
   else {
      if (not FileExists(attributes.database))
         caller.error = caller.error + 2;
      path = GetDirectoryFromPath(attributes.database);
      success = 0;
      while (success eq 0) {
         attributes.tempdatabase= path & randrange(1000,9999) & ".mdb";
         if (not FileExists(attributes.tempdatabase))
         success = 1;
      }
   }
   if (isDefined("attributes.backup"))
      if (FileExists(attributes.backupdatabase))
         caller.error = caller.error + 4;
</cfscript>

<cfif not caller.error>

   <cfif isDefined("attributes.backupdatabase")>
      <cffile action="copy" source="#attributes.database#" destination="#attributes.backupdatabase#" />
   </cfif>

   <cftry>
      <cfobject type="com" action="connect" name="objaccess" class="Access.Application" />
      <cfcatch type="Any">
         <cfset request.comerror = cfcatch.message />
         <cfset caller.error = 10 />
         <cfobject type="com" action="create" name="objaccess" class="Access.Application" />
      </cfcatch>
   </cftry>

   <cftry>
      <cfscript>
         objDBEngine = objaccess.DBEngine;
         temp = objDBEngine.CompactDatabase("#attributes.database#","#attributes.tempdatabase#");
      </cfscript>
      <cfcatch type="any">
         <cfset request.comerror = cfcatch.message />
         <cfif cfcatch.message is "">
            <cfset request.comerror = cfcatch.detail />
         </cfif>
         <cfset caller.error = 10 />
      </cfcatch>
   </cftry>

   <cfif FileExists(attributes.tempdatabase)>
      <cffile action="delete" file="#attributes.database#" />
      <cffile action="rename" source="#attributes.tempdatabase#" destination="#attributes.database#" />
   </cfif>

</cfif>

And make sure that database is not locked with CF server.

Discussion (1 comment)

jackk:

1 August 2008 17:34 (EST)

Hi,
Nice posting. MS access provided you a preinstalled tool to compact and repair your database. But it has some limitation such as some time it cause database corruption. In this scenario stellar phoenix access repair tool is a good option which is able to repair your database files and mdb files.

Thanks