Mythical Jet SQL

[Jamie Collins]

1) LIMIT TO nn ROWS (SQL DML):

http://office.microsoft.com/en-gb/access/HP030704831033.aspx

"Using the LIMIT TO nn ROWS clause to limit the number of rows
returned by a query"

Jamie says: I've tried the syntax (substituting an integer value for
nn) in various (all?) possible places in a SELECT query and always get
a syntax error. Conclusion: this syntax is not actually supported.

2) CREATE TEMPORARY TABLE (SQL DDL):

http://office.microsoft.com/en-gb/access/HP010322201033.aspx

"CREATE [TEMPORARY] TABLE... visible only within the session in which
it was created. It is automatically deleted when the session is
terminated. Temporary tables can be accessed by more than one user."

Jamie says: I get a syntax error. Conclusion: this syntax is not
actually supported.

3) Multiple-field NOT NULL constraint (SQL DDL):

http://office.microsoft.com/en-gb/access/HP010322201033.aspx

"You can use NOT NULL...within a named CONSTRAINT clause that applies
to...a multiple-field named CONSTRAINT"

http://office.microsoft.com/en-gb/access/HP010322141033.aspx

[Quote selective]

Multiple-field constraint:
CONSTRAINT name
NOT NULL (notnull1[, notnull2 [, ...]])

notnull1, notnull2 The name of the field or fields that are restricted
to non-Null values.

[Unquote selective]

Jamie says: A syntax error for me, every time. Conclusion: this syntax
is not actually supported.

4) ON UPDATE SET NULL (SQL DDL):

http://office.microsoft.com/en-gb/access/HP010322141033.aspx

"The ON UPDATE SET NULL clause means that if a customer's identifier
(CustId) is updated in the Customer table, the corresponding foreign
key values in the Orders table will automatically be set to NULL."

Jamie says: the example syntax (and similar) generates an 'Invalid
argument' error. Conclusion: this syntax is not actually supported.

Jamie.

--

 

[Guest]

Please show us how you are invoking the SQL. To work
in ANSI-93 mode you normally start with the Application
Connection property.

Note, for reference, ANSI-92 mode is not normally called
'Jet SQL'. The term 'Jet SQL' refers to the 'ANSI-89' mode.

(david)

1) LIMIT TO nn ROWS (SQL DML):

http://office.microsoft.com/en-gb/access/HP030704831033.aspx

"Using the LIMIT TO nn ROWS clause to limit the number of rows
returned by a query"

Jamie says: I've tried the syntax (substituting an integer value for
nn) in various (all?) possible places in a SELECT query and always get
a syntax error. Conclusion: this syntax is not actually supported.

2) CREATE TEMPORARY TABLE (SQL DDL):

http://office.microsoft.com/en-gb/access/HP010322201033.aspx

"CREATE [TEMPORARY] TABLE... visible only within the session in which
it was created. It is automatically deleted when the session is
terminated. Temporary tables can be accessed by more than one user."

Jamie says: I get a syntax error. Conclusion: this syntax is not
actually supported.

3) Multiple-field NOT NULL constraint (SQL DDL):

http://office.microsoft.com/en-gb/access/HP010322201033.aspx

"You can use NOT NULL...within a named CONSTRAINT clause that applies
to...a multiple-field named CONSTRAINT"

http://office.microsoft.com/en-gb/access/HP010322141033.aspx

[Quote selective]

Multiple-field constraint:
CONSTRAINT name
NOT NULL (notnull1[, notnull2 [, ...]])

notnull1, notnull2 The name of the field or fields that are restricted
to non-Null values.

[Unquote selective]

Jamie says: A syntax error for me, every time. Conclusion: this syntax
is not actually supported.

4) ON UPDATE SET NULL (SQL DDL):

http://office.microsoft.com/en-gb/access/HP010322141033.aspx

"The ON UPDATE SET NULL clause means that if a customer's identifier
(CustId) is updated in the Customer table, the corresponding foreign
key values in the Orders table will automatically be set to NULL."

Jamie says: the example syntax (and similar) generates an 'Invalid
argument' error. Conclusion: this syntax is not actually supported.

Jamie.

 

[Albert D. Kallal]

1) LIMIT TO nn ROWS (SQL DML):

http://office.microsoft.com/en-gb/access/HP030704831033.aspx

"Using the LIMIT TO nn ROWS clause to limit the number of rows
returned by a query"

I don't see that above "limit to" text in your link...I never seen that
syntax,
and I don't believe even sql server supports that. I just tried the above
link,
and I saw nothing about limit TO nn rows). You have to use "top n"
in sql server, and JET.

