Populate foreign key from Autonumber

[Wyoming]

Hello guys,

I have two Excel spreadsheets which contain data to be imported into Access. They are 1-n related.
Parent table has a unique key field [ID_LEAD]
The child table has its own ID field and also the [ID_LEAD] field as a foreign key. There are n records in the child table for each record in the parent table.

The parent table is to be imported into an Access table where the ID_LEAD is an Autonumber field, so the original values of the Excel file will be replaced by the Autonumber ones.
This brakes the relationship between the 2 tables, since the values of ID_LEAD change in the parent table.

Is there any not-too-complex way of updating the values of ID_LEAD in the child table with the new values of the Autonumber field in the Parent table?
This update could be done before or after the import of the child table. I don't care as long as it works.

I believe that being able to automatically identify the ID_LEAD value of all records that have been imported from the Parent table would be enough to sort this out.

Thanks a lot

[HansV]

If the values of ID_LEAD in the parent Excel sheet do not occur in the Access table, they will be preserved without change when you import the parent sheet into the parent table. So the relationship between parent and child should be OK.

[Wyoming]

Thank you, Hans
You are right, the relationship is still there, but in the child sheet the ID_LEAD column will still be referring to the original ID_LEAD values, how can I update them to the new ones? or, in other words, how can I know or exctract the values of the ID_LEAD field for the new records of the parent table?
I would like to be able to do all this automatically so that by pressing one button the parent table is imported and then the child table is imported but for that I need to update the ID_LEAD field in the child table.

[HansV]

Sorry, I don't understand. The ID_LEAD values in Access should be the same as those in Excel, so there shouldn't be any need for updating them.

[Wyoming]

The problem comes when importing the parent table into Access.
In Access the ID_LEAD field is an Autonumber field so when I import the table it will automatically generate new values for this field so the original ID_LEAD values will be replaced by the ones generated by the autonumber.
After that, I would need to update the ID_LEAD values of the child table so that they match the new ones generated by the autonumber.
So, for instance, if the values of ID_LEAD in the parent table are:
1
2
3
But when I import the table into Access the autonumber changes them into:
10
11
12
I need to update the ID_LEAD values in the child table so that 1 is updated to 10, 2 is updated to 11, etc...


Please let me know if the description of the situation is clearer now.

[HansV]

That is probably because the Access table already contains records with ID_LEAD 1 through 9. A primary key cannot contain duplicates, so the values 1, 2 and 3 from the Excel sheet have to be modified.
A possible solution would be to make ID_LEAD a number field and to create another field as autonumber primary key. The ID_LEAD values should then be imported without change, and you can use an update query to insert the autonumber values into the child table.

[Wyoming]

Yes, making ID_LEAD a number field would be a solution but unfortunately this is something that can't be done at this stage.
I think I will try to create a query that somehow extracts those records from the parent table that have been imported and get the new ID_LEAD from there in order to update the child table. I'll see if that works :S

Thanks for your help!