create table syntax error in Microsoft Access?

Question

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.

Pரதீப் · Accepted Answer · 2016-10-10 04:18:10Z

1

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 (;

answered Oct 10, 2016 at 4:18

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

onedaywhen Over a year ago

Just to state the obvious for the OP, Access SQL syntax does not support comments so the -- Not (; part needs to be removed when being executed.

onedaywhen · Accepted Answer · 2016-10-10 07:34:47Z

0

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.

answered Oct 10, 2016 at 7:34

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Collectives™ on Stack Overflow

create table syntax error in Microsoft Access?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related