Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2558
  • Last Modified:

Change Unicode Compression Field Property

I imported an Access V1.1 database with hundreds of tables and about 4000+ fields. I want to set all fields to "Allow Zero Length" and "Unicode Compression"=true.  Should be easy! Yes?

I found loads of stuff but nothing seems to work. {?} At first I tried with DAO, then gave up. Then I tried using ADO + ALTER TABLE...ALTER COLUMN.

The I found this:


Can anybody find another working way....

Function Converter()

' Set references to ADO 2.7 and ADOX

Dim Cat As New ADOX.Catalog
Dim CN As New ADODB.Connection

CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + CurrentDb.Name + ";User Id=;Password=;"

Set Cat.ActiveConnection = CN

Dim TB As ADOX.Table
Dim FLD As ADOX.Column

For Each TB In Cat.Tables
    If Left(TB.Name, 4) <> "msys" Then ' ignore system tables
        For Each FLD In TB.Columns
            ' only change Text & Memo fields
            If FLD.Type = adVarWChar _
                   Or FLD.Type = adLongVarWChar Then
                    FLD.Properties("Jet OLEDB:Allow Zero Length") = True
                    ' The following causes a problem:
                    FLD.Properties("Jet OLEDB:Compressed UNICODE Strings") = True
            End If
    End If

MsgBox "Done"

End Function

  • 3
  • 2
1 Solution
Try this (DAO):

inthedarkAuthor Commented:
I thought that DAO was dead.  But if its the only way to get things working then it lives on.
DAO isn't dead - when communicating with Jet databases, it is the more powerful (and richer) technology when compared to ADO. However, if you are using a non-Jet backend, ADO is a better solution.
inthedarkAuthor Commented:
DAO is also 2560% faster than ADO.

The first time I tried this I was using DAO from within VB6. So it works in Access but not in VB.

inthedarkAuthor Commented:
Try this:

Function MyTest()
    Dim FLD As DAO.Field
    Set FLD = CurrentDb.TableDefs("table1").CreateField("NewField", dbText, 10)
    With FLD
        .AllowZeroLength = True
        .Properties.Append .CreateProperty("UnicodeCompression", dbBoolean, -1, False)
    End With
    CurrentDb.TableDefs("table1").Fields.Append FLD
    CurrentDb.TableDefs("table1").Fields("NewField").Properties("UnicodeCompression") = True
End Function

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now