Autonumber Jumps Back 800,000 Records... Causes Duplicate

[markphsd]

I've had this problem before, the autonumber jumps back several thousand records, or several hundred thousand records and starts incrementing again.

How do I prevent this, or if i can't prevent this, how would I overcome the problem? Compact & Repairing doesn't solve the problem.

Before, I was able to just copy and paste the table, but if have a client who's db I can't access, how would I fix this problem?

Mark P.

Bleh

 

[jsteph]

To fix the immediate problem, delete the field, then re-add it.

I don't mean to sound flippant, but the way to really solve this is to not use autonumber.

You create a simple table--single record, one field for each table needing a sequenced number that means nothing to the record but gives it uniqueness.

Create a small routine that reads/locks/updates/returns the single record (number) in this table. You can assign in the BeforeInsert event. The function can recieve a dummy parameter so it can be called in append queries and get a number for each record.

It's a little more work, but it's fewer headaches.
--Jim

 

[markphsd]

Well,

1. I could do a copy, but when I'm dealing with several thousand, or hundred thousand records that's not desirable.

2. Not use the autonumber: On everytable? On just a couple of key tables? I have over 100 tables in my application. To add in a routine would be possible, but... not desirable.

So, without using the routine... what could cause this problem, and what solutions are there? (Besides copying and pasting, via code or manually).

Additionally, if this is the only headache of autonumber, then I'd rather continue with autonumber. It does it's job pretty good.

Out of curiousity, how long does the routine take to create a new ID? The autonumber is pretty fast, and speed is essential for me.



Mark P.

Bleh

 

[jsteph]

Mark,
The routine I use is as fast as Autonumber--remember, just because you don't 'see' a table in Access for Autonumber and you don't 'see' a routine--it's still there. Access has an internal table, and it must read from disk, lock, and write to disk. No getting around that, period.

As far as which table to use the routine on--I still use Autonumber when I need a quick & dirty unique number for some lookup table or something. But if that number is, say, a Customer ID or Order ID, then I never do. This due to not only non-specific glitches such as your case, but also the fact that compacting, importing/exporting, copy/pasting/appending/deleting, etc, etc. all can cause the autonumber to change or get resequenced.

Maybe I don't understand your problem fully. Do you just need to re-sequence? If so, why wouldn't deleting the Autonumber field and recreating it work? If that answer to that is that the Autonumber field is being used in a Relationship of any kind and that related records will now be orphaned--then it is even more critical that you not use autonumber. Also, I'm not sure about Access Xp, but I think the newer Access versions might have more control over increment stepping, maximum value, etc, so one of those options might be at play in your problem.

Also, when does this jumping back occur? Is there an event or something that triggers it?

Again--I know it's more work, but I wrote the routine once years ago and I just copy it into every Access project where I need it and it's little overhead.
--JIm

 

[markphsd]

I think that there's an event that triggers it. But I'm not sure what event and why. Like many things in Access, what appear to be glitches are usually just nuances that must be understood, using a routine is one good way to avoid the nuance, but i'm buried in the autonumber for the time being.

I'm still not sure what would cause the problem. It doesn't have to do with the number of records. I read yesterday, on here, of a guy who appended records until he reached the 2gb limit with just 1 table and 2 fields, one of which was the autonumber field.

I actually can point to a specific form that seems to cause the problem, so I'm not sure if the form is corrupt or if it's something in the code.

I guess I'll have to review my work more.

Mark P.

Bleh