Thread: SQL in Access won't allow Multiple Tables using Create Table

Hi
I'm trying to create multiple tables in Access using SQL but an error keeps appearing. Access is not recognising the second Create Table command. Can you create multiple tables in access using SQL in the same query module?

The error is 'Syntax Error' 'Create Table Statement' Any clues?

Create Table Vehicle (

CarType CHAR (30) NOT NULL,
Registration Char(30),
Year Char(30),
Phone Char (13),
Fax Char(13),
ContactPerson Char(30) ) ;


Create Table Owner (
Name Char (30) NOT NULL,
NextOfKin Char(30) ) ;

I have not idea why it isn't working.


Thanks so much
Danny2000

Hi danny2000,

The short answer to your question about creating multiple tables would be "no".

Access re-writes your SQL statement into the QBE graphical query interface. Since the QBE is set only to interpret one valid query at a time, it'll choke if you put in more than one. You can get away with sub-queries using SELECT, but not with action commands like CREATE TABLE.

Since Access SQL uses the semi-colon to end the SQL statement, it'll see what you've written as a valid statement (up to the semi-colon) followed by it's interpretation of a Syntax error.

You'll need to write the CREATE TABLE queries as two separate statements/queries.

Cheers,