Syntax error in CREATE TABLE statement - SQL (MS Access 2016)

Question

I took this example from my class textbook and pretty much copied word for word from the text. The following is the code that I wrote into MS Access:

CREATE TABLE PRODUCT(
  P_CODE VARCHAR(10) NOT NULL UNIQUE,
  P_DESCRIPT VARCHAR(35) NOT NULL,
  P_INDATE DATE NOT NULL,
  P_QOH SMALLINT NOT NULL, 
  P_MIN SMALLINT NOT NULL, 
  P_PRICE NUMBER(8, 2) NOT NULL,
  P_DISCOUNT NUMBER(5, 2) NOT NULL,
  V_CODE INTEGER,
  PRIMARY KEY (P_CODE),
  FOREIGN KEY (V_CODE) REFERENCES VENDOR ON UPDATE CASCADE
);

This code produces a syntax error every time it's run within MS Access.

I tried running this query to create the table PRODUCT within my db. When reviewing the code, I couldn't find anything specific that would have caused the error, but I could be wrong due to my inexperience with SQL.

Any help would be greatly appreciated!

Which column in table `Vendor` should be referenced? Have you tried something like `references vendor(column_name)` — Isolated, Nov 09 '22 at 20:46
**Why** are you using Access? MS Access's SQL engine is frozen-in-time sometime around 1997-2002 - it doesn't even support semicolon delimiters (which is part of your problem). It is not a suitable tool for learning SQL with because it supports only a small subset of SQL-92 and _nothing_ from subsequent standards (no CTEs, no window functions, no MERGE, no OUTPUT, no query batches, etc). — Dai, Nov 09 '22 at 20:48
vendor.V_CODE. The following is the code for the vendor table: CREATE TABLE VENDOR ( V_CODE INTEGER NOT NULL UNIQUE, V_NAME VARCHAR(35) NOT NULL, V_CONTACT VARCHAR(25) NOT NULL, V_AREACODE CHAR(3) NOT NULL, V_PHONE CHAR(8) NOT NULL, V_STATE CHAR(2) NOT NULL, V_ORDER CHAR(1) NOT NULL, PRIMARY KEY(V_CODE) ); — YoBoyAndy, Nov 09 '22 at 20:50
@Isolated I tried adding references vendor(v_code) to the last line but it still gives me the same syntax error. — YoBoyAndy, Nov 09 '22 at 20:57
@HansUp I am executing the code through a combination of either hitting the run button or changing the view to datasheet. If I'm misunderstanding your question I apologize. — YoBoyAndy, Nov 10 '22 at 01:15
You could define a field as `DECIMAL(8, 2)` if you execute your create statement with `CurrentProject.Connection.Execute` However I don't believe there is any way to make Access accept `NUMBER(8, 2)`. It does not make sense to me that you are expected to use that textbook example in MS Access --- it's not valid Access SQL. The assignment seems misguided. — HansUp, Nov 10 '22 at 02:12

score 0 · Answer 1 · answered Nov 10 '22 at 07:53

Per the comments the problem is the Decimal type. I'm updating the answer here for Access 2016: How do I create a decimal field in Access with Alter Table?

First enable the SQL Ansi 92 standard. In 2016 this has moved to File-Options-Object Designers-Query Design. It seems you can only enable it for new databases. So do so and create a new database then open the sql-tab of a blank query and paste the following code:

'make sure you have a VENDOR table first for instance:

CREATE TABLE VENDOR
(V_CODE  AutoIncrement  CONSTRAINT PrimaryKEY PRIMARY KEY);

'Then with slightly less old syntax (varchar would usually be text, smallint would usually be integer with a size, and PrimaryKey includes Not null and unique)

CREATE TABLE PRODUCTS(
  P_CODE VARCHAR(10) CONSTRAINT PrimaryKey PRIMARY KEY,
  P_DESCRIPT VARCHAR(35) NOT NULL,
  P_INDATE DATE NOT NULL,
  P_QOH SMALLINT NOT NULL, 
  P_MIN SMALLINT NOT NULL,
 P_PRICE DECIMAL(8, 2) NOT NULL,
  P_DISCOUNT DECIMAL(5, 2) NOT NULL,
V_CODE LONG REFERENCES VENDOR(V_CODE)
);

'Alter Table also works now

ALTER TABLE PRODUCTS ADD COLUMN P_PRICE DECIMAL(8,2) NOT NULL;

Caveat At least the sql-pane tells you that something in your DDL is wrong if not what. if you are not using the default Jet database as your backend then you are stuck concatenating strings using vba and ADO according to here: https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/create-table-statement-microsoft-access-sql

At that point I would manually try manually adding the decimal columns if there where not too many.

Syntax error in CREATE TABLE statement - SQL (MS Access 2016)

1 Answers1