I needed an ADODB version of this and came up with the attached. Please share.

Sub GetTables()
    Dim Recordset As ADODB.Recordset
    Dim TableName As String

    Set Recordset = CurrentProject.Connection.OpenSchema(adSchemaTables)

    Do While Not Recordset.EOF
        If Recordset("TABLE_TYPE") = "TABLE" Then
            TableName = Recordset("TABLE_NAME")
            GetColumns TableName
        End If
        Recordset.MoveNext
    Loop
    Recordset.Close
End Sub

Private Sub GetColumns(TableName As String)
    Dim Recordset As ADODB.Recordset
    Dim ColumnName As String
    
    Set Recordset = CurrentProject.Connection.OpenSchema(adSchemaColumns, Array(Null, Null, TableName))
    Do While Not Recordset.EOF
        If Recordset("DATA_TYPE") = 130 Then
            ColumnName = Recordset("COLUMN_NAME")
            UpdateColumn TableName, ColumnName
        End If
        Recordset.MoveNext
    Loop
    Recordset.Close
End Sub

Private Sub UpdateColumn(TableName As String, ColumnName As String)
    Dim Recordset As ADODB.Recordset
    
    Debug.Print TableName, ColumnName
    DoEvents
    Set Recordset = New ADODB.Recordset
    Recordset.Open TableName, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    Do While Not Recordset.EOF
        If Not IsNull(Recordset(ColumnName)) Then
            Recordset(ColumnName) = Trim(Recordset(ColumnName))
        End If
        Recordset.MoveNext
    Loop
    Recordset.Close
End Sub

Please visit Contribute if you would like to see a code sample posted here.

ADODB Code to Trim Text Fields is Copyright © JP Software Technologies. All Rights Reserved.

• transient custom lists
• new fields
• additional field values
• primitive RegEx pattern masking
• options such as save location and quantity may be stored across sessions
• API has been enhanced

Random Data Generator 3.3.0 now available is Copyright © JP Software Technologies. All Rights Reserved.

ShipTrack 4.2.5 now available is Copyright © JP Software Technologies. All Rights Reserved.

It has been a while since I last posted, so I thought I'd provide a short update on what has been happening lately with the site.

I haven't spent much time writing VBA. Between the holidays, family and work, there simply isn't time to sit down and write a proper blog post.

I did, however, launch a new website on New Year's Eve because I have been spending more time writing PHP. Click here if you want to check it out. So far I have two WordPress plugins and four MyBB plugins written, with one more of each pending.

2012 Stats

Last year this site had 250,000 visits from 200,000 people. After getting close to 2,000 hits per day, my site was spanked by Google's algorithm update, so now we get around 800. I didn't think I write like a robot but apparently Google thinks differently. I should probably order a website review to figure out what I'm doing wrong.

Most popular referrers

These sites provided the most visits in 2012:

• Stack Overflow
• MrExcel
• MSDN
• VBAX
• Chandoo
• DDoE
• Planet Excel
• Programmable Web
• Excel Forum
• DataPig
• Wrox
• Spreadsheet Page
• Access World
• Contextures Blog
• Office
• OzGrid

Thank you to everyone who felt compelled to link to this site!

Downloads

The mail merge tutorial continues to be the most popular download. Visit the Downloads page for a full listing.

Addins

Updates to ShipTrack and RDG are in progress. ShipTrack has become more popular and several new carriers have been added. A few more enhancements are pending.

I will continue to post to this blog as time permits. There are still a few more posts in the "Useful Array Functions for VBA" series that I have yet to complete.

2013 State of the Blog is Copyright © JP Software Technologies. All Rights Reserved.

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.

Updated code to trim all text fields in a MS Access database is Copyright © JP Software Technologies. All Rights Reserved.