asked on

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:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/adoproperties.asp

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
Next

End If
Next

MsgBox "Done"

End Function

ASKER CERTIFIED SOLUTION

shanesuebsahakarn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

inthedark

ASKER

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

shanesuebsahakarn

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.

inthedark

ASKER

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.

inthedark

ASKER

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
Stop
End Function