Backup and recovery solutions for Microsoft Access abound. Danny Lesandrini shares some of the utilities he has collected over the years and what he finds practical to use on a daily basis.
for Article: Maint_Scripts.zip
At first, I wondered if
there was enough information on the subject of file maintenance for Access MDBs
but before long I realized there’s probably too much for a single article.
There’s no way I can cover all of the possible options and someone is bound to
write me and say, "hey, there’s an easier way to do that." What
follows is what I’ve collected over the years and what I find practical to use
on a daily basis.
The following is what I
have in mind when I say "Access file maintenance":
1) Backing up development versions and data files
2) Decompiling and recompiling development copies
3) Simplifying the Compact and Repair process
4) Easy deployment of new client versions
We know why backups are
important but what is Decompile and why is it important to Compact your MDBs?
Decompile is an undocumented option that many of us became aware of after the
release of Access 97. While we were warned that Decompile could cause problems,
even corruption, I have never personally experienced a failure with it. I have,
however, recovered some seemingly corrupted databases and often shrunk the size
of bloated client MDBs considerably. Michael Kaplan was kind enough to post the
best available explanation of it at his site:
Compact and Repair, on the
other hand, are documented features and show up in the Access menus as an
option. While there are also warnings not to abuse this process, I’m able to
report that I’ve never lost a file because of running the Compact command. What
does it do? Again I defer to Michael Kaplan who once responded to a newsgroup
post with this answer:
[Avoiding compact] is not
necessarily the best course of action, though, for at least two reasons:
1) Compact resets queries so they can be recompiled the next time they are run.
Since data changes over times the QPE (query processing engine) may have a
different optimization plan doe ideal data retrieval but if you never make that
recompile happen, you will never receive that benefit.
2) If you run even moderately complex reports, Access creates a lot of
queries/SVTs (segmented virtual table) that will bloat up the database a bit.
Only a compact can shrink the size back down. Since there is no way other than
by experimentation to determine when this is the case, it is often best to
assume it may happen and prepare for it.
On to the Free Stuff
There is an option in
Microsoft Access that allows you to "Compact On Close". This is not
an option I’ve ever implemented but it could provide a simple solution. I worry
about the overhead accompanied with the process when the user tries to close
the application. (I don’t want phone calls telling my program closes slowly.)
One developer suggested using the following VBA code to toggle the option on
and off every 10 closes of the database or so. That’s a reasonable option, I
CurrentDb.Properties("Auto Compact").Value = 1 'set to 0 for Off
Microsoft Access may be
opened in "decompile mode" by adding the /decompile switch when
opening the Access executable from a shortcut. From the RUN dialog box, the
command to open Access XP from its default install location would look like
"C:Program FilesMicrosoft OfficeOffice10MSACCESS.EXE" /decompile
After Access opens,
navigate to the MDB file you wish to decompile and open it. There will be no message
box or other indication that decompile has taken place. Just trust that it has
and close the Microsoft Access application window. Then reopen your database,
go to a VBA code window and compile the app again. Compact then close the MDB
and you may see that the file has shrunk, sometimes considerably so.
This command may be put in
the Target box of a shortcut item, followed by the name of the Access MDB file
you wish to decompile, saving you the navigation step. One could create a
decompile shortcut for each database file that is regularly maintained in this
way. The same is true for the Compact command, which is also available to the
command line of the MSAccess.exe executable. I never do this but a full
discussion is available on the Microsoft
This is the solution I
really like. What if you could add Compact, Decompile and Repair to the file
right-click menu in Windows? If so, you could skip the step described above and
never have to create shortcuts with the command line switch. Well, this
solution has been around forever. Once again our thanks goes out to the MVPs at
the Access Web where there’s a script to perform
this task. You’ll have to modify the .REG file (in Notepad) to point to the
correct version of Access and the correct install folder but it’s simple to
Getting Decompile and Compact context menu options:
I’ve been around since the
days of Windows batch files (.bat) and I still like them for things like this.
Just copy the text below, paste it into Notepad and save it as anything .bat.
Be sure to edit the file paths and names before trying to use it. Now double
click the file and the copy will proceed. A black DOS style command window will
appear with the REM comments and the command progress. It typically runs fast
enough that no one can really read it, unless the file is very large and the
copy takes time.
REM Backing up old APP file ... please wait ... Copy "C:Program FilesAPPAPP.mdb" "C:Program FilesAPPAPP_bkup.mdb" REM Downloading new APP to your computer ... please wait ... Copy "S:ProductionAPPAPP.mdb" "C:Program FilesAPPAPP.mdb" REM Starting APP ... please wait ... START "msaccess.exe" "C:Program FilesAPPAPP.mdb" EXIT
Batch files gave way to
VBScript files. I love these too and use them for everything, though some
security policies might not allow the running of VB Scripts on a network
environment, where viruses may be propagated by this type of unprotected code.
Again, it’s simple. Open
notepad, copy the text below and this time save it as a .vbs file. Remember
that you need to change paths and file names. Launch it with a double-click.
Dim sSource, sTarget, sMsg, sDevFldr, sServFld Dim sPre, sMon, sDay, sYear, sHour, sMin, objScript ' /////// NOTE: Edit variables in this section /////// ' ' The following lines of code are the only variables that ' need be edited. You must provide a path to the folder ' where development files are kept. ' sDevFldr = "C:DevelopmentApp" sServFld = "YourServerYourBkupApp" sDevFldr = "C:Archive" sServFld = "C:ArchiveArticle Stuff" ' ' /////// NOTE: Edit variables in this section /////// ' Get the file Prefix for archive sake sMon = Right("0" & DatePart("m",Date()),2) sDay = Right("0" & DatePart("d",Date()),2) sYear = Right(DatePart("yyyy",Date()),2) sHour = Right("0" & DatePart("h",Now()),2) sMin = Right("0" & DatePart("n",Now()),2) sPre = sMon & "-" & sDay & "-" & sYear & " " sPre = sPre & sHour & "_" & sMin & "_" ' Create Scripting Object Set objScript = CreateObject("Scripting.FileSystemObject") ' ******************************************************** ' Compact/Backup Aoo to Server Dev Archive sSource = sDevFldr & "Demo.mdb" sTarget = sServFld & sPre & "DemoBak.mdb" objScript.CopyFile sSource ,sTarget ' ' ******************************************************** ' Clean up Set objScript = Nothing ' Finished MsgBox "Finished backing up development database."
VBScript is especially
flexible and may even be used to perform an Access Compact. An example of that
script is at my site, along with many other useful windows scripts. I’ve
reproduced the main command below but you may download the entire script via this
' For Access 2000, use Application.9 Set objAccess = CreateObject("Access.Application.9") objAccess.DbEngine.CompactDatabase strPathToMDB ,strTempDB
Where to Go From Here
The above is a not a
single solution but a potpourri of ideas from which one can find a solution.
Play with the sample code and be creative. The ideas can be used to backup,
deploy and maintain your Access files and other things too.
I chose to use a
commercial solution because I like the convenience but it’s not the only method
I regularly use. Sure, it costs a few bucks but I long ago figured out that my
time is worth a lot in this business. Maybe it’s a little thing and saves only
a small amount of time to automate a compact or file copy but who has time to
waste these days?
for Article: Maint_Scripts.zip