MVPs: Autonumber bug; how meek can we be?

[kiln]

This is the third post I've made on this topic over the last several
months. Access 2000 has a catastrophic autonumber bug. It's well
documented, and completely unfixed by Microsoft. I can hardly believe
how little discussion this has received in the newsgroups. There has
been no response from Microsoft at all.

I am now asking for the Access MVPs to address this issue. I know that
your MVP status is honorary and that you don't have any official voice
at Microsoft. But why aren't any of you kicking up a fuss over this?
This is the worst bug I've ever seen in Access. Duplicate primary keys -
how low can you go?

Maybe none of you take Access/Jet seriously anymore? That is the only
conclusion I can draw. Microsoft obviously doesn't either, and has no
reason to, since apparently both MVPs and bulk of Access developers are
willing to put up with the most ruinous bugs in silence.

 

[Jeff Rush]

This is the third post I've made on this topic over the last several
months. Access 2000 has a catastrophic autonumber bug. It's well
documented, and completely unfixed by Microsoft. I can hardly believe
how little discussion this has received in the newsgroups. There has
been no response from Microsoft at all.

I'm new here so please forgive my ignorance. I have not heard of this issue
before, do you have a link or article I can referr to in order to read up on
this issue??

Thanks,

Jeff

 

[Douglas J. Steele]

What Service Pack are you running? According to
http://support.microsoft.com/?id=257408 the problem was fixed in SP4.

 

[kiln]

Hi Douglass

I started a thread on Oct 4 called "Devastating Autonumber bug in Access
2000" that details my setup. I'm at Win2k sp4 and Jet sp7. It's not
fixed and that is part of what is so spooky about the level of
attention, or the grade of programmers that MS is throwing at problems
with Access. First there was no problem with Autonumbers. Then they get
the bright idea to have a seed value (I guess). Then they blow the
implementation. Then they issue a patch that they claim fixes it, but
doesn't. That's sloppy in the extreme.

 

[kiln]

Hi Jeff

Take a look at two recent threads

"Devastating Autonumber bug in Access 2000" on Oct 4
(microsoft.public.access.formscoding,microsoft.public.access.modulesdaov
ba,microsoft.public.access.tablesdbdesign)

and

"AutoNumber uses less than greatest value!" on Sept 9
(microsoft.public.access.tablesdbdesign only I think)

 

[Allen Browne]

Kiln, we can understand your frustration with a problem that was introduced
in Access 2000 as part of the change to Jet 4.

However, there are some aspects to bear in mind:

1. If the autonumber is the primary key, if does not function correctly, but
you do not get the duplicates. The result is a frustration in data entry
rather than a compromise of existing data.

2. Personally, I have not seen the issue resurface since the Jet SP7 patch.
That's why I'm not "kicking up a fuss".

3. If you can generate a reproducable scenario under Jet SP7, please take it
up with support.microsoft.com. A small file and the exact steps to reproduce
the issue would be most helpful for them.

 

[Alex Gigante]

I just posted the message below. Is this the bug?

In a large database in which we are using AutoNumber to
automatically set the ID for each entry, we have
discovered that it skipped a number about midway through.
As a consequence, the form that we are using to input the
data no longer corresponds with the table, i.e., the form
entry no. is always one behind the no. automatically
assigned to the entry in the table. Because these nos.
are keyed to the documents that we are identifying with
the entries, we now have serious confusion.

Has anyone experienced this glitch before? If so, is
there a fix?

 

[Allen Browne]

No, Alex, this is a different issue.

AutoNumbers are supposed to provide a unique value for the record, and that
value should not change once its been assigned. That means that if you
delete the record where the AutoNumber was 53, then 53 will always be
missing from then on. Access does NOT change 54 to 53, and 55 to 54, and 56
to ... It is just as well it doesn't: if it did, and you had a related table
that said client 54 owed you $1000, if the AutoNumbers did renumber, all the
related information would be wrong. (Believe me, people don't always set up
their relationships, let alone relying on cascading updates.)

You may notice that Access assigns a number when you *begin* to enter a new
record. This way, if a second user starts another new record at the same
time, they get the following number, and so there is no clash. If you abort
the entry without saving it, Access does NOT reuse that number. In a
multi-user environment, it can't afford to make assumptions about whether
the number was actually saved or whether other users' numbers have been
assigned. It just makes sure it assigns a UNIQUE number.

It follows that after deletions and aborted inserts, the last AutoNumber
assigned to the records is NOT the same as the number of records in the
form. This is normal behaviour, quite different from the bug where Access
was assigning a duplicate AutoNumber.

 

[kiln]

Hi Allen

I have created a set of data files that repro this behavior. I've
submitted a report to MS but who knows how long it will take them to
respond, if ever. The submission form has no way to submit the test
files.

As far as your first point goes, the autonumber is a PK and does indeed
attempt to create a duplicate value. The only way I'm able to get my
users back to work is by applying your patch each time we do a beta test
iteration.

(http://allenbrowne.com/ser-40.html)

Sure if making everyone feel "great" about Access.