DECIMAL keyword not allowed in Create table query ?

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Jamie Collins]

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...

Are you suggesting that the Access *interface* uses DAO to execute a
query? That doesn't sound correct. AFAIK the 'native library' that
Access uses are the Jet DLLs directly.

...Microsoft did not update DAO to handle the Decimal data type.

True but DAO is a red herring here.

So, it works in ADO code, but it does not work ... in the query
interface.

That's only true of the interface if it is in ANSI-89 Query Mode
('Traditional'). The syntax will work in the interface when in ANSI-92
Query Mode. See:

About ANSI SQL query mode (MDB)
http://office.microsoft.com/en-us/access/HP030704831033.aspx

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

Allen, please review your article. In brief: an index will not help at
all; aggregation is not effected (because aggregation is a *set*
function that neither requires nor uses an explicit sort order); the
erroneous sorting is absolutely predictable (not 'wildly inaccurate')
for all values including zero and NULL; the negative values are
correctly sorted, they merely appear in the wrong place, which shows
your 'use CDbl() or CCur()' case to be a straw man e.g. you could use
ORDER BY MyField >= 0, MyField DESC.

Jamie.

--