Updated code to trim all text fields in a MS Access database

This is a guest post from Eric @ http://ventre-a-pattes.net/.

I loved your TrimAllTextFieldsAllTables [Editors note: See original post], but it fails if some tables have no text fields. Attached is a slightly modified version that fixes the issue.

Thanks again.

Sub TrimAllTextFieldsAllTables()
  Dim db As DAO.Database
  Dim tbls As DAO.TableDefs
  Dim tbl As DAO.TableDef
  Dim thisTable As DAO.TableDef
  Dim SQLString As String
  Dim flds As DAO.Fields
  Dim fld As DAO.Field
  Dim hasText As Boolean
 
  Set db = CurrentDb
  Set tbls = db.TableDefs
 
  ' loop through each appropriate table (i.e. no linked or system tables)
  For Each tbl In tbls
    hasText = False 'added
    If tbl.Attributes = 0 Then
      Set thisTable = tbl
      ' grab all fields
      Set flds = thisTable.Fields
 
      SQLString = "UPDATE [" & tbl.Name & "] SET "
 
      ' if field is text, create SQL string to trim it
      For Each fld In flds
        If fld.Type = dbText Then
          hasText = True 'added
          SQLString = SQLString & _
                 "[" & fld.Name & "] = Trim([" & fld.Name & "]),"
        End If
      Next fld
 
      SQLString = Left(SQLString, Len(SQLString) - 1) & ";"
      If hasText Then 'added
        Debug.Print SQLString
        ' execute update statement on table
        db.Execute SQLString, dbFailOnError
      End If 'added
    End If
  Next tbl
 
End Sub

Thank you Eric! My code was intended to be run on tables that have all text fields, but this code can be run on tables with a mix of text and numeric fields.

If you would like to contribute a code sample, visit the Contribute page.