4 Tips to Keep your Access Database in Tip Top Shape

Here are four quick tips I use to keep my database file size down and my queries running quickly. These have been gleaned from various articles I've read over the last few years. You'll want to follow these in order to ensure each step benefits from the one before it. If you have any more, add them in the comments section.

First, backup your database

My article Updated VBA Backup Code contains code to programmatically backup any file. You'll definitely want to backup the file first, in case any changes you make happen to corrupt the database file. You could also use the backup feature built into Access.

Decompile, Then Recompile VBA code

Following advice from MVP Roger Carlson on how to create a shortcut to decompile a database's VBA code, I created a shortcut to do so. However, to avoid any potential errors in creating the shortcut, I wrote some VBA code to create it.

Sub CreateDecompileShortcut()
 ' creates a 'Decompile' shortcut on local desktop
  Dim desktopFolder As String
  Dim accessFolder As String
  Dim WshShell As Object ' WshShell
  Dim WshShortcut As Object ' WshShortcut

  ' get desktop folder
  desktopFolder = Environ("userprofile") & "\Desktop\"

  ' get MS Access path, assume default install
  accessFolder = Application.Path & "\MSACCESS.EXE"

  ' create shortcut
  Set WshShell = CreateObject("WScript.Shell")
  Set WshShortcut = WshShell.CreateShortcut(desktopFolder & "Decompile Access Database.lnk")

  With WshShortcut
    .Arguments = "/decompile"
    .TargetPath = accessFolder
    .WindowStyle = 4
    .Description = "Decompile MS Access Database"
    .Save
  End With

End Sub

Run this code from your Excel VBA Editor and then follow Roger's instructions in his blog post, starting from #3 (since we already have the shortcut). (FYI this is a great way to create a shortcut to your application from an installation package.). This will put your VBA code in a compiled state. You only need to run the above code once, then reuse the shortcut every time you want to recompile your Access VBA code.

Compact and Repair

After compiling the VBA code in your database, run Compact and Repair. Access 2003 allows you to do this programmatically. See Clean and Repair your Access database for a method that uses VBA (works for Access 2003 only). You could also create another shortcut that can do it for you. The shortcut will work for any version.

I use this procedure to create a shortcut that can compact and repair the database file of my choice. Once again it is run from Excel VBA but you only need to run it once for each database to create the shortcut.

Sub CompactAndRepairMDB(fileName As String)

 ' creates a Compact And Repair shortcut on local desktop
  Dim desktopFolder As String
  Dim accessFolder As String
  Dim WshShell As Object ' WshShell
  Dim WshShortcut As Object ' WshShortcut

  ' get desktop folder
  desktopFolder = Environ("userprofile") & "\Desktop\"

  ' get MS Access path, assume default install
  accessFolder = Application.Path & "\MSACCESS.EXE"

  ' create shortcut
  Set WshShell = CreateObject("WScript.Shell")
  Set WshShortcut = WshShell.CreateShortcut(desktopFolder & "Compact and Repair " & ExtractFileName(fileName) & ".lnk")

  With WshShortcut
    ' filename must be wrapped in quotes
    .Arguments = Chr(34) & fileName & Chr(34) & " /compact"
    .TargetPath = accessFolder
    .WindowStyle = 4
    .Description = "Compact and Repair MS Access Database"
    .Save
  End With

End Sub

Function ExtractFileName(fileName As String) As String
' extract filename portion of filename, no extension
 Dim fileN As String

  fileN = Right(fileName, Len(fileName) - InStrRev(fileName, "\"))
  fileN = Replace(fileN, GetFileType(fileN), "")

  ExtractFileName = fileN
End Function

Function GetFileType(fileName As String) As String
' get file extension
 GetFileType = Mid$(fileName, InStrRev(fileName, "."), Len(fileName))
End Function

To call the function you would do something like this:

Sub CreateShortcut()

  Dim fileName As String

  fileName = "G:\Files\Accounts\Databases\Customer_File.mdb"

  Call CompactAndRepairMDB(fileName)

End Sub

Now suppose you wanted to call one of these shortcuts programmatically, you could use the Run Method of the WSHOM Shell Object, like this:

Sub RunFile(fileName As String)
  Dim WshShell As Object ' WshShell
  Set WshShell = CreateObject("WScript.Shell")
  WshShell.Run fileName
End Sub

Recompile Queries

According to Performance Tips to Speed Up Your Access 2007 Database, recompiling your queries makes them run faster. The way to do this is to open the query in Design view, save and run it.

The following procedure, run in Access, will loop through the queries collection, then open, save and run each query in turn.

Sub RunAllQueries()

  Dim db As DAO.Database
  Dim queries As DAO.QueryDefs
  Dim query As DAO.QueryDef

  Set db = CurrentDb
  Set queries = db.QueryDefs

  ' loop through each query
  For Each query In queries
    ' open query in design view
    DoCmd.OpenQuery query.Name, acViewDesign
    ' save query
    DoCmd.Save acQuery, query.Name
    ' run query
    DoCmd.OpenQuery query.Name, acViewNormal
    ' close query
    DoCmd.Close acQuery, query.Name
  Next query

End Sub

Any more tips I forgot?

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

This article is closed to any future comments.
excel school learn dashboards