Re: Microsoft Jet SQL Reference

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



"Chris Smith" <ChrisSmith@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B55F8C82-6968-44DF-A3F4-ED4094D17847@xxxxxxxxxxxxxxxx
>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
>
>


.

Relevant Pages

  • Re: Microsoft Jet SQL Reference
    ... CONSTRAINT Customers2_PK PRIMARY KEY, ... CLstNm NCHAR VARYING ) " ... >> CREATE TABLE Customers (CustId INTEGER PRIMARY KEY, ...
    (microsoft.public.access.queries)
  • RE: Sync 3 related tables on a form
    ... You can try to maintain a multi query join string for the same purpose. ... Create a primary key in the Customers table. ... Create a relationship between the Orders table and the OrderDetails ...
    (microsoft.public.vb.database)
  • RE: How we get record from two database and insert into other database
    ... Create a primary key in the Customers table. ... Create a relationship between the Orders table and the OrderDetails ... The data type is string for this element, ...
    (microsoft.public.vb.database)
  • Re: Primary Key Dilemma
    ... > I'm designing an application that will be sold to multiple customers, each> of which is a small business. ... The database behind this application will be> accessed via the Internet, and will be shared by *all* customers. ... Each> business is permitted to access only its own data. ... It just doesn't seem right to me to have an auto inc column AND a> separate business_id column comprising the primary key. ...
    (microsoft.public.sqlserver.programming)
  • Re: Postpone creation of attributes until needed
    ... create table b, create pseudo column on table a, link to column on ... Assume a Customers table and an Invoices table. ... Users will never see the primary key, ...
    (comp.lang.python)
Loading