Microsoft Jet SQL Reference

[Chris Smith]

I can locate help with subject topic in my local Access 2003 documentation,
but it's nowhere to be found on msdn.microsoft.com. Please send link.
Lots of non-relevant stuff when I searched on MSDN, though.
I'm quite challenged by the CONSTRAINT clause documentation. In particular,
this example from the documentation doesn't seem to work:
-8<--------8<------
You can specify actions to be performed on the foreign table based on a
corresponding action performed on a primary key in the table on which the
CONSTRAINT is defined. For example, consider the following definition for the
table Customers:
CREATE TABLE Customers (CustId INTEGER PRIMARY KEY, CLstNm NCHAR VARYING
(50))


Consider the following definition of the table Orders, which defines a
foreign key relationship referencing the primary key of the Customers table:

CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes
NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId)
REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE
---8<-------8<-----

Even s/NCHAR VARYING (255)/VARCHAR(255)/ just got me to a parse error in
the constraint clause.
Basically, I'm writing my database as a text file, and doing some
hack-tackular stuff to it in a script, such that it builds against MSAccess,
or SQLServer, but the Jet4.0 SQL DDL seems to suffer from a popularity
deficit...
Thanks,
Chris

 

['69 Camaro]

Hi, Chris.

I can locate help with subject topic in my local Access 2003 documentation,
but it's nowhere to be found on msdn.microsoft.com. Please send link.

The entire Jet 4.0 SQL Reference is on your hard drive in the following
location if you installed MS Office in the default directories (watch out for
word wrap):

"C:\Program Files\Common Files\Microsoft Shared\OFFICE11\1033\JETSQL40.CHM"

For more insight on using DDL for Jet 4.0 (and a few more examples), read
the SQL Server BOL. One can download it from this Web page:

http://www.microsoft.com/downloads/...b1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en

I'm quite challenged by the CONSTRAINT clause documentation. In particular,
this example from the documentation doesn't seem to work:

One must set the SQL Server Compatible Syntax (ANSI SQL-92) option for the
current database before this syntax will work in the SQL View pane.

CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes
NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId)
REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE)

One must set the SQL Server Compatible Syntax (ANSI SQL-92) option for the
current database before this syntax will work in the SQL View pane, too.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Michel Walsh]

Hi,


Try ADO, not DAO (not the designer built around DAO). As example, in the
Immediate Window:

CurrentProject.Connection.Execute "CREATE TABLE Customers (CustId INTEGER
PRIMARY KEY, CLstNm NCHAR VARYING (50)) "

work quite fine.

Using DAO won't work, as you can try with

CurrentDb.Execute "CREATE TABLE Customers2 (CustId INTEGER PRIMARY KEY,
CLstNm NCHAR VARYING (50)) "


Most of Jet 4.0 extensions require ADO, and don't work with DAO. You can
mix the use of DAO and ADO, but sure, there can be some problem, such as
transactions that are maintained independently on each "system", in general.



Hoping it may help,
Vanderghast, Access MVP

 

[david epsom dot com dot au]

Notes:

CurrentDb.Execute "CREATE TABLE Customers2 (CustId INTEGER PRIMARY KEY,
CLstNm NCHAR VARYING (50)) "

CurrentDB.Execute "CREATE TABLE Customers2 (CustId Long
CONSTRAINT Customers2_PK PRIMARY KEY ,
ClstNn text(50)) "

NOT THAT i DISAGREE WITH THE CONCLUSION.

here is a comparison of a simple sql server, ado jet and
dao jet ddl action:

DAO/Jet:
ALTER TABLE tblBS ADD COLUMN datChange DATETIME NULL

SS:
ALTER TABLE tblBS ADD datChange SMALLDATETIME DEFAULT getdate()

ADO/Jet
ALTER TABLE tblBS ADD datChange SMALLDATETIME DEFAULT now()

Note that DAO/Jet did not allow default function.
(david)

 

[Chris Smith]

One must set the SQL Server Compatible Syntax (ANSI SQL-92) option for the

current database before this syntax will work in the SQL View pane.

Ah, so! Ancient Chinese secret!
Possibly someone could nudge the Elders of Redmond to note this wee fact in
the documentation...

 

['69 Camaro]

Ah, so! Ancient Chinese secret!

Of course. ;-)

Possibly someone could nudge the Elders of Redmond to note this wee fact
in
the documentation...

Don't feel bad. The documentation used to leave out a great deal more of
the important information. And remember that the people who know the most
ancient Chinese secrets usually get paid more than the people who don't know
these secrets.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.