Updated Access VBA code to delete empty records

January 13, 2010 by JP ·

I recently had to use the code I posted in Clean and Repair Your Access Database to delete blank records from a database. But it failed because one of the tables has spaces in the name. I didn't create the table, I swear!

So here is the revised version that works when you have tables with OR without spaces in the name.

Sub DeleteBlankRecords()

  ' loop through each table and delete blank records

  On Error GoTo ErrorHandler

  Dim db As DAO.Database
  Dim tbl As DAO.TableDef
  Dim currentTable As String
  Dim currentField() As String
  Dim fieldCount As Long
  Dim i As Long
  Dim strSQL As String

  Set db = GetCurrentDB

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

      currentTable = tbl.Name

      ' build string consisting of all fields in 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

      strSQL = "DELETE [" & currentTable & "].*"

      For i = 1 To fieldCount
        strSQL = strSQL & ",[" & currentTable & "].[" & currentField(i) & "]"
      Next i

      strSQL = strSQL & " FROM [" & currentTable & "] WHERE ("

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

      strSQL = strSQL & ");"

      Debug.Print strSQL
      ' execute SQL delete query
      Call ExecSQL(strSQL)
    End If

  Next tbl

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub