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:
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?
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