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.
Code:
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