Autonumber Relationship Help

[bacarat]

Hi Everyone,
I have a database with several relationships based on an autonumber, My problem is that the data was being entered on several different copys of the same database and now I need to bring all of that information into one database. All of my fields re linked with an autonumber#. I manager to get all of this information correct by manualy matching the fields and numbers. So it all has the correct information. My only problem now is that in order for me to do that I had to change the primary keys from autonumbers to normla numbers so that I could change the values. Is there anyway to set it back to an auto number? Or is there a code I can use for it to manualy add in a new nuumber from now on each time I enter a new record? I guess what im asking is can I change a populated number field back to an autonumber somehow? If not Is the Dmax function my only option for getting unique values to populate in these tables? Thanks.

 

[The_Doc_Man]

Generally, what you do is this (stated briefly)

You have a table with a number field (not auto-num). Make a new, empty table that looks just like the old one but with an autonum field.

Now write an append query that appends the old table to the new one. The autonumbers will populate, but the link to that other number field will still be OK

Now write an update query that updates the link in the child table from the original number field to the new number field.

Now go back and reset the relationship to the new autonumber field.

now go back and remove the old number field from the new table. And you can now delete the old table, too.

Every one of the steps can be a simple query. Just be methodical and make backups before you do anything that deletes something permantly so if you didn't do it right, you can step backwards.

 

[Premy]

How about creating a new table (with an Autonumber field) and append your old data to it with an append query?

http://allenbrowne.com/ser-26.html also might be worth taking a look at.

I'd suggest you first make a backup of your original table... just in case.

HTH
Premy

 

[Premy]

Well, it seems like Doc Man already gave it to u in much more detail while I was writing. Now you're bound to get it right ;-).

regards,
Premy

 

[Pat Hartman]

Unfortunately, you can't change a numeric field to an autonumber so you need to create new tables with autonumbers defined as the PKs.

If you hadn't already manually modified the value of the PKs and FKs, I would use DocMan's method. If you don't have to mess with the keys, just use append queries. The append query will maintain your existing numeric values as long as there are no duplicates.

Make sure you have name autocorrect turned off during thisprocess or you may find yourself left with broken references because Access will propagate name changes and you don't want that to happen during this process.

 

[bacarat]

Thank You Everyone for your input. I will give this a try today and let you know how it works.Thanks!

 

[bacarat]

Ok, I made a copy of my old table "tblNames" and named it "tblNames1". I then made an append query appending the "tblNames" to "tblNames1". This worked and updated my new table with values from my old one. Looks like its working so far, my only question is about the update query after this? Im not quite sure I understand how to do this? Any help on making this update query would be great. Thanks!

 

[The_Doc_Man]

The update query finds the records in the child table one at a time. They are unequivocal because they are still linked through the OLD linkage.

So you make the query design grid join with the child records via the old linkage. Then in the icon bar, change the query to an Update query. Now it will give you a CHANGETO line in the grid. And there, you can put the name of the new linking field to be stored as the link, overwriting the old linking field. Or something like that.

 

[bacarat]

Thanks Alot!

 

[Pat Hartman]

But, if the PK's for the parent tables didn't change, you can use straight append queries for the child tables since their FKs won't need to change.