CODEWITHSUNDEEP
sqlms-access
WillNZ
WillNZ

Reputation: 765

Access SQL - ALTER COLUMN to AutoNumber?

How can you alter a table in MS Access using SQL to change a data type to AutoNumber?

I have tried to following with no success

ALTER TABLE PERSON ALTER COLUMN PERSON_ID Integer PRIMARY KEY counter
);
ALTER TABLE PERSON ALTER COLUMN PERSON_ID Integer PRIMARY KEY AUTONUMBER
);
ALTER TABLE PERSON ALTER COLUMN PERSON_ID Integer PRIMARY KEY AUTOINCREMENT
);

Each time I get the same issue "Syntax error" and it highlights the last word in the SQL.

Upvotes: 10

Views: 27624

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123419

For a Data Definition (DDL) query in Access you use COUNTER to define an AutoNumber field. You were trying to use both Integer and counter on the same field, and that won't work.

I just tried this and it worked for me in Access 2010:

ALTER TABLE PERSON ALTER COLUMN PERSON_ID COUNTER PRIMARY KEY

Note that in order for this statement to work

  • the table must be empty, and
  • the table must not already have a Primary Key, not even on the [PERSON_ID] field.

If the table already has rows in it then Access will not allow you to convert a Numeric (Long Integer) field to AutoNumber. In that case you need to create a new table with the AutoNumber Primary Key and then insert the rows from the old table into the new table.

For example, for an existing table named [PERSON] with columns

PERSON_ID INTEGER
PERSON_NAME TEXT(50)

you need to create a new table

CREATE TABLE PERSON_NEW (PERSON_ID COUNTER PRIMARY KEY, PERSON_NAME TEXT(50))

and then copy the records over

INSERT INTO PERSON_NEW
SELECT * FROM PERSON

Upvotes: 18

Related Questions