Question corrupted Autonumber

[ck.letterbox]

Hi Guys,

I'm so desperate here!
I've read through many threads about this corrupted Autonumber but after tried many solutions, I still cannot fix my case.

The details of my case are:

- Windows XP SP3, MS Access2003 with Msjet40.dll version: 4.0.9511.0
- There're ~8 users.
- use one backend mdb file.
- so far, I think it's not about 'bad' sub-form that causes this problem.

Last year, I upgraded the backend file from Access97 to Access2003.
And from that point, Autonumber started to corrupt. I got 2 tables that have Autonumber fields. One contains about 260,000 records and another one contains about 150,000.

I also tried to create a new mdb file and import everything from the old file but it doesn't fix this problem. Compact/repair doesn't help either.

I found some awsome codes:
- Allen Browne
- Pat Hartman in another forum

Both of which are fantastic codes. But seem like they work on the local tables only. And after a day or two it get corrupted again.
Are there any way I can run these code on link-table?

I plan to automate this code from front end.


any ideas are much appreciated.

 

[ck.letterbox]

Plus,
This problem occure with my client system.
I can ask someone to reset the seed (which will corruepted every 1-2 days).
So, I think it's nice to put it in the front end code.

CK

 

[namliam]

What constitutes a corrupt autonumber in your mind?

 

[Insane_ai]

Make sure no one and No programs are accessing your table. This must be done in the databse where the table exists, it does not work on a link.
Open a new query in design view. Change to SQL view.
Use the following comand
ALTER TABLE [Table Name] ALTER COLUMN [Field Name] COUNTER((Highest # +1),1);

Example: ALTER TABLE [outbounddetails] ALTER COLUMN [DetailID] COUNTER(5081441,1);


I have this issue on occassion. I now code my databses to use DMax() + 1 for fields that need to be incremented. The Autonumber feature is nifty but I find it unreliable on databases theat are heavily used.

 

[HiTechCoach]

As you sure that all the workstations have JET SP8 or later installed?

Is the front end shared?

 

[ck.letterbox]

thanks guys!

namliam, I don't know exactly what cause this problem since I can't replicate the error. The application runs normally vary from 1 day to 1 week before Autonumber corrupts.

The version is as above which is version: 4.0.9511.0
I don't know if this's SP8 or not but it seem to be the latest version, isn't it.
And all users work on their own PC with a copy of front end mdb file (1 user with 1 PC not share).

Insane_ai, your suggestion looks cool! I will try it!

guys, thanks again!

 

[gemma-the-husky]

what do you mean by corrupted autonumber

do you mean

a) it repeats previously used numbers or
b) it just leaves gaps

 

[ck.letterbox]

sure buddy it repeats used number!

 

[ck.letterbox]

Insane_ai, Do I need to change my field from Autonumber to number before I run your query?

 

[gemma-the-husky]

repeating an autonumber is a known error, reported on the MS knowledge base website, in some circumstances. Search knowledge base (www.microsoft.com/support) about this - it occurs in some cases after a compact/repair

You have to fix it, by doing an append query with any acceptable autonumber to re-seed the autonumber. (then delete the appended item)

 

[Rabbie]

There appear to some misconceptions about Autonumber fields.

They should only be used as a meaningless primary key for a table and not where they have a meaning. They are not guaranteed to go up in strict sequence. Gaps can occur but if used as above then this shouldn't matter

 

[Insane_ai]

Insane_ai, Do I need to change my field from Autonumber to number before I run your query?

You do not need to change the data type for this query to work. You do need to make sure you have exclusive access for it to execute.