Change Unicode Compression Field Property

Posted on 2004-11-30
Last Modified: 2008-01-09
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

Question by:inthedark
    LVL 41

    Accepted Solution

    Try this (DAO):

    LVL 17

    Author Comment

    I thought that DAO was dead.  But if its the only way to get things working then it lives on.
    LVL 41

    Expert Comment

    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.
    LVL 17

    Author Comment

    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.

    LVL 17

    Author Comment

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now