Autonumber Changed to Random

[F1stman]

Hello All,

The database I designed for a client has encountered a problem. The employee
charged with 'maintaining' the database decides to make the database into a
briefcase file (replica) so he could take a version home. Only problem is all
of the autonumber fields which workers use to refer to everything from
projects to invoices are now set to random. The numbers are used in manual
records and tracking as well. They went from a 4 digit number to a 10 digit
random number. Very annoying.

THE OBJECTIVE: I have used every arrow in my quiver. Is there ANY way to
change an autonumber field to incremental after it is changed to random. I
need to maintain the existing numbers as well. That rules out copying from
what I have seen.

Please help. I will be greatly appreciative.

 

[Arvin Meyer [MVP]]

Yes, but it is no easy process. First make a copy and work on the copy only.
Basically, you need to find every set of Primary and matching Foreign keys.
Once you are sure of that, change every autonumber to a plain long integer.
Create a new autonumber field for every primary key, and a corresponding
empty long integer field for every foreign key. Now link your tables on the
old random autonumber and do an update query to take the new sequential
autonumber and create its corresponding value in the subtable.

Once you're sure everything is right, delete the old random fields and
rename the new fields. Now don't forget to bill the employee or the client
for your trouble. And make sure that someone who actually knows what they're
doing maintains the database.

 

[David W. Fenton]

Yes, but it is no easy process. First make a copy and work on the
copy only. Basically, you need to find every set of Primary and
matching Foreign keys. Once you are sure of that, change every
autonumber to a plain long integer. Create a new autonumber field
for every primary key, and a corresponding empty long integer
field for every foreign key. Now link your tables on the old
random autonumber and do an update query to take the new
sequential autonumber and create its corresponding value in the
subtable.

Once you're sure everything is right, delete the old random fields
and rename the new fields. Now don't forget to bill the employee
or the client for your trouble. And make sure that someone who
actually knows what they're doing maintains the database.

None of this will accomplish anything as long as the database is
replicated, since you can't have a non-random Autonumber in a
replicated MDB.

 

[Arvin Meyer [MVP]]

None of this will accomplish anything as long as the database is
replicated, since you can't have a non-random Autonumber in a
replicated MDB.

Obviously, the replica has to be broken first.

 

[John W. Vinson]

Hello All,

The database I designed for a client has encountered a problem. The employee
charged with 'maintaining' the database decides to make the database into a
briefcase file (replica) so he could take a version home. Only problem is all
of the autonumber fields which workers use to refer to everything from
projects to invoices are now set to random. The numbers are used in manual
records and tracking as well. They went from a 4 digit number to a 10 digit
random number. Very annoying.

THE OBJECTIVE: I have used every arrow in my quiver. Is there ANY way to
change an autonumber field to incremental after it is changed to random. I
need to maintain the existing numbers as well. That rules out copying from
what I have seen.

Please help. I will be greatly appreciative.

Well, you've now discovered the reason that most serious developers recommend
a) keeping good current backups and b) never exposing Autonumber values to
user view. This is actually reason number three on my list of reasons to avoid
exposing autonumbers (one being user-entry gaps caused by cancelling a record,
two being the large gaps which Append queries can create).

DO you have a backup? Might it be possible to restore it (and have this
unfortunate user enter all the data since it was created)?

If not you will have to "unreplicate" the database - one way is to create a
complete new database, define all the tables, link to the replicated database,
and run Append queries to populate all the fields. Considerable care will be
needed in filling tables with autonumber keys, to ensure that relationships
are maintained properly. Good luck!

John W. Vinson [MVP]