Unicode Compression

[vidajam]

I am converting a lot of Access97 databases into 2000 and I need to change a lot of Table/Text Fields to unicode Compression to YES.
How can this be done in vba code. I was thinking if I could open a list of all tables, go though all fields, find text fields, then change the UCode to yes.
How do I do that using a function. Thanks

 

[Remou]

Hi
Here is a rough idea. Be warned, the code below can seriously damage your database.

On Error GoTo Error_UC
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        'Not a system table
        If Left(tdf.Name, 4) <> "Msys" And tdf.Name <> "SwitchBoard Items" Then
            'Debug.Print tdf.Name
            For Each fld In tdf.Fields
                If fld.Type = dbText Then
                    Debug.Print tdf.Name & "  " & fld.Name
                    fld.Properties("UnicodeCompression") = True
                End If
            Next
        End If
    Next
    
    Set prp = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
    Exit Function
    
Error_UC:
If Err.Number = 3270 Then
    Debug.Print tdf.Name; Err.Number; Err.Description
    Err.Clear
    Resume Next
End If

 

[vidajam]

Why do you say that it Changing the parameters can damage a database ?

 

[Remou]

The code snippet cycles through all tables changing a property, which is not something to do casually, so What I meant to say was "To the casual reader ...".