ADODB Code to Trim Text Fields

In Trim all text fields in MS Access database I posted a method for removing excess spaces from a field in Access. This is a guest post from Dave Braunschweig showing how to do the same using ADODB.

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.

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

This article is closed to any future comments.
learn excel dashboards