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.

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Anupam" <(E-Mail Removed)> wrote in message
news:4B9F5711-19D0-4DEB-B8E7-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> Access DDL

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

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Anupam wrote:
> 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

--
Roy-Vidar