Conclusion: this syntax is not actually supported.

That seems correct, Sql, the MSDE, JET, and sql server 2000 don't have that
as
part of their syntax. That above link you provided said nothing about using
the "limit"key word. Did I missing something here? Could you explain
where in the above link the "limit to nn" rows is mentioned? As I said, this
is a propriety vendor specialty feature..and I don't believe even sql server
has
this feature...let alone JET...

2) CREATE TEMPORARY TABLE (SQL DDL):

http://office.microsoft.com/en-gb/access/HP010322201033.aspx

Yes, that either a documentation error, or some feature no one ever been
able to use.

Perhaps someone can chime in and mention if this applies to sql sever, but
the
above link does seem to be talking about JET sql. However, since it also
speaks of a "session", I suspect that this feature might only work inside of
a transaction, or perhaps you have to create a new workspace for the
TEMPORARY keyword to be used. Have you tried this by creating a
new session (copy of the currentdb object????). You could also
try a ado, but I never seen the temporary keyword used.....

I think the word "session" needs to be further explored here.

 

[Jamie Collins]

I don't see that above "limit to" text in your link...I never seen that
syntax,

That above link you provided said nothing about using
the "limit"key word. Did I missing something here? Could you explain
where in the above link the "limit to nn" rows is mentioned?

You have to expand the 'Why use ANSI-92 SQL?' sub topic e.g. choose
Show All. It's also quoted in the following KB article:

http://support.microsoft.com/kb/306250

As I said, this
is a propriety vendor specialty feature..and I don't believe even sql server
has
this feature...let alone JET...

The only SQL product I know of to use this syntax is Oracle Rdb.
Oracle SQL has ROWNUM which can be exploited to achieve the same.

Yes, that either a documentation error, or some feature no one ever been
able to use.

Perhaps someone can chime in and mention if this applies to sql sever, but
the
above link does seem to be talking about JET sql.

I'm pretty sure it doesn't. SQL Server uses a # table name prefix to
indicate a temporary table. CREATE TEMPORARY TABLE is valid *full*
SQL-92 but SQL Server is still only entry level compliant.

However, since it also
speaks of a "session", I suspect that this feature might only work inside of
a transaction, or perhaps you have to create a new workspace for the
TEMPORARY keyword to be used. Have you tried this by creating a
new session (copy of the currentdb object????). You could also
try a ado, but I never seen the temporary keyword used.....

I think the word "session" needs to be further explored here.

Good idea. Albert, you were my best bet as regards asking someone in
the Access team

Note that generally speaking ANSI-92 Query Mode and DAO (CurrentDB,
Workspaces, etc) do not generally mix e.g. can you use DAO for nested
transactions (I know you can using BEGIN TRANSACTION while in ANSI-92
mode)? They are not mutually exclusive, though e.g. the DECIMAL data
type enjoys DAO support and IIRC Allen Browne has an article
demonstrating ON DELETE CASCADE using DAO.

Thanks,
Jamie.

--

 

[Jamie Collins]

Note, for reference, ANSI-92 mode is not normally called
'Jet SQL'. The term 'Jet SQL' refers to the 'ANSI-89' mode.

I was not aware of that (I guess it would be Jet ANSI-92 SQL i.e.
provding a constext). Do you have a citation or is it more of a
'community convention' thing? TIA.

Please show us how you are invoking the SQL. To work
in [ANSI-92] mode you normally start with the Application
Connection property.

I'm using an ADODB.Connection object with the MS OLE DB Provider for
Jet 4.0 via VBA6 code in Excel 2003. However, I would expect the
results to be the same however the OLE DB provider is used, ditto
ANSI-92 Query Mode via the Access user interface (does that also use
the OLE DB provider or is it 'native' access?)

I'm not sure whether code that generates Jet syntax errors can be
regarded as proof of anything <g> but if it adds interest, here's some
example VBA6 code for each case (can be run from any VBA6 environment,
no references required e.g. paste into a standard module in the
Excel's Visual Basic Editor):

1) LIMIT TO nn ROWS (SQL DML):

Sub Myth1()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\Myth1.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Test1 (col1 INTEGER);"
.Execute _
"INSERT INTO Test1 (col1) VALUES (1);"
.Execute _
"INSERT INTO Test1 (col1) VALUES (2);"
.Execute _
"INSERT INTO Test1 (col1) VALUES (3);"

Dim rs
Set rs = .Execute( _
"SELECT col1 FROM Test1 LIMIT TO 2 ROWS;")

