DECIMAL keyword not allowed in Create table query ?

[Nicolo Carandini]

Cant understand why

CREATE TABLE [MyTable] (
[ID] AUTOINCREMENT,
[Importo] DECIMAL(18, 2),
CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])
);

is refused by ACCESS 2007, showing "sintax error in field definition" and
highlighting the word DECIMAL.

I remember that with Access 2003 we needed to enable SQL ANSI 92 but Access
2007 shoud be 92 compliant and last but not least DECIMAL is a reserved word
of ACCESS 2007.

So what I'm missing here?

 

[Michel Walsh]

I confirm that the following is working in Access 2003:

CurrentProject.Connection.Execute "CREATE TABLE [MyTable01] ([ID]
AUTOINCREMENT, [Importo] DECIMAL(18, 2), CONSTRAINT [PrimaryKey] PRIMARY KEY
([ID]));"



Vanderghast, Access MVP

 

[Nicolo Carandini]

Thanks for your kind reply.
Anyone can try this with Access 2007?
I'd like to add that I'm from Italy so I'm using an italian version of
Access 2007. So if someone can succesfully run the sql in a USA version we
can make a deeper investigation to find if is a bug (feature...) of the
italian version and if there is a workaround.

I confirm that the following is working in Access 2003:

CurrentProject.Connection.Execute "CREATE TABLE [MyTable01] ([ID]
AUTOINCREMENT, [Importo] DECIMAL(18, 2), CONSTRAINT [PrimaryKey] PRIMARY KEY
([ID]));"



Vanderghast, Access MVP

Cant understand why

CREATE TABLE [MyTable] (
[ID] AUTOINCREMENT,
[Importo] DECIMAL(18, 2),
CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])
);

is refused by ACCESS 2007, showing "sintax error in field definition" and
highlighting the word DECIMAL.

I remember that with Access 2003 we needed to enable SQL ANSI 92 but
Access
2007 shoud be 92 compliant and last but not least DECIMAL is a reserved
word
of ACCESS 2007.

So what I'm missing here?

 

[Chris2]

Cant understand why

CREATE TABLE [MyTable] (
[ID] AUTOINCREMENT,
[Importo] DECIMAL(18, 2),
CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])
);

is refused by ACCESS 2007, showing "sintax error in field definition" and
highlighting the word DECIMAL.

I remember that with Access 2003 we needed to enable SQL ANSI 92

MS Access 2000 does not have this capability (the above cannot be set).

I tried to execute the DDL above and received the same error you did.

but Access 2007 shoud be 92 compliant

It is not.


Sincerely,

Chris O.

 

[Chris2]

Thanks for your kind reply.
Anyone can try this with Access 2007?
I'd like to add that I'm from Italy so I'm using an italian version of
Access 2007. So if someone can succesfully run the sql in a USA version we
can make a deeper investigation to find if is a bug (feature...) of the
italian version and if there is a workaround.

Nicolo Carandini,

Are you saying that there is no option to set SQL 92 compliance in MS Access 2007?


Sincerely,

Chris O.

 

[Nicolo Carandini]

Hi Chris2,
sorry for my wrong assumption, so my real question should be (from the start):

How to set SQL 92 compliance in MS Access 2007?

and finally I found the way to set it in the 2007 ribbon stuff:

Click the Microsoft Office Button, then (at the bottom right of the Gallery)
the Access Option button, then select the Object Designer tag and under the
Query Design group select "SQL Server Compatible Syntax (ANSI 92)" options as
appropriate.

Thanks everybody for your help!

 

[Allen Browne]

Hi Nicolo

Michel's example used ADO code to exeute a DDL query statement to create a
field of type Decimal. I think you will find that works.

However, if you copy the query statement into SQL View of a query and try it
there, it will not work. That's because the native library that Access uses
is DAO, and Microsoft did not update DAO to handle the Decimal data type.
So, it works in ADO code, but it does not work in DAO code, nor in the query
interface.

In any case, Access has trouble handling the Decimal type:
Incorrect Sorting (Decimal fields)
at:
http://allenbrowne.com/bug-08.html

For a comparison of the names used in DAO and ADO, see:
http://allenbrowne.com/ser-49.html
Note 7 explains your case.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Thanks for your kind reply.
Anyone can try this with Access 2007?
I'd like to add that I'm from Italy so I'm using an italian version of
Access 2007. So if someone can succesfully run the sql in a USA version we
can make a deeper investigation to find if is a bug (feature...) of the
italian version and if there is a workaround.

I confirm that the following is working in Access 2003:

CurrentProject.Connection.Execute "CREATE TABLE [MyTable01] ([ID]
AUTOINCREMENT, [Importo] DECIMAL(18, 2), CONSTRAINT [PrimaryKey] PRIMARY
KEY
([ID]));"



Vanderghast, Access MVP

Cant understand why

CREATE TABLE [MyTable] (
[ID] AUTOINCREMENT,
[Importo] DECIMAL(18, 2),
CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])
);

is refused by ACCESS 2007, showing "sintax error in field definition"
and
highlighting the word DECIMAL.

I remember that with Access 2003 we needed to enable SQL ANSI 92 but
Access
2007 shoud be 92 compliant and last but not least DECIMAL is a reserved
word
of ACCESS 2007.

So what I'm missing here?

 

[Chris2]

Hi Chris2,
sorry for my wrong assumption, so my real question should be (from the start):

How to set SQL 92 compliance in MS Access 2007?

and finally I found the way to set it in the 2007 ribbon stuff:

Click the Microsoft Office Button, then (at the bottom right of the Gallery)
the Access Option button, then select the Object Designer tag and under the
Query Design group select "SQL Server Compatible Syntax (ANSI 92)" options as
appropriate.

Thanks everybody for your help!

Nicolo Carandini,

See Allen Browne's answer on another branch of this thread.


Sincerely,

Chris O.