Restart from 1 AutoNumber field

[Mihail]

Hello !
I have more related tables:
T_1, T_2 .... T_k, T_k+1 .... T_n
Every table has a primary key defined as AutoNumber.
For k = 2 to n every table has a field defined as LookUp on T_k-1 primary key.
In the test phase I make new records and I remove records so, the primary key (AutoNumber) loose the natural order: (i.e. start at 287 and the next number is 301).

Question:
Is there an easy way (from VBA) to restore the AutoNumber field to start at 1 with step 1 ? Of course, the relation between related fields must remain the same. Hope you understand what I wish to say.

If the answer is NO, maybe you can teach me how to remove and restore the relationships between tables (from VBA)

I use MS OFFICE 2007.

Thank you !

[TheSmileyCoder]

I dont know if there is a VBA solution, however:

If the tables are empty (I.e. no records left) then doing a compact and repair operation will reset the autonumber.

[Mihail]

Thank you, TheSmileyCoder.
I don't know that but is a very good solution to send to the users a clean database.

However, this is not a solution for me. I alredy have a lot of records in my database and there are good (useful) records.

To solve my problem I imagine this scenario:
If in T_k the AutoNumber start at value START_k then in T_k+1 I can replace the values in the LookUp field: ActualValue = ActualValue - START_k + 1. This I can done without problems.

Now I need to remove the relationship between T_k and T_k+1 in order to redefine the primary key in T_k as NUMBER (not AUTONUMBER). If the relationship between tables is not removed an error occurs (that happens at manually but I think is the same if I use VBA).

The next step is to insert in T_k a new field, give this field a name and set it as AutoNumber. I do not use yet ALTERTABLE function but I think that this is the solution for this step.

Next step: Set this new field as primary key, remove the old primary key field, rename the new field with the same name as the removed one and restore the relationship between tables.

So, how to (from VBA):

1) Store for later use the relationship between two tables ? Of course with all the properties (in my case all the relationships preserve referential integrity and there are 1 to many), but, for generality, is better to store all the properties of relationship.

2)Remove the relationship between two tables ?

3)Set the relationship between two tables (with the stored properties)?

I am sure you understand that I try to create a mini program that work like in manual mode. I'll try to make it as generally as I could. Something like this:

Sub RefreshPrimaryKey(T_1,PrimaryField_1,inDataBase_1,T_2,LookUpField_2,inDataBase_2)
     'Code heare
End Sub

[TheSmileyCoder]

You could try taking a look at this link:
Save Relationshipview
I have not tried it myself, but I think it could be relevant for you.