table lock / sharing issue

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a network shared db used to generate and store barcode numbers, and
the data associated with each. For the reason below, the main table's
default locking is set to "all records."

- There is a piece of code that generates the barcode number,
incrementally, so the table's default locking has to be set to "all records"
to avoid 2 users accidentally assigning the same bcode # to two different
promotions.

In order to provide functionality needed by our company's point of sale
systems we need the ability to have multiple barcode #'s that have the same
first 8 digits [18 digits total, 8 unique followed by 10 zeros] with the very
last of the 10 zeros being changed to 1, 2, 3, etc. (i.e.
159001230000000001, 159001230000000002, 159001230000000003)

I have a piece of code that does an append query in order to let the user
fill out the form, assign the bcode#, then hit "copy" so the user doesn't
have to fill out the dates, etc all over again, and have the same bcode#. It
then unlocks the bcode# field to allow them to change the last digit.

PROBLEM THIS CREATES:
When the append query is activated it seems the table cannot be set to "lock
all records," if it is the query will not work. If I unlock the table we
have the possibility that two users could assign duplicate bcode#'s to
different promotions.

I'm no expert and self-taught and find setting up user-level security very
difficult. Is there any way to get around this problem without setting up
security? Any suggestions?

Thank you!!!
 
Why not have a 'Prefix' table and a 'Suffix' table:

PREFIX table
ID
Prefix

SUFFIX table:
ID (of prefix)
Suffix

To create a barcode, first look in the Prefix table
To create a new suffix for an existing Prefix look in the Suffix table and
increment the number.
You need only lock the Suffix table record while it is being updated.

Maybe I don't fully understand your problem...

Dorian
 
dl said:
I have a network shared db used to generate and store barcode numbers

When the append query is activated it seems the table cannot be set to "lock
all records," if it is the query will not work. If I unlock the table we
have the possibility that two users could assign duplicate bcode#'s to
different promotions.
Click to expand...

See:

http://support.microsoft.com/default.aspx?scid=kb;en-us;240317

"The following example provides a function for generating custom
counter numbers and handling the concurrency and locking issues that
result from the process. It involves the use of a second table to store
the next available key value. This is used for performance reasons and
also to avoid adversely affecting users who would just need to read
data..."

Jamie.

--
 
Back
Top