How would you change the Unicode Compression of a field using VBA? 1

[johncasimir]

How would you change the Unicode Compression of a field in MSAccess using VBA?

I'm adding a field to a table using the sql:

"ALTER TABLE [TableName] ADD COLUMN [FieldName] CHAR(100);"

I need to change the Unicode Compression of that field to "Yes"

Thanks for the help in advance!

p.s This post is also listed in the MS Access Tables and Relationships Forum

John

 

[CautionMP]

johncasimir,
There is probably a way to do it with Access SQL but here is a method to do it with DAO (make sure you have a reference to the Microsoft DAO 3.6 Object Library in your VBA project).

Sub changeUnicodeComp()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("[i][TableName][/i]")
Set fld = tdf.Fields("[i][FieldName][/i]")

'Here is where the value is set
Set prp = fld.Properties("UnicodeCompression")
prp.Value = True

Set prp = Nothing
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Remou]

Compression is only available with ADO.

[tt]Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
strSQL = "ALTER TABLE

ADD COLUMN [Field] Text(40) WITH COMPRESSION"
cn.Execute strSQL[/tt]

--

http://msdn2.microsoft.com/en-us/library/Aa140015(office.10).aspx

 

[johncasimir]

Thanks CautionMP, however I got a "property not found Error".

Thanks Remou. Your solution worked pefectly!!!

John