Thank you very much for your help. I did talk to my customer about archiving and performance, but he absolutely wanted me to build the fonction to archive "finished events". I will bring him the arguments you just gave me and see what he says about it.

About the "insert into", it seems to be working fine this morning, for some reason. I updated my tables with the production tables and when I copy a record, Access assigns it the next highest unused number, as needed.

Thanks again for your help, It is greatly appreciated.

Chantal

Ah yes. Customers! Can't live with them, can't earn a living without them.

One of the things we often talk about at PNWADG is educating customers. So many of them come from the point of view of an Excel "database". They simply don't understand what happens when you move away from that tool to something as powerful as an actual relational database.

Good luck convincing your customer. As a suggestion, you might put forth some extra effort to understand why the customer thinks they need the archiving. Let them know that you truly understand their wants/needs/issues/concerns. After all, they might have very valid reasons for archiving. If their arguments are based on old school database principles, help them understand the "reality" of what Access can do without archiving.

Point out that archiving MOVES things. And once something is archived, if it wasn't meant to be archived, it has to be MOVED back to be unarchived. You can provide views of the data so things don't need to be archived. Or you can spend your time writing and testing all of the archiving functionality.

Wrap it up with "archiving is unnecessary and ultimately leads to extra steps that users have to learn and remember to do, not to mention higher cost for your development time and likely more time to support the archiving functionality, especially when users forget to archive or archive something they didn't mean to archive." (That cost thing usually gets them.)

One thing you can do to satisfy your customer, use AutoNumber, and not have gaps is to never delete a record, but rather give it a field that gets set to a value indicating that the record has a deleted status while still keeping it in the records that exist. This way the record itself fills in the gap, preventing Access from trying to “re-use” the ID number.

Additionally, you could carry two IDs, one generated through Access' Autonumbering, the other manipulated by the program/users/whatever. The Autonumber could be used to relate the records (letting Access do what it does), while carrying the manipulated number as a means of archiving, and so on. (Kind of a spaghetti approach.)