SQL Specific Window for numeric data types?

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

Guest

Using SQL Specific Window in Access 03 to practice writing sql. I get a
syntax error in my Create Table whenever I define a numeric data type, such
as Number, Decimal, Integer, Double or Long. The error occurs when I try to
run the query. It always stops at the first parenthesis and issues a Syntax
error.

Example:

PAY_RATE NUMBER(4,2),

All other data types work. The query will run and create the table.
 
You are talking about a DDL query that creates a field?
If so, "NUMBER" is too generic.
Use INTEGER, LONG, DOUBLE, etc., without the size in brackets.

If you are trying to create a DECIMAL size field, the DDL query probably
won't work in the query interface, since DAO has not been updated to support
this. By executing your DDL query under ADO, you should be able to use:
DECIMAL(4,2)

However, you should be aware that even if you do this, the Decimal field
type is not properly implemented, and Access can't even sort it properly.
Details
Incorrect Sorting (Decimal fields)
at:
http://members.iinet.net.au/~allenbrowne/bug-08.html

For a list comparing the field types names and contstants to use for the
various libraries, see:
Field type names (JET, DDL, DAO and ADOX)
at:
http://members.iinet.net.au/~allenbrowne/ser-49.html
 
Hi Jamie

Just had a quick look, and the answer appears to be that an unsized TEXT
field is created inconsistently.

If you execute the DDL query under ADOX, you do get a Memo field
(adLongVarWChar).

But if you execute it under DAO, it looks like you get a standard Text field
(adVarWChar).

Does that match what you see?
 
Allen Browne said:
Just had a quick look, and the answer appears to be that an unsized TEXT
field is created inconsistently.

If you execute the DDL query under ADOX, you do get a Memo field
(adLongVarWChar).

But if you execute it under DAO, it looks like you get a standard Text field
(adVarWChar).

Does that match what you see?
Click to expand...

Yes, it does. Thanks Allen.

Jamie.

--
 
FWIW, I've just found an MSDN article that explains this behavior by
design:

http://msdn.microsoft.com/library/en-us/oledb/htm/oledbprovjet_data_type_support.asp

"To provide a more consistent data type naming scheme for Microsoft
providers, Jet has modified the Text data type slightly. While the
underlying storage has remained the same, the implications of the name
text as used in SQL commands have changed. While Text in Jet referred
to a short type, text in SQL Server is a BLOB field (akin to the Jet
LongText/Memo type). When using commands, Text with no size now implies
a LongText, while Text with a size (text(255)) still means the shorter,
non-BLOB text field."

Jamie.

--
 
Back
Top