1

I'm trying to create 3 access tables in SQL view on Microsoft Access but whenever I try to execute it, I receive the following error. 'Syntax Error in CREATE TABLE statement'.

Please find my code below.

CREATE TABLE Book (
Book_ID int,
Book_Title varchar (30),
PRIMARY KEY (Book_ID)
);

CREATE TABLE Users (
User_ID int,
User_Name varchar (30), 
PRIMARY KEY (User_ID)
);

CREATE TABLE Borrows (
User_ID int,
Book_ID int,
B_ID int,
PRIMARY KEY(B_ID),
FOREIGN KEY(User_ID) REFERENCES Users(User_ID),
FOREIGN KEY(Book_ID) REFERENCES Book(Book_ID)
);

INSERT INTO Book VALUES (101, 'The Hobbit'), (102, 'To Kill a Mockingbird');

INSERT INTO Users VALUES (1, 'Stephen'), (2, 'Tom'), (3,' Eric');

INSERT INTO Borrows VALUES (3, 102, 1), (1, 101, 2);

Appreciate any feedback I can get, have a good day.

Improve this question
1
  • 1
    You also can ONLY put one full create statement in the query builder at a time - multiple statements are not supported. As noted, you have to use correct table create syntax. So the syntax to create a table for Oracle, MySQL, SQL server or Access are all different from different vendors - you have to use ms-access syntax for the table create commands. Below answer has a link on the syntax. Commented Aug 15, 2021 at 16:25

2 Answers 2

Reset to default
1

Your first CREATE TABLE executed flawlessly from the query designer in Access 2010. However my preference is to include the PRIMARY KEY constraint as part of the field definition:

CREATE TABLE Book (
Book_ID int PRIMARY KEY,
Book_Title varchar (30)
);

That variation also executed successfully.

I suspect you have at least 2 issues to resolve:

  1. Access does not allow you to execute more than one SQL statement at a time (as Heinzi and Albert mentioned). You must execute them one at a time.
  2. In Access, INSERT ... VALUES can only be used to add one row at a time. Revise your inserts accordingly.

IOW, split the first one into 2 statements which you then execute individually:

-- INSERT INTO Book VALUES (101, 'The Hobbit'), (102, 'To Kill a Mockingbird');
INSERT INTO Book VALUES (101, 'The Hobbit');
INSERT INTO Book VALUES (102, 'To Kill a Mockingbird');

Then split and execute the remaining inserts similarly.

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

Comments

0

Your code example use SQL Server (T-SQL) syntax, not MS Access syntax.

The syntax for Access' CREATE TABLE statement is documented here:

The most obvious differences seem to be there is no varchar type and that PRIMARY KEY needs a constraint name if specified in an extra line. There might be more, see the article and its examples for details. I also suggest that you submit your statements one-by-one, instead of submitting a complete ;-separated batch; I'm not sure Access queries even support the latter.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.