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)
);
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.
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.
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
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)
);
