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

Related Articles:

• October 4, 2011 -- Show Password Characters
• August 15, 2008 -- Excel User Conference Update
• August 27, 2010 -- Calculate Working Hours In Reverse