CODEWITHSUNDEEP
sqlms-accessms-access-2010
ummlayth
ummlayth

Reputation: 65

Create Table Syntax Error - MS Access, SQL view

I keep getting CREATE TABLE Syntax Error, but I don't see the error! What is causing the error?

My SQL: 

CREATE TABLE my_employee
(
employee_id INTEGER PRIMARY KEY NOT NULL,
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(30) NOT NULL,
address VARCHAR(10) NOT NULL,
birthdate DATE,
salary NUMERIC(8,2) DEFAULT 15000,
marital_status CHAR(1)
);

Upvotes: 1

Views: 7352

Answers (4)

Timo Verhoeven
Timo Verhoeven

Reputation: 99

As HansUp said: "default" in DDL doesn't work here. As an alternative you can create the table without the default first and add the default via the TableDef afterwards:

CurrentDb().Execute "create table my_employee ..."
CurrentDb().TableDefs("my_employee").Fields("salary").DefaultValue = 15000

Upvotes: 1

HansUp
HansUp

Reputation: 97131

Since your DDL statement includes DEFAULT, you must execute it with ADO. I loaded your statement into a string variable and executed it from Access 2007 like this:

CurrentProject.Connection.Execute strSql

The salary field is decimal with precision 8, scale 2, and default 15000.

DEFAULT is one of the Access SQL features added with Jet 4.0. Those features are not available for a statement executed from DAO. If you are using Access' query designer to create and execute the statement, you're using DAO. Same if you were using CurrentDb.Execute. But CurrentProject.Connection is an ADO object, so it can .Execute Jet 4.0 features.

Note NOT NULL is not necessary after PRIMARY KEY since PRIMARY KEY implies NOT NULL. However PRIMARY KEY NOT NULL does not trigger an error. The statement works as you originally wrote it as long as you execute it from ADO.

Upvotes: 5

RAFIA
RAFIA

Reputation: 11

Well I was having the same problem with Ms Access 2007, but I solved it later on.
It is because actually some features are disabled by default for security reasons over there.
When it shows you syntax error, you can see the message at menu bar somewhere or at the bottom: Some Features Are Disabled For Security Reasons....
Click on the message then proceed to enable further features.

Upvotes: 1

Dirker27
Dirker27

Reputation: 1

Your problem is in your PRIMARY KEY declaration

CREATE TABLE my_employee
(
employee_id INTEGER NOT NULL,
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(30) NOT NULL,
address VARCHAR(10) NOT NULL,
birthdate DATE,
salary NUMERIC(8,2) DEFAULT 15000,
marital_status CHAR(1),
PRIMARY KEY (employee_id)
);

Upvotes: 0

Related Questions