Dear Richard,
The database I am working with has been evolving over the last 15 years.  I am currently using Access 2007, which as you know doesn’t support replication (thank God).  When I was using Access 2003 I made the grave error of replicating my database.  My main table is related to more than 25 other tables.  All of these tables have wild incremented numbers up to 8 digits on both sides of the decimal in all of the auto number fields.  Is there any way to renumber the auto number fields so that they are sequential with ordinary numbers?  Your opinion in the matter is greatly valued.
Gary Morrison

---

Reply from Richard Rost:

Yes, I used to hate database replication too. It NEVER worked properly for anything but the most basic databases. To answer your question, however, I can firmly say, "I don't know." I've never tried it before. If you have lots of tables and relationships, it might be a SERIOUS PAIN to remove all of those crazy Autonumbers and create new relationships. It's probably going to be more work than its worth. I don't believe there is an option in Access 2003 to REMOVE replication from a database though?

The good news, however, is that you really should never have to actually worry about those Autonumbers. Those are internal numbers for Access to worry about. If you need to give someone a CustomerID, or an OrderID, for example, you can just make your own CustomerNumber or OrderNumber field, like I show in this lesson.

As far as removing all of those IDs... if it was just ONE table, I'd say just nuke the ID field and create a new one. They'll all get new numbers... but if that's a field that's related to another table, you'll lose all of your relationships. Not good.