MsgBox .Errors(0) ' Blank error message
End With
Set .ActiveConnection = Nothing
End With
End Sub

2) CREATE TEMPORARY TABLE (SQL DDL):

Sub Myth2()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\Myth2.mdb"
With .ActiveConnection
.Execute _
"CREATE TEMPORARY TABLE Test1 (col1 INTEGER);"

MsgBox .Errors(0)
End With
Set .ActiveConnection = Nothing
End With
End Sub

3) Multiple-field NOT NULL constraint (SQL DDL):

Sub Myth3()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\Myth3.mdb"
With .ActiveConnection

' Single column named NOT NULL constraint
' does not error...
.Execute _
"CREATE TABLE Test1 (" & _
"col1 INTEGER" & _
" CONSTRAINT test1__not_null NOT NULL," & _
" CONSTRAINT test1__pk PRIMARY KEY (col1)" & _
");"

'...but nor does it create a constraint e.g. interrogate
' the SCHEMA CATALOG
Dim rs
Set rs = .OpenSchema(10) ' adSchemaTableConstraints
MsgBox rs.GetString

' Conclusion: the syntax is ignored by the parser
' rather than invoking any functionality.

' Multi column named NOT NULL constraint
' causes error
.Execute _
"CREATE TABLE Test2 (" & _
"col1 INTEGER," & _
"col2 INTEGER," & _
" CONSTRAINT test2__not_null" & _
" NOT NULL (col1, col2)" & _
");"

MsgBox .Errors(0)
End With
Set .ActiveConnection = Nothing
End With
End Sub

4) ON UPDATE SET NULL (SQL DDL):

Sub Myth4()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\Myth4.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Test1 (" & _
" col1 INTEGER NOT NULL PRIMARY KEY);"

.Execute _
"CREATE TABLE Test2 (" & _
" col1 INTEGER" & _
" REFERENCES Test1 (col1)" & _
" ON UPDATE SET NULL" & _
");"

MsgBox .Errors(0)
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[Larry Linson]

I was not aware of that (I guess it would be Jet ANSI-92 SQL i.e.
provding a constext). Do you have a citation or is it more of a
'community convention' thing? TIA.

If you have a copy of Access 2003, in the Database window, on the menu,
click "Help". In the menu dropdown, choose "Microsoft Access Help". In the
Help pane, under "Search For", enter "Jet SQL", and in the list of topics
returned, choose "Comparison of Microsoft Jet SQL and ANSI SQL".

Larry Linson
Microsoft Access MVP

 

[David W. Fenton]

I don't see that above "limit to" text in your link...I never seen
that syntax,
and I don't believe even sql server supports that. I just tried
the above link,
and I saw nothing about limit TO nn rows). You have to use "top n"
in sql server, and JET.

LIMIT is very common in server databases (it's in MySQL, for
instance).

The link about goes to an article about SQL89 vs. SQL92. My guess is
that LIMIT is SQL92, which is not the default for MDBs. You have to
do something or other to get SQL92 in an MDB (I think SQL92 support
was introduced in A2K3?).

 

[Jamie Collins]

If you have a copy of Access 2003, in the Database window, on the menu,
click "Help". In the menu dropdown, choose "Microsoft Access Help". In the
Help pane, under "Search For", enter "Jet SQL", and in the list of topics
returned, choose "Comparison of Microsoft Jet SQL and ANSI SQL".

Larry, you could have provided a URL for *all* Jet users, including
the Jet-no-Access users among us ;-) e.g.

http://office.microsoft.com/en-gb/access/HP010322501033.aspx

If you are using this article as an example where the term 'Jet SQL'
means ANSI-89 Query Mode and not ANSI-92 Query Mode then you have made
a mistake.

Consider the following quote from the article:

"Microsoft Jet SQL supports both ANSI SQL wildcard characters and
Microsoft Jet-specific wildcard characters to use with the Like
operator."

The article uses the term 'ANSI SQL' to refer to ISO/IEC 9075:1992 SQL
database language, commonly referred to as SQL-92; actually, it may be
referring to all ANSI/ISO SQL standards up to and including SQL-92,
noting that SQL-89 is forwards-compatible with SQL-92.

Examples of Microsoft Jet-specific wildcard characters are ? and * and
examples of ANSI SQL are _ (underscore) and %.

The above quote tells us that Microsoft Jet SQL supports ANSI SQL
wildcard characters and ANSI SQL wildcard characters are only
available while in ANSI-92 Query Mode. Therefore, this article's usage
of the term 'Microsoft Jet SQL' encompasses Jet's ANSI-92 Query Mode.

