SQL for MS Access Syntex issue

1240 pts.
Tags:
Microsoft Access
SQL
I have started to read the book by Cecelia L. Allison and Neal A Berkowitz on SQL for Microsoft Access.  and there is a syntex listed below that has been giving me a problem.   I get an error with the word UNIQUE.  So I removed it from the statement and then I get an error with the ON section at the end of the statements.  Why is that.  I am using MS Access 2000. 

 CREATE TABLE Manufacturers ( ManufacturersID INTEGER CONSTRAINT ManfID PRIMARY KEY, ToyID INTEGER NOT NULL, CompanyName CHAR (50) NOT NULL, Address CHAR (50) NOT NULL, City CHAR (20) NOT NULL, State CHAR (2) NOT NULL, Postalcode CHAR (5) NOT NULL, Areacode CHAR (3) NOT NULL, Phonenumber CHAR (8) NOT NULL, CONSTRAINT ToyFk FOREIGN KEY (ToyID) REFERENCES Toys (ToyID) ON UPDATE CASCADE ON DELETE CASCADE );

 



Software/Hardware used:
MS 2000

Answer Wiki

Thanks. We'll let you know when a new response is added.

I don’t know much about MS Access, but I think some statements can only be executed through ADO, and will throw errors when trying to execute them from the SQL View user interface.

So, I would try creating a module, with some code like this:

<pre>Sub CreateTable()

CurrentProject.Connection.Execute _
“CREATE TABLE Manufacturers” _
& “( ManufacturersID INTEGER CONSTRAINT ManfID PRIMARY KEY, ” _
& “ToyID INTEGER NOT NULL, ” _
& “CompanyName CHAR (50) NOT NULL, ” _
& “Address CHAR (50) NOT NULL, ” _
& “City CHAR (20) NOT NULL, ” _
& “State CHAR (2) NOT NULL, ” _
& “Postalcode CHAR (5) NOT NULL, ” _
& “Areacode CHAR (3) NOT NULL, ” _
& “Phonenumber CHAR (8) NOT NULL, ” _
& “CONSTRAINT ToyFk FOREIGN KEY (ToyID) REFERENCES Toys (ToyID) ON UPDATE CASCADE ON DELETE CASCADE )”

End Sub</pre>

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • msi77
    This should work: CREATE TABLE Manufacturers ( ManufacturersID INTEGER CONSTRAINT ManfID PRIMARY KEY, ToyID INTEGER NOT NULL, CompanyName CHAR (50) NOT NULL, Address CHAR (50) NOT NULL, City CHAR (20) NOT NULL, State CHAR (2) NOT NULL, Postalcode CHAR (5) NOT NULL, Areacode CHAR (3) NOT NULL, Phonenumber CHAR (8) NOT NULL, CONSTRAINT ToyFk FOREIGN KEY (ToyID) REFERENCES Toys); 1. ON DELETE/UPDATE is not supported by dialect (only via GUI). 2. Primary Key in REFERENCES Toys need not be listed
    1,670 pointsBadges:
    report
  • carlosdl
    Yes. Thanks. I think I should have clarified my answer a little. 1) The CREATE TABLE statement would work from the SQL View user interface if the ON UPDATE/DELETE CASCADE part is not present in the CONSTRAINT clause (but you won't have cascading updates/deletes). If you need that functionality you would have to execute the CREATE TABLE statement or an additional ALTER TABLE statement (to add the constraint) through ADO. 2) Primary key fields don't need to be specified for the referenced table, but won't cause any errors if they are.
    70,220 pointsBadges:
    report
  • CompEng
    in the statement of the phonenumber I would like to make this a unquie statement. How would this be accomplished? And what do you mean by (1) below ON DELETED/UPDATE is not supported by dialect (only via GUI)?
    1,240 pointsBadges:
    report
  • CompEng
    So why would the author of the book have created the syntex the way it is if it would not work with Access?
    1,240 pointsBadges:
    report
  • carlosdl
    Hmmm. I don't know ;-) It works with Access, but not from the user interface (I might be some way to make it work, but I'm not an Access expert). I found this on MSDN (Intermediate Microsoft Jet SQL for Access 2000) "SQL Enhancements In Access 2000, many enhancements were made to the SQL implementation in the Microsoft Jet 4.0 data engine in order to support new features of Access, to conform more closely to the ANSI-92 standard, and to allow for greater compatibility between Access and Microsoft® SQL Server™. The Jet database engine now has two modes of SQL syntax: one that supports previously used SQL syntax, and one that supports the new SQL syntax. It is very important to note that some of the new SQL syntax is available in code only when you use ActiveX® Data Objects (ADO) and the Jet OLE DB provider, and is not currently available through the Access SQL View user interface or DAO. This article points out when a certain SQL command is available only through the Jet OLE DB provider and ADO." To create the unique constraint: 1)
    Phonenumber CHAR (8) NOT NULL UNIQUE, 
    or 2)
    Phonenumber CHAR (8) NOT NULL CONSTRAINT myConstraint UNIQUE,
    or using an ALTER TABLE statement.
    70,220 pointsBadges:
    report
  • msi77
    : So why would the author of the book have created the syntex the way it is if it would not work with Access? Maybe this example will work via ADO in a module. But will not in SQL query console.
    1,670 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following