Why Doesn't This Work in Access SQL?

[Tony Lin]

The following two sql commands work fine in SQL Server but don't work when
run as an Access Query. What is the correct syntax for Access SQL?


(1) ALTER TABLE MyTable

ADD MyCol int DEFAULT 5



Results in syntax error if run as an Access Query.

Executes OK in SQL Server



(2) ALTER TABLE MyTable

ADD MyPK int IDENTITY



Column MyPK is created with no error message.

However, it is created as an ordinary int column, not as an AutoNumber
column.





Finally, does the documentation of the Access SQL syntax appear anywhere in
the MSDN Library?


Thanks



Tony Lin

Fremont, CA

 

[Dan Artuso]

Hi,
For 2) use AutoIncrement.

Not sure about 1)

 

[david epsom dot com dot au]

Because it's not part of 'Access SQL'

ADD MyCol int DEFAULT 5

Unfortunately, the 'Access' SQL syntax is about
10 years old, and based on an SQL standard that
did not include 'default'
You can use DAO or Access (or ADOX?) or the 'ANSI'
SQL to set a default value, but 'Access SQL'
can't do it.

Jet does support an alternative syntax, called 'ANSI'.
If you use ADO to create a <new> Query, I think it
uses 'ANSI' syntax by default, instead of 'Access'
syntax (but I haven't tried it). 'ANSI' SQL is
disabled for DAO, so if you are using DAO, you have
to use the DAO method rather than SQL.

(david)

 

[Tony Lin]

David,

Got it. Thanks for the advice, David.


Tony Lin

 

[Michel Walsh]

Hi,


In the Immediate Debug Window, not in the query designer, try:


CurrentProject.Connection.Execute " ALTER TABLE myTable ADD COLUMN MyCol int DEFAULT 5 "


Should work (Access 2000 and 2002).


Many Jet 4.0 extensions work only with ADO, not with DAO (and the query designer is based on
DAO). So, in order to make test, you need to use the Immediate Window, not the query designer ( or
in VBA code, using ADO ).



Hoping it may help,
Vanderghast, Access MVP