Auto Number Problem

[Linda]

I have a table with an autonumber and periodically I migrate lines in that
table to another table which saves the autonumber in a number formatted
field.

I had to reinstate a row with its original autonumber and the table lost
count. It started counting from the reinstated number. Has anyone had this
problem?

For Example. I migrated lines 4000 - 4200 to another table. These lines no
longer existed in original table. When I reinstated line 4100, it allowed
the 4100 in the autonumber field.

That would be fine, but it continued to number the lines from 4100 instead
of 22000, which was the last line in the table.

It was like the table forgot what the last line really was.

Thanks

Linda

 

['69 Camaro]

Hi, Linda.

It was like the table forgot what the last line really was.

I can think of one scenario where this may happen. If the table was empty
and the database compacted, and then a record with AutoNumber 4100 was
inserted, then next number would be 4101, not 22000, the last AutoNumber
prior to emptying the table and compacting the database.

If this is not the case, then it's possible that your workstation (or the
computer where this database's tables are stored, if you're linking to the
tables in the back end) doesn't have the latest service packs for Jet (and
possibly MDAC, too, so you may as well check both). If you have Access
2000, 2002, or 2003 installed on the computer where this table is located,
then Jet 4.0 SP-8 and MDAC 2.8 SP-1 are the latest service packs.

Please see the following Web page for the correct file versions for Jet 4.0:

http://support.microsoft.com/?kbid=829558

Please see the following Web page for links to the downloads and the MDAC
Component Checker:

http://msdn2.microsoft.com/en-us/data/aa937730.aspx

If your workstation (and the computer where the back end resides) already
has the latest service packs, then it's possible the database file is
corrupt.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Larry Daugherty]

The sole purpose of the Autonumber datatype is to generate uniqueness
for surrogate primary keys. It is entirely for the use of JET in this
case. Human use of the autonumber for anything else amounts to abuse.
Humans should never see the content of an autonumber field nor care
what it happens to be. The symptoms you describe are normal behavior.
If you want to avoid the effects you describe, populate the field
without the autonumber field. Let the system do it for you. Note
that there is no guarantee of uninterrupted sequence in autonumber.

HTH

 

['69 Camaro]

Hi, Larry.

Humans should never see the content of an autonumber field nor care
what it happens to be.

When the AutoNumber eventually climbs to 4201, which already exists, Linda
will receive an error message, and won't be able to add new records to the
table. She will definitely care about that number, because it's wrong: it
won't be unique in that table. More importantly, if there are any foreign
tables related to this table using the surrogate key, all of the inserted
records from 4101 to 4200 will probably not match up with the existing
foreign records that already relate to the records that have been moved to
the archive table. That's a huge data integrity issue.

The symptoms you describe are normal behavior.

Repeating AutoNumbers is not normal behavior. (Well, they repeat after
about 4.3 billion inserts, but it's doubtful she's there yet.) In Linda's
example, the next AutoNumber should be 22001. Inserting a record to match a
related record should not reseed the AutoNumber column, unless the table was
emptied first, the database compacted, and then the record was inserted into
the table with a value in the AutoNumber column higher than 1.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

This is a bug in the current/last version of Jet 3.6
I don't know about Access 2007/ACE.

Whenever you paste a value into the autonumber field,
you must always reset the autonumber again.

You can do this either by using code to reset the autonumber
seed property of the field, or you can do a dummy append/
delete to reset the autonumber to a number greater than
the last value used.

You can use code to reset the autonumber to anything.
This is the 'new' feature that broke autonumber. Although
the 'new' feature is now 6 years old and autonumber is still
broken, and every fix just changed the bugs. Now that ACE
has been released, there is no reason to expect this will ever
be fixed in Jet 3.6

(david)

 

[Vladimír Cvajniga]

Now that ACE

has been released, there is no reason to expect this will ever
be fixed in Jet 3.6

Does it mean that Microsoft releases buggy products and doesn't fix bugs in
older products?
That is:
1) New release is buggy and thus not useful. Many bugs survive to next
release.
2) Old release is buggy and thus not useful. Bugs in older products will
never be fixed.

Vlado

 

[Guest]

Yes, Access 95 is an example. It was not very useful, and
eventually they just released Access 98.

Access 2000/Access 2002 was a bit different: Access
2000 was a useful product with bugs, eventually they
just stopped patching A2000 and released A2002.

Access 2007/2003 is different again. Access 2003 is
useful, but has bugs. Access 2007 is useful, but there is
no indication that at present it has fewer bugs than 2003,
just different bugs.

(david)

 

