I'm in a real crunch here. We have a table with existing data, over 100K records. It has an integer primary key that was Autonumbered. Please don't ask, we had to do some merging and re-importing. Long story short, Access won't let me change that PK field back to Autonumber. And because there are other tables having foreign keys to that field, I can't just re-create the field. I need some way of at least getting Autonumber to work with the existing field, or do a manual auto-number. Records are added through bound forms, if that helps. I tried putting code in the BeforeInsert to auto-increment the highest ID field manually, but then when we go back into the data entry form, we get an error that says, "Access Database Engine cannot find a record in with key matching field(s) . This happens whenever the form refreshes. There has to be a graceful way around this, but we've been at this for hours and I'm desperate for any ideas for a workaround.
Thanks!
Create a new table that is a copy of your current table. In this new table, set your PK field to AutoNumber. Next, create an append query that appends all rows in your current table to the new table. Finally, rename your current table to _backup. Rename the new table you created to the original name of your current table, .