Clean and Repair Your Access Database

I've recently started working with Microsoft Access on a regular basis, and noticed that the databases bloat up pretty quickly. I would run the Compact and Repair feature regularly, but looked for a way to do it programmatically.

Turns out its extremely easy (borrowed from mvps.org):

Compact and Repair

Sub CompactDB()

' compacts current database

CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database Utilities"). _
Controls("Compact and Repair Database..."). _
accdodefaultaction

End Sub

Sometimes my forms produce blank records in some of my tables (don't ask). I tried searching for the code to loop through every table in a database and delete blank records, but couldn't find it, so I wrote my own. I run this right before running the code above. It simply builds a SQL string from the table name and fields and calls a separate function to execute it. See below and leave a comment if you have a question.

Delete Blank Records from All Tables

Update 1/13/2010: See Updated Access VBA code to delete empty records for an updated version of this code.

Sub DeleteBlankRecords()
' loop through each table and delete blank records

Dim tbl As TableDef
Dim currentTable As String
Dim currentField() As String
Dim fieldCount As Long
Dim i As Long
Dim SqlString As String

For Each tbl In CurrentDb.TableDefs
  If tbl.Attributes = 0 Then ' not a system table, OK to proceed

    currentTable = tbl.Name

    ' populate array with all field names from specified table
    fieldCount = tbl.Fields.Count

    ReDim currentField(1 To fieldCount)
    For i = 0 To fieldCount - 1
      currentField(i + 1) = tbl.Fields(i).Name
    Next i

    SqlString = "DELETE " & currentTable & ".*"

    For i = 1 To fieldCount
      SqlString = SqlString & "," & currentTable & "." & currentField(i)
    Next i

    SqlString = SqlString & " FROM " & currentTable & " WHERE ("

    For i = 1 To fieldCount
      If i = 1 Then
        SqlString = SqlString & "((" & currentTable & "." & currentField(i) & ") Is Null)"
      Else
        SqlString = SqlString & " AND ((" & currentTable & "." & currentField(i) & ") Is Null)"
      End If
    Next i

    SqlString = SqlString & ");"

  Call ExecSQL(SqlString)

  End If
Next tbl

End Sub

Function ExecSQL(SqlString As String)
' run any SQL statement

With DoCmd
  .SetWarnings False
  .RunSQL SqlString
  .SetWarnings True
End With

End Function
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

comment bubble 5 Comment(s) on Clean and Repair Your Access Database:

  1. As far as I know, the compacting code only works for Access 2003 and before. Do you know a way to do this in Access 2007?

  2. As far as I can tell, it isn't possible. See Utter Access for more info.

  3. We may want to add [ and ] if the field has a space in it (which is allowed in Microsoft database products for unknown reasons.)

  4. It is surprising the compact code does not work in 2007. The delete blank recs code could be very useful for some imported tables. Very nice.

  5. Found this in a MS newsgroup posting, you can compact Access 2007 files this way. Update the path/filename for your specific needs.

    Shell """C:\Programmi\Microsoft Office\Office12\msaccess.exe"" " & _
      """C:\test\test_be.accdb"" /compact"
This article is closed to any future comments.
excel school learn dashboards