How do I set Unicode Compression to yes in create table statement?

[Anupam]

I am writing this code which is generating error:-
dbs.Execute "CREATE TABLE Property_Ledger_Temp (Ledger CHAR(26)NOT NULL WITH
COMPRESSION, Vr LOGICAL,CONSTRAINT Ledger UNIQUE (Ledger));"
Without Compression the code is executed.

 

[Allen Browne]

Access DDL is not powerful enough to set these kinds of properties.

Use DAO instead. Set a boolean property called UnicodeCompression on the
field.

There's an example of creating a table in DAO, and setting these properties
here:
http://allenbrowne.com/func-DAO.html
Scroll down the page to this function to see how to set the properites:
Function StandardProperties(strTableName As String)

There are several important properties you cannot set via DDL, e.g.
AllowZeroLength.

 

[David W. Fenton]

Access DDL

There is no such thing as "Access DDL," only Jet DDL.

 

[RoyVidar]

I am writing this code which is generating error:-
dbs.Execute "CREATE TABLE Property_Ledger_Temp (Ledger CHAR(26)NOT
NULL WITH COMPRESSION, Vr LOGICAL,CONSTRAINT Ledger UNIQUE
(Ledger));" Without Compression the code is executed.

"Note that the WITH COMPRESSION and WITH COMP keywords are declared
before the NOT NULL keywords."
http://msdn.microsoft.com/en-us/library/aa140015(office.10).aspx

dbs.Execute "CREATE TABLE Property_Ledger_Temp (" & _
"Ledger CHAR(26) WITH COMPRESSION NOT NULL, " & _
"Vr LOGICAL, " & _
"CONSTRAINT Ledger UNIQUE (Ledger))"

You might want to also ensure the Zero Length Property is set to
False, which cannot be set with DDL, but various ways through code.

Here is one utilizing ADOX.Catalog.

With CreateObject("ADOX.Catalog")
Set .ActiveConnection = dbs
.Tables("Property_Ledger_Temp").Columns("Ledger").Properties( _
"Jet OLEDB:Allow Zero Length").Value = False
End With

If you can use DAO, that will probably prove to be significantly faster