I wanted to let everyone about something that I discovered recently.

I have a database that I set up with an import routine to bring in data from EXCEL. Once I get all of the data scrubbed, I use update queries to assign IDs. I run some other checks as well. Basically, this DB is just a place where I can scrub data and then export it to an archive database which serves as the back-end for a dashboard.

After I archive the records, I use delete queries to clear the tables in the scrub database and set the stage for the next import. What I recently discovered is the following:

I can import records into ACCESS from my ImportTemplateT EXCEL file as many times as I want and the autonumber ID field will continue to increment properly.

HOWEVER, as soon as I delete the records in EXCEL and paste in new data and run the import routine in Access, the auto-number ID field in the table that I am importing to resets.

For example, let's say that I imported data for June which had 40,000 records. My June autonumber IDs ranged from 1-40,000. After scrubbing the records, I archived them, cleared the tables and then imported July's records. Let's say July also had 40,000 records. Access did not start the ID autonumber at 40,0001. It reset to 1.

Thus, the auto-number IDs for the July records were the same as the ones for the June records - in this example, 1-40,000.

A solution that I came up with was to create a second ID field in the import table in Access and use an update query to concatenate the auto-number ID with the current date (Date()) to create a new, unique ID. Here's the function: Clng(ID+Date()).

You can also use a static date (I'm actually using the last day of the month that the invoice was received in.) rather than the date function.

---

Reply from Alex Hedley:

Compact and Repair resets AutoNumbers.
Why not have the AutoNumber created on the Import into the Archive db as that's the last place it goes into.
Or create a Composite Key.