Geek Answers Handbook

Access SQL - ALTER COLUMN in AutoNumber?

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

I tried to follow without 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 );

Every time I get the same syntax error problem, and it highlights the last word in SQL.

+7
sql ms-access
source share
1 answer

To query Data Definition (DDL) in Access, you use COUNTER to define the AutoNumber field. You tried to use both Integer and COUNTER in the same field, and this will not work.

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

 ALTER TABLE PERSON ALTER COLUMN PERSON_ID COUNTER PRIMARY KEY

Please note that in order for this statement to work

  • the table should be empty, and
  • The table should not have a primary key, even in the [PERSON_ID] field.

If the table already has rows, Access will not allow you to convert the Numeric (Long Integer) field to AutoNumber . In this 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
TEXT PERSON_NAME (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
+8
source share

More articles:


All Articles