Problems with autonumber resetting

[Lee]

Hi everyone

I have a table which has an autonumber field and a date
field. When a certain action is undertaken on a form,
the value in each of these fields is appended to
an 'archive' table and then deleted from the current
table.
I've noticed, however, that if the database is compacted
afterwards, the autonumber value is reset to 0 which I do
NOT want!!! Is there a work round that you know of that
will preserve the autonumber field when compacting?

Any ideas would be very welcome.

Lee

 

[Scott McDaniel]

No. Your Autonumber field should really have no "value" to the database. I
belive in A2000 that you can set the seed for your autonumber field, but I'd
advise you to rethink your strategy regarding this.

 

[Lee]

Hi Scott,
Thanks very much for the reply.
I use Access 97 so will have to rethink my database but I
have to disagree with you about the autonumber field
should not have any 'value' to the databse. Its value is
its uniqueness which means you don't have records being
overwritten!!

Best wishes

Lee

 

[Allen Browne]

As you found, Access resets the autonumber seed on compact.

Alternatives:

1. Leave the highest-value record in the database until after the compact.

2. After the compact, execute an Update query to insert a record that has
the value of the previous highest number, and delete it again. Details:
http://allenbrowne.com/ser-26.html

3. Update to Access 2000 or later, and set the Seed of the autonumber.
Example of the code to do this:
http://allenbrowne.com/ser-40.html

 

[Jeff Boyce]

Lee

If you have to preserve a key value, and you are using Access '97 the way
you describe, you'll need to create your own "custom autonumber" procedure.
This is a misnomer, because you won't actually be using Access' Autonumber
type, just generating an ID # (usually done by taking the Max(ExistingID#) +
1).

A different question, though, is why? As in "why are you 'moving' records?'
You may be able to save yourself all this headache, and have a design that
can continue to use Access Autonumber (in any version), if you add a new
field to your table -- a Yes/No "Archived?" field.

Then, when the action in your form takes place, the [Archived?] field is set
to "Yes". By updating your queries that use the table, you can either
explicitly exclude (or select) on the [Archived?] field, plus you don't have
to re-combine across tables to handle historical info.

Also, as a potentially stronger design standpoint, you don't have
"duplicate" tables.

Good luck

Jeff Boyce
<Access MVP>

 

[Lee]

mmmm, yes I take your point about moving records instead
of just using a flag - the simple solution to problems
seems to pass me by sometimes!! I'll go back and
redesign it I think!

Many thanks for the advice.

Kind regards,

Lee

 

[Scott McDaniel]

I think you misunderstand me. An Autonumber field should have no value to
the DATA and no meaning to the user. An autonumber is arbitrarily assigned
by the database and has no relevance to the record with which it is
associated.

Consider the example of a Contact database. What makes the contact named Bob
Smith different from Bob Smith? Assigning an Autonumber insures that the
RECORDS are different (and unique), but it does NOT ensure that the DATA
associated with that Autonumber is different and unique. You could very
easily duplicate records if ALL you depend on is the Autonumber to guarantee
uniqueness. Instead, you should build a unique index on the table that
requires one or more fields to be unique ... for example, perhaps the
combination of FirstName, LastName, Street, and Zip would be required to be
unique before allowing a record to be added.

Think this doesn't happen? I just rebuit a database and imported ~2000
customer records, with ~4000 associated contacts. There were a grand total
of 66 duplicated companies, and over 200 duplicated contacts. There was NO
WAY for this company to accurately track which company owed what, or provide
a complete list of all contacts associated with a company ... simply because
they had depended entirely on an Autonumber field to guarantee uniqueness.
One company was listed 4 separate times ... identical data with the ONLY
difference being the Autonumber primary key. Each had been given an Account
number, each had invoices and payments associated with them.

 

[Lee]

I see what you mean...yes I did misunderstand what you
were getting at.

I'm off to brush up on my thinking skills!!!

Thanks for your patience.

Lee

 

[TC]

But that was not a fault of using autonumbers. They just omitted the
relevant unique index, no?

TC

 

[TC]

Ah, I get your point.

TC
(off for the day)

Yes, there was no unique index on the tables. As I said, they depended
ENTIRELY on the Autonumber field to guarantee uniqueness ...

--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP
was

 

[Scott McDaniel]

Yes, there was no unique index on the tables. As I said, they depended
ENTIRELY on the Autonumber field to guarantee uniqueness ...