[Vladimír Cvajniga]

In other words: the only thing can do is to dream of perfect Access... since
it's never gonna be released... Why do we pay for then? Do we pay for
dreams? :O

Vlado

 

[Jeff Boyce]

I won't defend their release practice, but I will ask if you have ever
bought anything that was "perfect"?

I use Access because it gets a job done, like my vehicle.

Jeff Boyce
Microsoft Office/Access MVP

 

[Vladimír Cvajniga]

Perfect:
My Honda!
My Mazda!
My TV set!
My DVD set!
My VCR!
My satellite receiver!
..
..
etc...

Not perfect:
Anything by Microsoft! :-/

Vlado

 

['69 Camaro]

Hi, Vlado.

Not perfect:
Anything by Microsoft! :-/

Then you know what the shortcomings are, so you can build the perfect
software applications yourself and take all of Microsoft's market share.
You'll be rich!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Vladimír Cvajniga]

Hi, Gunny,

I don't think I'm gonna beat Microsoft. Microsoft is a big SW produrer. They
should do their job perfect, or near perfect, at least!!! As I stated
before: we will never have Access bug-free. Why they don't take care of what
they produce? They know there are bugs in Access and JET. Why don't they
simply fix those bugs?

Vlado

 

[Vladimír Cvajniga]

One simple question:
If your customer finds a bug, or ,as Microsoft tend to say, "issue", in your
old application, won't you fix the bug? Will you just let it "fly"? I don't
think so.

Vlado

 

[Todos Menos [MSFT]]

I strongly disagree with your database folklore


of course your end users should be able to 'see' the integer keys

anything else is abuse

 

[Todos Menos [MSFT]]

Access Data Projects never had this problem

we have seed and increment

and identity insert statements

seriously you guys are fucking idiots for still using MDB for anything

 

['69 Camaro]

Hi, Vlado.

They should do their job perfect, or near perfect, at least!!!

Unfortunately, the software business doesn't work that way. Look at Windows
95 as a case in point. It's the Windows release that should have been 1.0.
It took Microsoft 10 years to get to the point where they could release an
operating system with that level of sophistication. If they'd waited to
release the final version and skipped the other releases along the way
(i.e., 1.0, 2.0, 3.0, 3.1, and 3.11), then they would have gone out of
business before releasing the Windows 95 version because they needed
continuous revenues to fuel further development of their software. Even
though those earlier versions had limitations and bugs, they were the fuel
that propelled the software development engine towards bigger and better
things.

They know there are bugs in Access and JET. Why don't they simply fix
those bugs?

I don't work at Microsoft on those teams, so I can only speculate on what
their motivations are, which isn't really fair.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

['69 Camaro]

Hi, Vlado.

One simple question:
If your customer finds a bug, or ,as Microsoft tend to say, "issue", in
your old application, won't you fix the bug? Will you just let it "fly"? I
don't think so.

It may be a simple question, but it doesn't have a simple answer. What if
fixing a bug for one customer adversely affects another -- or perhaps ten
other -- customers? What if fixing a bug for a few customers means the
product goes in the opposite direction of where most of the other customers
want it to go, and which they don't want to pay for if it does? What if
fixing a bug would require an extensive (and expensive) rewrite? What if
fixing a bug in the current version would be a waste of time because the
product is getting rid of that feature in the next version, or in a few
versions down the line?

In all of these cases, you'd be making a few happy at the expense of the
many, or you'd be spending money unnecessarily, or you'd be spinning your
wheels. So, there are legitimate business reasons for not fixing certain
bugs in software when one has many customers' needs to satisfy, not just one
customer's needs.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Todos Menos [MSFT]]

more importantly

what happens when the big fat company that is raping customers with
buggy software?

what happens when the lethargic company gets too slow to fix bugs?


what happens when people SUBMIT bugs to Microsoft; and they don't fix
them?

Why is Microsoft busy with Robotics Studio and Lego Mindostorms-- when
they can't release bug free MS Access


screw MS
from a quality standpoint; from a performance standpoint-- all you
guys would be better served by moving to DreamWeaver mySql / php

as it is; MDB is not a rational platform for anything

 

[Todos Menos [MSFT]]

it's not a question of 'making a few happy'

it's about fucking making BUG FREE SOFTWARE


The only thing that can save Microsoft-- at this point-- is for Ralph
Nader to replace Ballmer

 

[Tony Toews [MVP]]

It took Microsoft 10 years to get to the point where they could release an
operating system with that level of sophistication.

And for the hardware to get cheap enough that could run it. We'll ignore the
bloating arguments. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm