Autonumber reverts to gaps in numbers rather than taking next sequ

[Guest]

I converted a 2.0 db to 2003. The 2.0 db allowed records to be "closed" and
then "reopened", moved to another table and then back to the original. The
autonumber is the key and link to related tables. This worked fine in 2.0.
Apparently 2003 picks up missing gaps in the autonumber sequence to assign
the next number which does not work well when reopening records. I might
have record #8090 which has been closed and when I want to reopen it, #8090
has been used by Access for a new record, and the reopened record goes to the
next available number which messes up my links. The whole 10 year old system
needs to be rewritten, but time does not allow at this point.

Is there a quick and easy way to fix this problem?

 

[Joseph Meehan]

I converted a 2.0 db to 2003. The 2.0 db allowed records to be
"closed" and then "reopened", moved to another table and then back to
the original. The autonumber is the key and link to related tables.
This worked fine in 2.0. Apparently 2003 picks up missing gaps in the
autonumber sequence to assign the next number which does not work
well when reopening records. I might have record #8090 which has
been closed and when I want to reopen it, #8090 has been used by
Access for a new record, and the reopened record goes to the next
available number which messes up my links. The whole 10 year old
system needs to be rewritten, but time does not allow at this point.

Is there a quick and easy way to fix this problem?

Is there any problem other than someone looking at that number and
getting confused?

I suggest you may not want to use Autonumber for that use. Autonumbers
are designed to provide unique numbers. It in not designed to provide
numbers in order and for a number of reasons may not do so. As a result
using them in any application where the user sees the numbers is likely to
end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.

 

[Pat Hartman\(MVP\)]

Access doesn't reuse autonumbers. Something else is causing the problem.

It has been many years since I used A2.0 so I don't remember any differences
in how it handled autonumbers. I do know that in current versions, the ONLY
way to add a record to a table and provide an autonumber is with an append
query. So, if the converted application does not use append queries to
transfer the records from one table to another, you will need to modify it
to do so.

 

[Al Camp]

I agree with Pat Hartman on that. Autonumbers should never repeat a number it has
generated, so there's something else going on here.

But rather than speculate on that, try this suggestion...
You could save all the records previously autonumbered, but then change the field from
Auto to Long and use a DMax +1 Default Value to generate your numbers from now on. A
work-around like that should do the trick.

 

[Joseph Meehan]

Access doesn't reuse autonumbers. Something else is causing the
problem.
It has been many years since I used A2.0 so I don't remember any
differences in how it handled autonumbers. I do know that in current
versions, the ONLY way to add a record to a table and provide an
autonumber is with an append query. So, if the converted application
does not use append queries to transfer the records from one table to
another, you will need to modify it to do so.

My guess was and is that somewhere in the transfer it is in effect
compacting the database freeing up those numbers. There also could be some
sort of corruption of the file as well.

 

[Pat Hartman\(MVP\)]

Compacting would only free up numbers at the high end of the set. It would
never free up a number lower than the highest existing record.

 

[Guest]

1. The problem is that it provides the link to related data and when the
number is changed the links are messed up.

2. I would say that autonumbers are most definitely being reused, since
when a new record is added after compacting it will go back and pick up a
prior number that is not currently in the table. Compacting is most
definitely freeing up numbers lower than the highest number, however it
doesn't go back to the first unused number. The thought just occurred to me,
so I haven't verified it, but it may go back to the point of conversion. The
only way I have found to fix it on a temporary basis is to "add" and delete
records until I get to the proper number. Much of the time, when the records
are added in this manner I get a message saying that I cannot add a record
because that would create a duplicate record so I escape and then attempt to
add another record, continuing this process until I get to the appropriate
autonumber.

3. I am using append queries to put the data back in the original table. I
think the problem with adding records this way is that if the autonumber has
already been reused then it will pick up the next number in line and that's
where the links are messed up.

My solution is that I'm going to have to rewrite the autonumber to create my
own autonumbering system. I figured that would be the solution, but I hoped
to find an easier way before I tackled the situation.

Thanks for all your help.

 

[Al Camp]

I ran into this same situation. A large table with an autonumber key (related to other
tables) needed to be archived every year. We only wanted two years of "active" records
working at one time.
It was no problem to move records from the main table to an archive table... the
problem came when the client wanted to (rarely) access very old records. I, and the
client, didn't want to develop all the forms/related forms necessary to allow the client
to view "archived" records the way they viewed "working" records. When I tried to bring
the archive data back into the working table, the autonumber would not allow my old
autonumbers to be re-introduced/merged into the working table... and appending the
archives would assigna new autonumber (thereby losing it's relationship to other tables).
My solution was to drop the autonumber field on the working table, and go with what I
suggested...
use DMax +1 Default Value to generate your numbers
It was no problem at all. I just had to change the key field to Long, and alter the
Default Value for that key field on that form where it was generated. All the previous
autonumbers remained the same, I lost none of the related table values, and new key value
just picked up where the last autonumber left off. Now I could append archive records
back to the working table... allow the customer to do their historical review using the
same forms as developed for the working tables... and then re-archive the old data.
Customer brings back 2003, reviews the problem transactions, and then re-archives 2003

 

[Guest]

Thanks all for your assistance.

I probably have about 10 or so tables that I will have to incorporate this
into, but I don't expect it will be a problem. Acutally doing this from the
get go would have taken less time that researching to see if there was an
"easier" way to take care of it.

 

[Pat Hartman\(MVP\)]

An append query is the ONLY place where you can specify a value for an
autonumber. If this were not so, it would never be possible to convert
existing data. If it wasn't working for you then there was some problem
with the query. Perhaps you didn't have the existing primary key field
mapped to the autonumber field in the append.

 

[Pat Hartman\(MVP\)]

Sounds like you have run into the dreaded autonumber bug. I don't have the
suggested remedy handy but for starters, you'll need to make sure that all
patches have been applied to Access and Jet. I have experienced this bug
with A2K and AXP on the same database. In my case the remedy didn't fix the
problem. I ended up having to rebuild the form that was causing the
problem. Search the KB for the specifics. If the remedy doesn't fix your
problem, rebuild the form that you are using to add these records.
Hopefully it isn't complex. Note that I first tried exporting the form to
another database, importing it to another database, compact/repair, even the
/decompile to get rid of extraneous code. Nothing solved the problem until
I rebuilt the form.
You can also look for "seed autonumber" or "reseed autonumber" which should
bring you to code samples to show you how to get your autonumber back if it
goes kerfluey.