Access: How to alter column to counter with existing rows in table?

Question

I know that it can't be easily solved by this statement:

ALTER TABLE BLOCK ALTER COLUMN block_id COUNTER(5000,1) PRIMARY KEY; 
-- I get here error saying about wrong column type

Because of 2 reason:

the table must be empty
the table must not already have a Primary Key

I have such simplified scheme:

I need to make in block table block_id column and in record table record_id column to be a counter. Source_id column in Source is already a counter. The problem here is that tables have connection like shown in the attached picture. And all this 3 tables are filled with data.

Max(block_id)  = 4129
Max(record_id) = 9047

And I want to make this columns to be a counter starting from 2 values written before. Is there any solution without making temp tables?

score 0 · Accepted Answer · answered Sep 01 '15 at 11:35

0

It is not possible to do this without temp tables. The Primary Key is not the problem, but you can never change an INT field to AutoNumber if there is data.

You need to use an Append query (INSERT INTO) to copy the existing IDs into the new table with the AutoNumber.

This link may give you some ideas: How to reset an AutoNumber field value in Access

answered Sep 01 '15 at 11:35

Andre

26,751
7
36
80

I will handle by creating additional columns and then replace original `id` columns – Rocketq Sep 01 '15 at 11:48
I'm afraid that won't work either - you can't UPDATE an AutoNumber column, you can only INSERT it. – Andre Sep 01 '15 at 12:03
But I did it by updating columns without counter – Rocketq Sep 01 '15 at 12:12

Access: How to alter column to counter with existing rows in table?

1 Answers1