Column defined as auto-increment required on insert

Question

I'm trying to define a MS Access table through their SQL DDL. I have plenty of experience with traditional SQL, but the access specific language is throwing me for a loop.

CREATE TABLE DemoTable (
    Demo_id IDENTITY PRIMARY KEY,
    Demo_nm VARCHAR(200) NOT NULL
);
=> Query executed successfully. 0 rows were affected.

I've tried AUTOINCREMENT, COUNTER and IDENTITY(1,1) etc.

INSERT INTO DemoTable VALUES('Some Name');
=> Number of query values and destination fields are not the same.

This is because it expects a Demo_id value.

INSERT INTO DemoTable VALUES(5, 'Some Name');
=> Query executed successfully. 1 rows were affected.

Everything else is created correctly.. an index is created for the Demo_id and Demo_nm is not nullable.

My reference:

How to: Modify a Table's Design Using Access SQL

Answer 1

This works for me:

CREATE TABLE DemoTable (
    Demo_id Counter PRIMARY KEY,
    Demo_nm VARCHAR(200) NOT NULL)

Then

INSERT INTO DemoTable (   
Demo_nm ) Values ("abc")