Proper Foreign Key Syntax?

[nomadsoul]

Hi I can't find the proper sql syntax for defining the FK. I'm creating two tables; customers and orders. In the orders tables the cust_ID is the FK. Well, I thought the syntax below was correct but it wasn't accepted when I ran the Create Orders Table statement. If this is not the proper FK reference can someone tell me what is? I've looked every where. I want to use strict sql and avoid any query builders and such. I can't find any good access sql syntax resources either.
Thank You.
-v2007

The customer table:
Create Table Customers
(
Cust_ID int NOT NULL Primary Key,
LastName varchar(20) NOT NULL,
FirstName varchar(20),
Street varchar(40),


State char(2),
Phone varchar(15),
PostCode varchar(20)
);

The Orders Table:
Create Table Orders
(
Ord_ID int NOT NULL Primary Key,
OrderNO int NOT NULL,
ShippedVia varchar(30),
ShipAddress varchar(55),
ShipCity varchar(30),
ShipRegion varchar(1),
DateOfDelivery DATETIME,
Cust_ID int Foreign Key References Customers(Cust_ID)
);

The error high-lights the keyword 'Foreign' as when I run it. But there's no problem creating the customer table.

[NTC]

As you self describe yourself as novice - I am wondering why you seek to create tables via a code method?

[John_G]

Here you are - try this:

currentdb.execute "Create Table Orders (
Ord_ID int NOT NULL Primary Key,
OrderNO int NOT NULL,
CustID Int,
ShippedVia varchar(30),
ShipAddress varchar(55),
ShipCity varchar(30),
ShipRegion varchar(1),
DateOfDelivery DATETIME,
CONSTRAINT FKOrdersCustID Foreign Key(CustID) References Customers );

It worked when I tried it in A2010.

I found all the information I needed in the MS Access VBA help, searching on "foreign Key"

[orange]

I agree with NTC. This is your first post-- why are you creating tables via code?

Have you done a database design/erd?
When you show code it is best to show all procedure code.

You may find w3schools sql a helpful sight.

Good luck with your project, and welcome to the forum.

[nomadsoul]

Hi NTC
I'm not really a db or sql novice -- only an Access novice. I'm actually teaching a general db class at and I want them to understand the foundations through SQL before we expand our class project. I'm having my students use Access because most already have it on their drives. Last time I used it was when I was a student (2003) since then I've favored MySQL and SQL Server. I like Access but find it harder to use than before.
John:
I tried it and it works, thanks!
I also successfully tried this and it worked:

Create Table Orders
(
Ord_ID int NOT NULL Primary Key,
Cust_ID int NOT NULL,
OrderNO int NOT NULL,
ShippedVia varchar(30),
ShipAddress varchar(50),
ShippedCity varchar(30),
ShipRegion Varchar(1),
DateOfDelivery DATETIME,
Foreign Key (Cust_ID) References Customers(Cust_ID)
);

Orange:
Thanks, actually I tried w3c first and got the original syntax:
Cust_ID int Foreign Key References Customers(Cust_ID)
Modeled from:P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
from this page:
http://www.w3schools.com/sql/sql_foreignkey.asp

I also trust this resource so I'm really curious why it didn't work.

Thanks for the great replies, I hope to learn much more from this forum.