Jet SQL vs. standard SQL

  • Thread starter Thread starter Bill Todd
  • Start date Start date
B

Bill Todd

Where can I get a list of differences between Jet SQL syntax and ANSI
standard SQL? I am trying to assess the difficulty of converting an
application that uses Access Jet as its database to another database.
 
Most of the Jet extensions to ANSI SQL were included in the
latest ANSI standard. Any ANSI standard db engine should accept
them (for example 'right join', 'left join' were not ANSI standard
but are now) You have to worry about wild cards (Jet used
MSDOS wild cards, * and ? instead of ANSI/ODBC wild cards like %),
and quotes: Jet interchangeably accepts (pairs of) double quote "
and single quote ' for data delimiters, and accepts [] for field
delimiters.

Apart from that, any difficulty that you have converting to
another database engine will not be because of syntax differences
in the SQL. Apart from the quotes and the wild cards, Jet is
actually very strict about sticking to what was expected to be
ANSI standard SQL: much stricter than any other popular database
engine, and hence much easier to convert FROM than to convert TO.

Note: you do have to worry about DATA TYPES, DDL, DATA VALIDITY,
EMBEDDED VBA, USER DEFINED FUNCTIONS, IDENTITY FIELDS, DECLARITIVE
REFERENCTIAL INTEGRETY, DATA VALIDATION and QUERY OPTIMISATION.

(david)
 
Bill Todd said:
Where can I get a list of differences between Jet SQL syntax and ANSI
standard SQL? I am trying to assess the difficulty of converting an
application that uses Access Jet as its database to another database.
Click to expand...

Bill Todd,


Here's the Access Help Topic: "Comparison of Microsoft Jet SQL and ANSI SQL"


Datatypes:
Are named differently, can have different definitions, and the BIT (YES/NO
fields) Access datatype has no ANSI equivalent (The ANSI BIT datatype is
something else again.

Keywords:
There are differences in naming between reserved and keywords in Access
vs. ANSI.

Does Not Support:
Full Outer Join
Views (fakes it with QueryDefs)
Triggers
CHECK Constraint
The Output of Relational Operations are not Relations (i.e., when a Query
runs in Access, Access does not treat the resultset/recordset that comes out
of it as indistinguishable from a Table)

Vendor Extensions/Changes
The Crosstab Query (Transform/Pivot).
Uses different wildcard characters in LIKE.
JOINs are built a little differently (with mandatory "()").
PARAMETERS clause of SELECT statements.
The ability to place PARAMETERS inside a SELECT statement that directly
prompt users for information.
DISTINCTROW clause.
Slightly different BETWEEN rules.


The Help Topic named above covers the rest of it.


Sincerely,

Chris O.
 
Hi Bill,

I think David's and Chris's resolution is sufficient and solid. You could
also find the answers in the Access Help Topic as Chris sited. I wanted to
post a quick note to see if you would like additional assistance or
information regarding this particular issue.

We appreciate your patience and look forward to hearing from you!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 
Back
Top