Using a MS Access query to auto increment my primary key

0
votes

I am working with a MS Access database that has no primary keys in the tables (I'm well aware this is a terrible practice). The database is built on 4 linked tables (data is imported from excel files daily) and queries which create temporary tables. I have used alter table to add a primary key to one of the created tables. Therefore, the table is generated daily through a query, a primary key is then added. Is there anyway so that it will auto increment? As it stands my table has 15k records yet my primary key column is empty. It's essential for new queries in order to create joins etc. I do not know VBA.

I have tried this (and similar variations)

Alter Table Unified_Backlog_Shipping_Table
Add P_Key Number AUTOINCREMENT;
2
sqlms-accessms-access-2007
You can't change a column to autonumber once it already has data in it. You could add a new column that is autonumber. - Wayne G. Dunn
The column has no data in it. I want to add a column that is autonumber, but I haven't been able to for some reason as of yet. Thanks for your response. - Eoin2211

2 Answers

1
votes

You must declare the AUTOINCREMENT field as PRIMARY KEY in order for it to behave correctly. Just AUTOINCREMENT by itself is not enough.

This example from the Immediate window in Access 2010 accomplishes what I think you want: the autonumber field is added and is populated for any existing rows.

strSQL = "ALTER TABLE Unified_Backlog_Shipping_Table" & vbCrLf & _
    "ADD COLUMN P_Key AUTOINCREMENT PRIMARY KEY;"
CurrentDb.Execute strSQL
1
votes

I worked it out myself. If anybody has a similar problem, it was the order of the words that was the problem. 

ALTER TABLE Unified_Backlog_Shipping
ADD P_Key AUTOINCREMENT PRIMARY KEY;