Need to trim *all fields* across *all tables*

[skydivetom]

Hello:

I need some assistance with trimming *all fields* across *all tables*.

Attached DB contains the following objects:
- 2 Tables
- 2 Queries
- 1 Module that is *intended* to trim all fields in both tables

View Data:
- Upon opening either query ("qryLen_TableOne" or "qryLen_TableTwo"), all fields show LEN value = 80

Module:


- As of now, the attached VBA does NOT seem to execute properly.
- That is, after execution, I continue to show "80" across all fields in both tables.

What I need some help with:
- Review/adjust/replace the VBA in the module so that all fields in both tables are trimmed down (no leading/trailing spaces) upon execution.

Thank you,
EEH

[ranman256]

Make 2 update queries that trim all fields, 1 for each table, using Trim(field)
put both queries in a macro,
run macro.

[moke123]

I think you need to set unicode compression to yes on your fields. Then run a query to trim the fields.

[skydivetom]

ranman256, moke123:

Thank you for the response. I was hoping to NOT use individual queries. It's ok if my actual application had only 2 tables that require trimming. In my view, there wasn't a need to include all tables in the example file.

Unfortunately, it has dozen of tables that need to be trimmed.

That's why I started trying to develop a module that uses a For loop for all tables in the TableDef.

So, I still need to figure out how to update the module code to ensure I have a more dynamic process. Any additional thoughts?

[moke123]

I couldn't get trim to work on your tables until I changed the unicode compression setting.

[skydivetom]

Ok... I just experienced the same.

How do I update/change "unicode compression setting" from "No" to "Yes" for multiple tables in the VBA module?

[orange]

Tom,
What exactly is the concern?
I added a table with some text fields.
Your process seems to work, but maybe there's more to your concern??

[darkwind]

Ok... I just experienced the same.

How do I update/change "unicode compression setting" from "No" to "Yes" for multiple tables in the VBA module?

Just add:

fld.Properties("UnicodeCompression").Value = True

above:

SQLString = SQLString & "[" & fld.Name & "] = Trim([" & fld.Name & "]),"

That should handle it.

Oh, and going forward, you might want to review here:https://www.fmsinc.com/free/NewTips/...ccesstip44.asp

You don't want your code generating tables with unicode compression turned off... probably.

[skydivetom]

darkwind -- thanks for chiming in.

Please see attached v02:

1. Adding "fld.Properties("UnicodeCompression").Value = True" to the loop did change the unicompression for *all* fields. Great!
2. However, all string values (all fields in both tables) remain to be at LEN=80.

Please open either of the 2 queries to validate.

What's still missing the that the UPDATE/TRIM is not applied to any fields?

Thanks.

[orange]

I did some testing with an old Northwind database.
I got some errors on those tables that did not have any dbtext fields. It resulted in an SQL error.
Your code still tries to execute the SQL even when there is no text field in the tabledef.

Sample: UPDATE [Employee Privileges] SET;

I adjusted the code to only execute the SQL if there was a dbtext field in the record.
I did not get any error/warning re Unicode compression.

Public Sub TrimData()
       
10        On Error GoTo TrimData_Error
                
          'The following code will trim all text fields in all tables
          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 TextFldFound As Boolean   'added
20        Set db = CurrentDb
30        Set tbls = db.TableDefs
          ' loop through each appropriate table
40        For Each tbl In tbls
              ' Debug.Print tbl.Name
50            If tbl.Attributes = 0 Then
          
60                Set thisTable = tbl
                  ' grab all fields
70                Set flds = thisTable.Fields
                  'set the textFldFound to false
80                TextFldFound = False
90                SQLString = "UPDATE [" & tbl.Name & "] SET "
          
                  ' if field is text, create SQL string to trim it
100               For Each fld In flds
110                   If fld.Type = dbText Then
120                       TextFldFound = True  ' a text field was found so continue
                          'Debug.Print thisTable.Name & "   " & fld.Name
130                       SQLString = SQLString & "[" & fld.Name & "] = Trim([" & fld.Name & "]),"
140                   End If
150               Next fld
          
160               SQLString = Left(SQLString, Len(SQLString) - 1) & ";"
                  
                  ' execute update statement on table ONLY IF THERE IS A TEXT FIELD
170               If TextFldFound Then
                      db.Execute SQLString, dbFailOnError
                      Debug.Print SQLString
180               End If
190           End If
              TextFldFound = False
200       Next tbl

          
210       On Error GoTo 0
TrimData_Exit:
220       Exit Sub

TrimData_Error:

230       MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure TrimData" _
              & "  Module  M100_TrimData "
240       GoTo TrimData_Exit
End Sub

[skydivetom]

orange -- thank you for reviewing/updating the code.

When you open the 2 queries, do you see any other value than "80"? For instance, some values (e.g., "Notice") in FIELD1 should be = 6.

Also, if you place the cursor into any records (in table) the cursor is NOT "hugging" the last character. I'm trying to remove all the trailing spaces for each field.

Even w/ the updated VBA routine, it's not happening at this moment.

//

Also, when executing a simple UPDATE query,

UPDATE tbl_One SET tbl_One.Field1 = RTrim([tbl_One]![Field1]);

... nothing is changing. Is this the correct syntax for removing trailing spaces/characters?

[skydivetom]

See attached JPG for illustrating the issue... even after I run the new VBA.

[Minty]

This works by looking for the starting position of a double space.

  SQLString = SQLString & "[" & fld.Name & "] = Left([" & fld.Name & "],InStr(1,[" & fld.Name & "],'  ')),"

But NOT when the fields have unicode compression turned on.
It also doesn't work on the long text fields which doesn't surprise me.
As they are only 80 characters long I would change the data type.

[darkwind]

It appears there is a bug in Access with the Unicode Compression property. Setting it programmatically does not appear to properly apply the setting. You can reproduce/confirm this:

1. Execute the above code. Note that the extra spaces are still present, and Unicode Compression is nominally set to 'YES' in all field properties.
2. Manually attempt to remove the extra spaces in a field and save the record. Note that the extra spaces re-appear.
3. Open the table in design view. Change the Unicode Compression property to No for a field and save the table. Manually change the Unicode Compression property back to Yes.
4. Open the table. Manually remove the extra spaces in the field and save the record. Note that the extra spaces do not re-appear.

So, unfortunately, it looks like Unicode Compression cannot be changed using VBA directly. Instead, you'll need to use an ADO query to execute an ALTER TABLE command with the WITH COMPRESSION keyword. Unfortunately, the WITH COMPRESSION keyword results in a syntax error if run through DAO or normal methods. So, to do it with ADO:

1. Add a reference to Microsoft ActiveX Data Object X.X Library
2. Adjust your module to the following:

Public Sub TrimData()
       
5         On Error GoTo TrimData_Error
                
          'The following code will trim all text fields in all tables
          Dim db As DAO.Database
          Dim tbls As DAO.TableDefs
          Dim tbl As DAO.TableDef
          Dim SQLString As String
          Dim flds As DAO.Fields
          Dim fld As DAO.Field
          Dim TextFldFound As Boolean
          Dim sCommandText As String
          Dim cmd As New ADODB.Command
          
          
10        Set db = CurrentDb
15        Set tbls = db.TableDefs
          ' loop through each appropriate table
20        For Each tbl In tbls
              ' Debug.Print tbl.Name
25            If tbl.Attributes = 0 Then
                  
30                cmd.ActiveConnection = CurrentProject.Connection
35                cmd.CommandType = adCmdText
                  
                  ' grab all fields
40                Set flds = tbl.Fields
                  'set the textFldFound to false
45                TextFldFound = False
50                SQLString = "UPDATE [" & tbl.Name & "] SET "
55                sCommandText = "ALTER TABLE " & tbl.Name
          
                  ' if field is text, create SQL string to trim it
60                For Each fld In flds
65                    If fld.Type = dbText Then
70                        TextFldFound = True  ' a text field was found so continue
                          'Debug.Print thisTable.Name & "   " & fld.Name
75                        SQLString = SQLString & "[" & fld.Name & "] = Trim([" & fld.Name & "]),"
80                        If Not fld.Properties("UnicodeCompression").Value Then
85                            cmd.CommandText = sCommandText & " ALTER COLUMN " & fld.Name & " TEXT(80) WITH COMPRESSION;"
90                            cmd.Execute
95                        End If
100                   End If
105               Next fld
          
110               SQLString = Left(SQLString, Len(SQLString) - 1) & ";"
                  
                  ' execute update statement on table ONLY IF THERE IS A TEXT FIELD
115               If TextFldFound Then
120                   db.Execute SQLString, dbFailOnError
125                   Debug.Print SQLString
130               End If
135           End If
140           TextFldFound = False
145       Next tbl


          
150       On Error GoTo 0
TrimData_Exit:
155       Exit Sub


TrimData_Error:


160       MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure TrimData" _
              & "  Module  M100_TrimData "
165       GoTo TrimData_Exit
End Sub

This worked in my testing.

Edit: You may need to set the Unicode Compression property back to NO before running the ADO query above -- I'm not 100% sure on that, as I tested it only in a situation with Unicode Compression off. If so, you can use the previous code to do so, just add fld.Properties("UnicodeCompression").Value = True into the loop.

[orange]

Unicode compression is new to me???
I turned it off for field1 in tbl_One.

Then ran this code

Sub review()
    On Error GoTo review_Error
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tbl_one")
Do While Not rs.EOF
Debug.Print "Before: " & Len(rs.Fields("Field1")) & " <" & rs!field1 & ">"
rs.Edit
rs!field1 = Trim(rs!field1)
rs.Update
Debug.Print "After: " & Len(rs.Fields("Field1")) & " <" & Trim(rs!field1) & ">  " & Len(Trim(rs!field1))
rs.MoveNext
Loop

    
    On Error GoTo 0
review_Exit:
    Exit Sub

review_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure review" _
   & "  Module  M100_TrimData "
    GoTo review_Exit
End Sub

And get this result:
Before: 6 <Notice>
After: 6 <Notice> 6
Before: 6 <Notice>
After: 6 <Notice> 6
Before: 6 <Notice>
After: 6 <Notice> 6
Before: 6 <Notice>
After: 6 <Notice> 6
Before: 5 <Error>
After: 5 <Error> 5
Before: 5 <Error>
After: 5 <Error> 5
Before: 6 <Notice>
After: 6 <Notice> 6
Before: 6 <Notice>
After: 6 <Notice> 6
Before: 6 <Notice>
After: 6 <Notice> 6
Before: 5 <Error>
After: 5 <Error> 5
Before: 5 <Error>
After: 5 <Error> 5
Before: 5 <Error>
After: 5 <Error> 5
Before: 5 <Error>
After: 5 <Error> 5
Before: 8 <Critical>
After: 8 <Critical> 8
Before: 6 <Notice>
After: 6 <Notice> 6
Before: 5 <Error>
After: 5 <Error> 5
Before: 5 <Error>
After: 5 <Error> 5
Before: 8 <Critical>
After: 8 <Critical> 8
Before: 6 <Notice>
After: 6 <Notice> 6
Before: 6 <Notice>
After: 6 <Notice> 6
Before: 6 <Notice>
After: 6 <Notice> 6
Before: 5 <Error>
After: 5 <Error> 5
Before: 5 <Error>
After: 5 <Error> 5
Before: 5 <Error>
After: 5 <Error> 5
Before: 5 <Error>
After: 5 <Error> 5
Before: 8 <Critical>
After: 8 <Critical> 8
Before: 6 <Notice>
After: 6 <Notice> 6
Before: 6 <Notice>
After: 6 <Notice> 6
Before: 6 <Notice>
After: 6 <Notice> 6
Before: 5 <Error>
After: 5 <Error> 5
Before: 5 <Error>
After: 5 <Error> 5
Before: 5 <Error>
After: 5 <Error> 5
Before: 5 <Error>
After: 5 <Error> 5
Before: 8 <Critical>
After: 8 <Critical> 8