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.
Download 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.
More information may be found for both of these topics on The Access Web courtesy of some very knowledgeable MVPs. Search for the keywords of "decompile" or "compact.
Therefore, while I personally don't know exactly what these things do under-the-covers of Access, I do like the results they deliver: reduced size of my files and occasional recovery from buggy behavior and/or corruption. The warnings are out there, so I won't take responsibility if Decompile or Compact breaks your MDB file but if it's something you already know about and like to implement regularly, then consider the following ways to make that process simpler.
Before I launch into my solutions, remember what I said earlier. What follows is not an exhaustive list of how the above tasks may be done but rather a summary of how it has been handled by some developers, myself included. My suggestions are not necessarily the "best" way to do these things but are good enough that they are the methods I choose to use in my daily development work.
The "Pay For" Solutions
Since I'm focusing on what I actually use to maintain my Access files, I'll begin with a utility that one has to pay for. It's a utility created by my friend and fellow author Garry Robinson from Australia. It's called The Workbench and you can read about it, download a demo and purchase it at his site.
I like this tool for many reasons. First, because it allows me to create a favorites list to launch any of my development MDB and ADP files with their associated security MDW files with a single click. You can do the same thing with a series of shortcuts and it won't cost you the $120 but this tool is fast and easy to use. More importantly, it comes with many other features that make it invaluable to me. More to the point and as you can see in the screen shot, The Workbench offers options to Backup, Compact, Compile and Decompile as well. I use these features every day.
What this tool doesn't do is automate maintenance. Some of my friends are fond of FMS tools, such as the FMS Total Visual Agent, which does schedule things like data compacts and file backups during off hours. While I use some FMS tools on a regular basis, this is not one I'm familiar with in practical application. You can read more about their wonderful tools for yourself at the FMS web site.
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 guess.
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 this
"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 support site.
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 implement.
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 link:
' 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?
Download for Article: Maint_Scripts.zip