Autonumber - increment vs random

[Paintballlovr]

Ive run into an autonumber issue and I'm looking for some input as to if switching from increment to random may resolve the issue. I have an archiving process within my database that moves records out of my main table and with it goes the autonumber. When I try to import the next batch of records, they don't all come in and I think its because im generating a key violation on the ID number. I started down a path of retro fitting the database to eliminate the autonumber and it dawned on me that maybe a random autonumber would be a much quicker and easier fix. I don't care what the autonumber is, as long as each one is unique and the autonumber doesn't prevent new records from appending, due to key violations. Does it sound like a random autonumber would be a solution or do I need to proceed with the overhaul?

[JoeM]

Note, if you are moving/archiving an Autonumber field, in the table that you are moving it to, you do NOT want this to be set to an Autonumber field, as you cannot import into an Autonumber field. You just want it to be set to a numeric date type, one that is large enough to accept all your Autonumber values from the other table.

I think that continuing to use the Auto Increment option on that field in your non-Archive data table should be fine.

[June7]

Why move records to 'archive' table? Just have a Yes/No field to set this status and exclude the 'archived' records from queries.

Might find this discussion of interest https://www.accessforums.net/access/...ber-44906.html

[Paintballlovr]

Note, if you are moving/archiving an Autonumber field, in the table that you are moving it to, you do NOT want this to be set to an Autonumber field, as you cannot import into an Autonumber field. You just want it to be set to a numeric date type, one that is large enough to accept all your Autonumber values from the other table.

I think that continuing to use the Auto Increment option on that field in your non-Archive data table should be fine.

I do have the ID field set to a number type, so I'm ok there. The auto increment isn't working because of this situation:

Lets say I have 5 records in my master table, 1 thru 5. I archive record 2. When I run my next import that has two total records to bring in, the first record drops into 2 and since 3 is already assigned, my second record fails to append due to a key violation. Since I don't care if the IDs are in sequential order, I'm hoping that switching to random will resolve the issue.

[Paintballlovr]

Why move records to 'archive' table? Just have a Yes/No field to set this status and exclude the 'archived' records from queries.

Might find this discussion of interest https://www.accessforums.net/access/...ber-44906.html

I'm moving them out of the table so they don't affect my evaluation of duplicates, but that didn't even occur to me to filter them that way. I already have a checkbox for inactive, which is how we identify which records to move to the archive table, so maybe I can kill two birds with one stone. I'll take a run at it and let you know. Talking theory at this point and assuming this wasn't an option, does switching to random sound like a solution to new records dropping into a spot not large enough to hold them all (see my response to JoeM)

[JoeM]

I do have the ID field set to a number type, so I'm ok there. The auto increment isn't working because of this situation:

Lets say I have 5 records in my master table, 1 thru 5. I archive record 2. When I run my next import that has two total records to bring in, the first record drops into 2 and since 3 is already assigned, my second record fails to append due to a key violation. Since I don't care if the IDs are in sequential order, I'm hoping that switching to random will resolve the issue.

I have never seen that happen. Are you trying to import a number into the Autonumber field, or are you letting Access assign it for you?
From all my experience, I have never seen Access go back and try to fill in the blanks of deleted records. It always continues from the sequence from the last record.
Even if I delete the last records and run a Compact & Repair, it continues where it left off and does not try to use the numbers I just deleted. So try as a I may, I cannot get it to do the behavior you are describing.

Makes me believe that there is something more going on here you might not be telling us about.
How exactly are you archiving these records?
What do you do after you do that?
These issues you are having, are with your Master table, not your Archive table, right?
What is the Indexed property on your Autonumber field set to?

As June was saying, there may not be any reason to Archive these records of at all, unless your table is getting so huge that it is affecting query performance.

[Paintballlovr]

I have never seen that happen. Are you trying to import a number into the Autonumber field, or are you letting Access assign it for you?
From all my experience, I have never seen Access go back and try to fill in the blanks of deleted records. It always continues from the sequence from the last record.
Even if I delete the last records and run a Compact & Repair, it continues where it left off and does not try to use the numbers I just deleted.

Makes me believe that there is something more going on here you might not be telling us about.
How exactly are you archiving these records?
What do you do after you do that?
These issues you are having, are with your Master table, not your Archive table, right?
What is the Indexed property on your Autonumber field set to?

As June was saying, there may not be any reason to Archive these records of at all, unless your table is getting so huge that it is affecting query performance.

I am not importing into the autonumber field. When I was looking at the problem yesterday, here is how it actually looked. the highest autonumber in my master table was something like 847 and at this point, I did not realize there were missing autonumbers because of the archiving. I had a set of 22 records from a previous day and the issue was reported that not all of the records were in the database. I manually ran the queries in the macro that does the import, so I could see what was happening at each step. My final query is the append to the table and when I ran it, it said it had 22 records to append, which was correct. When I clicked ok, I got an error message saying that 2 records were appended and 20 failed due to a key violation. I went and looked in the master table and the two records that did append had autonumbers of 244 and 245 and since the next 20 numbers were already used, I'm assuming that is where the violations happened.

The archive process is this:
1) User clicks on box to inactivate record on a form
2) When the form closes, two queries are ran; an append query to make a copy of the record in the append table and a delete query to remove the record from the master table that was just appended to the archive table

I'm not sure what you mean as far as "what do I do after that"? I can tell you that this process happens in real time and could run multiple times a day. The import happens each morning, bringing in new records from the previous day.

The archiving was put in to resolve issues with inactivated records affecting my duplicate analysis, but you're right, June7 did bring up a different way to address those and I'm going to look at that idea as well.

[JoeM]

When I clicked ok, I got an error message saying that 2 records were appended and 20 failed due to a key violation. I went and looked in the master table and the two records that did append had autonumbers of 244 and 245 and since the next 20 numbers were already used, I'm assuming that is where the violations happened.

That is very odd. It looks somehow the Autonumber seed got reset. I am not even sure how you would do that, without the use of some VBA (unless that field was not always an incremented Autonumber). I have never seen that happen before, and I am unable to reproduce that behavior no matter what I try. Perhaps your database got corrupted?

[Paintballlovr]

That is very odd. It looks somehow the Autonumber seed got reset. I am not even sure how you would do that, without the use of some VBA (unless that field was not always an incremented Autonumber). I have never seen that happen before, and I am unable to reproduce that behavior no matter what I try. Perhaps your database got corrupted?

I was getting ready to start testing the random autonumber and using the inactive checkbox as a filter instead of moving records to an archive table and I found an issue with one of the queries in my import macro. It looks like the issue would be unrelated, as it's just a query that checks for dupes (not using autonumber), but since I found one issue there might be more. Let me go through everything with a fine tooth comb and make sure I'm confident in my import process before anyone spends anymore time looking at this issue. If I work everything out and the problem still exists, I'll pick up the thread at that point or resolve it if I find the issue. Thanks to everyone for your assistance so far and I hope this doesn't end up being a waste of time.

[Paintballlovr]

I swear with Bill Gates as my witness that this issue was happening, but I'll be darned if I could replicate it either. I went back and reran several days worth of imports and archived and reactivated records between a couple of days and everything worked as designed. Since I can't reproduce the issue, I'm going to mark this thread as solved. Thanks again to everyone who helped out!