Compacting Database to reset autonumber

[Mike]

I have an application Backend in Access97. Unfortunatly
the original administrators and authours used an
autonumber field as actual data in the database. The
other day someone got into the backend and deleted
hundreds of records out of the table with the autonumber
field. I need to reassign the records to the original
autonumber value. I know I can reset the autonumber by
compacting the database. As I stated this the back end,
and there are no forms, report, or VBA code; just tables.
I have never had to perform this function on any of my
databases and just wanted to ensure that I don't create
more problems when I compact. I did a test run on a copy
of the .mdb and everything seemed to turn out fine. I am
still a little apprehensive. Is there anything I should
know or look out for before I perform this action?
Thanks
Mike

 

[Adrian Jansen]

Save a copy of the database in its original form before compacting, then at
least you can revert to that if things go wrong.
And if you have a situation where 'someone' can do arbitrary deletes to your
database, consider a better backup strategy on your data.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[Douglas J. Steele]

Compacting the database is something that should be done regularly. My
question is why are you concerned about the values of the AutoNumber fields?
The sole purpose of AutoNumbers is to provide a (practically guaranteed)
unique value that can be used as a primary key. 1, 2, 134, 135 serves that
purpose just as well as 1, 2, 3, 4. Seldom, if ever, should your users even
be aware of what the AutoNumber value is.

 

[LB]

-----Original Message-----
I have an application Backend in Access97. Unfortunatly
the original administrators and authours used an
autonumber field as actual data in the database. The
other day someone got into the backend and deleted
hundreds of records out of the table with the autonumber
field. I need to reassign the records to the original
autonumber value. I know I can reset the autonumber by
compacting the database. As I stated this the back end,
and there are no forms, report, or VBA code; just tables.
I have never had to perform this function on any of my
databases and just wanted to ensure that I don't create
more problems when I compact. I did a test run on a copy
of the .mdb and everything seemed to turn out fine. I am
still a little apprehensive. Is there anything I should
know or look out for before I perform this action?
Thanks
Mike
.

Hi Mike

You may have already done your 'compact', but the
following may be useful to you....

If the data deleted belongs to a 'flat' table database,
i.e. only one table, then the above should be fine.

But are there other related tables which have
relationships to the original autonumber field? (This
would be related data, but obviously the referential
integrity would not have been set(?)) If so, you would
need to be careful - as with an autonumbered field, the
numbers are not necessarily consecutive, e.g. when a user
starts entering data in a new record (e.g. autonumberid =
3), then cancels data entry, then later creates a new
record (e.g. autonumberid = 4). The '3' would be lost,
so the sequence would go 1,2,4, etc. If this was the
case, and you recreated your autonumber, all the records
would be consecutive, i.e. 1,2,3,4. This would mean
there would be no related data displayed for the new '3',
and the new '4' would display incorrect related data -
i.e. that for the old '4' - etc.

I will not waffle on any further with a solution, as it
may not be relevent... but if the above is true to your
situation, I am happy to provide a few ideas.
Essentially you would have to use a back up of the data
to attempt to apply the correct 'autoid' - this involves
a few trickies!

Regards
LB