Change table relationship column

[jmaty23]

I am kind of new to Access and VBA so please bare with me. When I originally created my access database, I used a text field/column to connect 2 tables together. I would like to change this to reference the Primary Key/ID column instead of the text field.

Is there an easy way to go about doing this? My original thought was to create some vba code to replace the data in the column of the sub table with the autonumber in the primary table but I cannot figure out how to do it.

 

[jmaty23]

Right after I posted this the light bulb turned on in my head. I changed the column in the table to a lookup field and referenced the id field. Then I exported the data to excel and copied the numbers back into the column. I then changed the parent/child relationship to reference the id field in the primary table.

Hopefully this will help someone else.

 

[jmaty23]

I was able to make it work with the method I used. So far there haven't been any issues. Do you think that I should still create a new column and run the update query like you suggested?