DECIMAL keyword not allowed in Create table query ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

Michel Walsh said:
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 said:
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?
Click to expand...
Click to expand...
 
Nicolo Carandini said:
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
Click to expand...

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
Click to expand...

It is not.


Sincerely,

Chris O.
 
Nicolo Carandini said:
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.
Click to expand...

Nicolo Carandini,

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


Sincerely,

Chris O.
 
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!
 
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.

Michel Walsh said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Nicolo Carandini said:
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!
Click to expand...

Nicolo Carandini,

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


Sincerely,

Chris O.
 
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...
Click to expand...

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.
Click to expand...

True but DAO is a red herring here.
So, it works in ADO code, but it does not work ... in the query
interface.
Click to expand...

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
Click to expand...

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.

--
 
Back
Top