CODEWITHSUNDEEP
sqlms-access
Ameer Akashe
Ameer Akashe

Reputation: 103

create table syntax error in Microsoft Access?

Here is my code, i'm trying to make multiple tables:

Create Table Order_t
(
Id AutoIncrement Not Null,
OrderDate DateTime Not Null,
CustId Int Not Null,
Primary Key(Id),
Foreign Key(CustId) References Customer_t(Id)
(;
Create Table PersonRole_t
(
PersonRoleID Autoincrement Not Null,
Person_ID int Not Null,
Primary Key(PersonRoleID, Person_ID),
Foreign Key(Person_ID) References Person_T(Person_ID)
(;
Create Table Product_t
(
Id Text(10) Not Null,
Name Text(30) Not Null,
Description Text(30),
Finish Text(30),
UnitPrice Currency Not Null,
Primary Key(Id)
) ;

Whenever I run it in Microsoft Access, I get an error in the CREATE TABLE statement (it highlights the PersonRole_T table definition). Not sure what to do, rather new to SQL.

Upvotes: 0

Views: 483

Answers (2)

onedaywhen
onedaywhen

Reputation: 57093

The table Person_T with a key of Person_ID needs to exist before the References Person_T(Person_ID) statement can execute. Based on your naming convention, I would guess the statement should rather be References Person_T(Id).

Consider changing your naming convention so that a data element does not change its name depending its location realtive to tables/views. Also consider whether the _t suffix is worth the bother.

@Prdp's point about the close parens is valid too.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

Use CLOSE parenthesis at the end of CREATE Table statement instead of OPEN parenthesis

Create Table Order_t
(
Id AutoIncrement Not Null,
OrderDate DateTime Not Null,
CustId Int Not Null,
Primary Key(Id),
Foreign Key(CustId) References Customer_t(Id)
); -- Not (;

Create Table PersonRole_t
(
PersonRoleID Autoincrement Not Null,
Person_ID int Not Null,
Primary Key(PersonRoleID, Person_ID),
Foreign Key(Person_ID) References Person_T(Person_ID)
); -- Not (;

Upvotes: 1

Related Questions