Gandalf's notebook: 4 Tips to Keep your Access Database in Tip Top Shape

from www.codeforexcelandoutlook.com

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?