Advice please: add record, multi-user, autonumber

  • Thread starter Thread starter Christine
  • Start date Start date
C

Christine

I'm designing an application that will be used by multiple
users via a network. I understand that Access adds records
regarless of whether you actually "save" the record or
not, and that adding a cancel command doesn't do anything
either. Thus, an AutoNumber field increments when you may
not want it to.

So, my question is which method to add records is better:

Choice 1: Open the form. Store the fields to memory. When
the user clicks "Add record", put the contents of the
memory variables into the fields. This will then increment
the AutoNumber field. Pressing "Cancel" would release
everything from memory.

Choice 2: Create a temporary table to store the fields and
data. When the user clicks "Add record", use an append
query to add the record to the table. Then delete the
temporary table. Pressing "Cancel" would also delete the
temp table.

I'm not quite at the point of adding locks to handle
events in a multiuser environment, but (as a very
inexperienced user confused about locks, back and front
ends, etc.), which of the above choices would be the
easiest to learn how to code? For each choice, what would
have to be considered?

Many thanks,
Christine
 
Hi Christine

How about Choice 3: Just treat the autonumber as a unique identifier. It is
not going to be a sequential number anyway, e.g. if you delete Client 5,
then Client 6 does not change number to become client 5. The numbers that
are skipped due to aborted entries are just irrelevant.

If it is really important not to have skipped numbers for one particular
table (because of some auditing process, for example), don't use the
AutoNumber. Instead, create another table to hold the highest number
assigned so far. Then in the BeforeUpdate event procedure of the form where
new records are entered:
a) Open a recordset into this number table, locked exclusively.
b) Increment the number, and hold the lock on the table.
c) Assign the new number to the record in your form, and save the form.
d) Once the new record has saved, release the lock on the number table.
e) Incorporate error handling that involves random delays and a fixed number
of retries to handle the case where multiple users try to append records at
the same time.

In most cases though, you can just use the AutoNumber and accept whatever
unique number it gives you. You can use optimistic locking, i.e.:
Tools | Options | Advanced | Default record locking | No locks.
Other than training your users how to handle the conflict dialog, there is
nothing else you need to do.
 
Dear Alan,

Many thanks for your advice. Your instructions for steps a-
e were great, but I haven't been able to figure out how to
write the code to do all that.

I should imagine it is not possible for you to provide
that level of detail/instruction, so I'll have to settle
for having lots of missing autonumbers, and use your notes
for optimistic locking. I really don't want that, but
being so new at this it would tie up the project for too
long whilst I try to figure it out. If you can indeed
provide details, I'd be eternally grateful. If you can't,
you still have my many thanks.

Christine
 
No, just go with the missing numbers and Keep It Simple.

That's what we all do whenever we can. :-)

All the best as you sink your teeth into Access.
 
Back
Top