Further consider this quote:

expr1 [NOT] Between value1 And value2

In Microsoft Jet SQL, value1 can be greater than value2; in ANSI SQL,
value1 must be equal to or less than value2.

[Unquote]

I tested the following SQL code in Jet while in ANSI-92 Query Mode (I
used an ADODB.Connection in VBA code via the MS OLE DB Provider for
Jet 4.0):

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN NOW() AND DATEDIFF('M', -1, NOW());

I get no error, confirming that in Jet's ANSI-92 Query Mode value1 can
indeed be greater than value2, therefore confirming that the article's
usage of the term 'Jet SQL' encompasses Jet's ANSI-92 Query mode.

It seems you have missed the whole point of this article, which is
trying to convey that Jet, even while in ANSI-92 Query Mode, remains
non-compliant with the SQL-92 standard. It contrasts 'Microsoft Access
or DAO' (i.e. ANSI-89 Query Mode) with 'using the Microsoft OLE DB
Provider for Jet and Jet 4.X' (i.e. ANSI-92 Query Mode) but requires a
term to refer to both collectively and the one it uses is 'Jet SQL'.

I think you have read this article with the false premise that 'Jet
SQL' means ANSI-89 Query Mode only. With this premise, there are
sentences that do not make sense, such as the ones I've detailed
above. I suggest you try reading the article again but this time,
regardless of how sceptical you may be, with the premise that 'Jet
SQL' means both ANSI-89 Query Mode and ANSI-92 Query Mode. I think if
you can open your mind you will find that the article makes more sense
with the revised premise.

FWIW I blame the documentation for your error. I think the term 'ANSI
SQL' to refer to standard is too easily confused with 'ANSI-92' to
refer to a subset of _Jet_ SQL that is more compliant with the SQL-92
standard.

Just to bring this back on-topic, I notice the following quote:

"Microsoft Jet SQL does not support the following ANSI SQL features...
The LIMIT TO nn ROWS clause used to limit the number of rows returned
by a query. You can use only the WHERE Clause to limit the scope of a
query."

So this addresses 'Myth 1' from the OP. One section of the Help says
the syntax is available and another section (plus real life testing)
says it isn't!

Jamie.

--

 

[Jamie Collins]

The link about goes to an article about SQL89 vs. SQL92. My guess is
that LIMIT is SQL92, which is not the default for MDBs. You have to
do something or other to get SQL92 in an MDB (I think SQL92 support
was introduced in A2K3?).

Note that the term 'SQL-92' is usually used to refer to the ANSI/ISO
SQL standard. LIMIT TO nn ROWS is full SQL-92 standard syntax.

You probably meant to use the term 'ANSI-92', being a contraction of
Jet's ANSI-92 Query Mode. 'ANSI-92' is a bit of a misnomer because it
isn't even entry level SQL-92 compliant. Therefore, one cannot do
anything to get SQL-92 in Jet because the product is simply non-
compliant. FWIW the classic example of non-compliance is UPDATE in Jet
SQL (including ANSI-92 Query Mode), which does not support the SQL-92
syntax e.g.

UPDATE <table> SET <column> = (<scalar subquery>);

Instead, Jet has a proprietary UPDATE..JOIN syntax which is a
violation of the standards as regards table correlation names.

To get ANSI-92 Query Mode you must use the OLE DB provider for Jet 4.0
e.g. using ADO. In Access2002 and above it is possible to put the
Access user interface into ANSI-92 Query Mode to use certain Jet SQL
syntax e.g. CHECK constraints in SQL DDL.

SQL92, which is not the default for MDBs

For Access2007, ANSI-92 Query Mode appears to be the default, based on
my experience with the beta.

Jamie.

--

 

[Larry Linson]

Larry, you could have provided a URL for *all* Jet users

Troll.

 

[Jamie Collins]

Multiple-field NOT NULL constraint (SQL DDL):

Conclusion: this syntax
is not actually supported.

The DDL syntax may be mythical but the *functionality* exists e.g.

CREATE TABLE Test (
col1 INTEGER,
col2 INTEGER
)
;
CREATE INDEX test__multi_column_not_null
ON Test (col1, col2)
WITH DISALLOW NULL
;

Jamie.

--

 

[softpelt]

I learned a lot thanks yo you!!!
I kinda didn't get some of the words you were talking
about but i understand pretty much!
Keep sending messages!I love responding back to them!
I hope i didn't say anything wrong that would dissapoint
you!*-*^-^
Email me if you can!See you!!!!***^_^^_^