Multiple Users and New Records

[newtech2]

I have split a database with the app local and data on network. I had a problem with my form receiving a duplicate value error on the autonumber field when 2 users made a new record. I have since changed the data type of the autonumber field to number and added the following code to my form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
On Error Resume Next
Me![Job ID].DefaultValue = Nz(DMax("[Job ID]", "Job&quot,0) + 1
End If

Now, when user #1 adds a record, it takes the next number ok, but user#2 creates a new record, it takes the same number. When the first user moves on to another record it is fine, but when user #2 attempts to move on, it receives the duplicate error again.

Any suggestions?

 

[thornmastr]

Joe Celko, is a rather well known db consultant and a very vocal very vehement hater of autonumbers. His reasons are quoted below. Take then for whatever worth you wish.


================================================

1) It is not part of the SQL-92 Standard and it is highly proprietary to the
Sybase family. It is not portable -- not quite the same thing as
proprietary, since you can often translate one SQL dialect into another with
a simple replacement (i.e. the % operator becomes the MOD () function). So
your code will not move over to a new database.

2) AutoNumber looks like a datatype, but it is not. Create a table with one
column in it and make it an AutoNumber column. Insert a number into the
table and see what happens. Try to set it to NULL. If you cannot insert,
update and delete all the columns, then this is not a table!

3) AutoNumber looks like a constraint, but it is not. Try to create a table
with two AutoNumber columns and it fails. If you cannot add it to a column,
then it is not a constraint. It is possible to write a set of constraints
that prohibit data from ever being put in the table (their predicate is
always FALSE). It is possible to write a a set of constraints that allow
anything in the table (their predicate is always TRUE). But no constraint
can prohibit the creation of the table itself -- that is a meta-constraint.

4) It is not relational. Consider this statement on a table, Foo, which has
an AutoNumber column. Assume the query returns more than one row. INSERT
INTO Foo (x) SELECT a FROM Bar; You will get a result like this: AutoNumber
X ============ 1 'a' 2 'b' 3 'c' but if the query changed an index or was
put on the physical disk data page differently, you might have gotten:
AutoNumber X ============ 1 'b' 2 'c' 3 'a' Explain why one result is the
logically correct choice for an identifier and all other choices are not,
without any reference to the physical implementation. You cannot. Instead of
treating the query as a set, you are doing 1950's sequential processing
using the underlying sequential file system the Sybase family started with.

5) If you have designed your tables correctly, they will have a meaningful
primary key derived from the nature of the entity they model. The AutoNumber
column should be a redundant key. The reason AutoNumber columns are popular
as keys is that they are easy to declare. This is also the same reason that
people build non-normalized databases and put pennies in fuse boxes -- easy
is not right.

6) It is a bitch to do calculations on AutoNumber column values. Well, it
was hard to do direct math on the sequential position of a record in a
1950's punch card system and that it what the AutoNumber is mimicking.

7) There is no check digit in an AutoNumber columns value, so you have no
way of verifying it if you use it as a key.

8) If you use AutoNumber as a key, the values tend to cluster on physical
data pages because they are sequential. The result is that if the most
recent rows are the most likely to be accessed, there will be locking
contention for control of those physical data pages. What you really wanted
in a key is some spread of the rows over physical storage to avoid having
every user trying to get to the same page at the same time.


Now, having given you some rather good reasons for avoiding autonumbers, I must admit I use them only if I need unique keys that should not be created from information directly available in the record. Unless I need them for a unique key, I never use them.

Believe it or not, MS does not guarantee unique autonumbers unless specified as a unique key, and even there MS simply guarantees a duplicate key will not be written to the table.

If you wish, or you need to use autonumbers, define them as unique keys. That way, at least you will be able to trap for a duplicate condition and reissue the update sequence when it occurs,


Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[MichaelRed]

see faq700-184 It is probably not EXACTLY what you want, but in addition to the citing above, it includes one way go get the overall process to work w/o collision errors or duplicates.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[newtech2]

MichaelRed: Thanks! I am new to programming in Access. Where would I need to place this code and where do I need to tweak it?

Thanks in advance!

 

[MichaelRed]

These (questions) are a bit beyond the scope of my participation in these forums. Generically, you place (or call) the code from any/every place which can generate NEW records. Modifications depend on your 'business rules' for the specific implementation reflected in the faq posting, the rules were ~~~ include the date of (original) entry of the record and a count of the record within the month. Your rules may (almost certainly will) be different.

The code is offered as an example, and is not intended or expected to be just taken 'as is', and I cannot offer pro-bono support specific modifications to suit individual users or companies.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over