I'm trying to create a table in MS Access using SQL via OLEDB JET interface. I need an ID field which is not null and auto-increments on insert

Question

This is in my connection string. "Provider = Microsoft Office 12.0 Access Database Engine OLE DB Provider;" My understanding is that the JET connection limits the sql syntax needed.

This string creates an ID field that is "Not Null" and "Unique" but it does not auto increment. It also requires me to include a value for each insert. I need a solution that automatically increments and does not require a value for an insert command.

CREATE TABLE [IC]
(
    [ID] INT UNIQUE NOT NULL,     <<--- I need help here
    [Part Number] VARCHAR(255),
    [Manufacturer] VARCHAR(255)
);

I think the solution may be on this page but I haven't found the right combination to solve the problem yet. CONSTRAINT Clause (Microsoft Jet SQL)

It's the datatype you need, IIRC. I think it's AUTO. Something like [ID] AUTO NOT NULL, from memory. — simon at rcl, Jan 10 '22 at 17:44
I added the word "AUTO" to that statement and tried it in a few places. It didn't seem to do the trick. Does it need to be something different like "AUTOMATIC" — Eric Minnaert, Jan 10 '22 at 17:51
I looked it up. It's AutoNumber. Do a search on it: you need to understand a bit about how it works. — simon at rcl, Jan 10 '22 at 18:28

score 0 · Answer 1 · edited Jan 11 '22 at 02:39

0

This worked, I had to replace the INT keyword:

CREATE TABLE [IC]
(
    [ID] AUTOINCREMENT UNIQUE NOT NULL,
    [Part Number] VARCHAR(255),
    [Manufacturer] VARCHAR(255)
);

edited Jan 11 '22 at 02:39

Sercan

4,739
3
17
36

answered Jan 11 '22 at 00:01

Eric Minnaert

57
8

I'm trying to create a table in MS Access using SQL via OLEDB JET interface. I need an ID field which is not null and auto-increments on insert

1 Answers1