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?

Answer 1

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