Re: Jet SQL vs. standard SQL

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: david epsom dot com dot au (david_at_epsomdotcomdotau)
Date: 04/18/04 

Date: Mon, 19 Apr 2004 08:59:29 +1000

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" <no@no.com> wrote in message
news:14v580plgg7oabu1soigo8cvbdlb8ms31b@4ax.com...
> 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.
> --
> Bill

Relevant Pages

  • Re: CREATE TEMPORARY TABLE
    ... Jet VIEWs (SQL language keywords in uppercase, ... the Jet 4.0 implementation is better than that of SQL Server ... Regardless which engine wins that particular race, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Use DAO or ADO?
    ... tables to a jet backend to open a ado recordset. ... will ALSO work for mysql, oracle and sql server if I take that sql ... engine that they don't understand who want to avoid Jet at all ...
    (comp.databases.ms-access)
  • Re: [Access2003, VBA] Use DAO or ADO?
    ... tables to a jet backend to open a ado recordset. ... Because one might have chosen to use ado in place dao in their ... You gain sql neutral code. ... engine that they don't understand who want to avoid Jet at all ...
    (comp.databases.ms-access)
  • Re: microsoft Query with Excel
    ... The ANSI standard, which in Jet does not implement, is ... The use of the term 'SQL database' often implies a MS SQL Server ...
    (microsoft.public.excel.misc)
  • Re: Office 2000 / Office 2007 compatibility question
    ... stored- procedure-like functionality could be built into the jet ... engine. ... I know nothing about SQL Express. ... this kind of "stored procedure" is on ...
    (comp.databases.ms-access)