Compact wont work

S

slide-lib

I've created a database (Access 2000) that has a field that is an autonumber,
since I need to have an incremental number for each record.
I have accidentally erased a record and attempted to compact it to recup
that number, but has no effect.
The databased is on a network, but I have also tried copying the database to
the the C: drive and it still doesn't work. I've checked all the possibilties
from the help menu for troubleshooting compact and repair, and can't figure
out why it will not work.
I can create a new table and use an append query to reset it, but that is a
bit of a complicated root, particularly because other staff will be inputting
records and they are bound to accidentally erase records and I need a very
easy do-it-yourself solution...such as "Compact and Repair".
 
D

Douglas J Steele

If the value of the autonumber is important to you, you probably shouldn't
be using Autonumbers, but "roll your own" instead. (Put logic in the form's
BeforeUpdate event to calculate what the next number should be)

Autonumbers exist for one purpose: to provide a (practically guarantee)
unique value that can be used as a Primary Key. They are not guaranteed not
to have gaps in them. If you were to use Replication, the Autonumbers
wouldn't even be sequential: they'd be random. Most developers don't even
show the value of the Autonumber to their users.
 
S

slide-lib

What do you mean by autonumbers "are not guaranteed not to have gaps in
them."? When the AutoNumber field has its NewValues property set to Increment
my understanding is that there should be no gaps.
I tried to avoid using autonumber, by assigning a "default value" that would
increment by one each new record. I figured out the right way to express
that, but unfortunately, Access 2000 doesn't seem to want to accept a formula
for "default values". Setting it to automatically produce the number (either
with autonumber or with a formula) is important as the numbers are quite big
and is easy to have typos. I guess I want to have my cake and eat it too.
You suggest "Put logic in the form's BeforeUpdate event to calculate what
the next number should be". Any way you could explain how to do that? I would
really appreciate it.
 
D

Douglas J. Steele

If, for example, you start to save a record and then change your mind, the
Autonumber value that would have been used is lost to you.

You can't use the DefaultValue property.

How you determine the next number to use depends on your setup. If you've
only got one user, you can use DMax() to determine the largest value used so
far, and add one to it. In a multi-user situation, one approach is to use a
transaction to open a recordset that retrieves the largest value used so far
(SELECT Max(ID) FROM MyTable) and saves your new record with the new ID.
Another approach is to have a table that stores the next value to be used.
 
T

Teri Harry

gggg
slide-lib said:
What do you mean by autonumbers "are not guaranteed not to have gaps in
them."? When the AutoNumber field has its NewValues property set to
Increment
my understanding is that there should be no gaps.
I tried to avoid using autonumber, by assigning a "default value" that
would
increment by one each new record. I figured out the right way to express
that, but unfortunately, Access 2000 doesn't seem to want to accept a
formula
for "default values". Setting it to automatically produce the number
(either
with autonumber or with a formula) is important as the numbers are quite
big
and is easy to have typos. I guess I want to have my cake and eat it too.
You suggest "Put logic in the form's BeforeUpdate event to calculate what
the next number should be". Any way you could explain how to do that? I
would
really appreciate it.
Click to expand...
 
Top