AutoNumber Random Number not Random

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I have a table structure that uses "Autonumber" for the
data type (primary key) with the field size and new value
settings as "Long Integer" and "Random." The table is
populated by multiple users while not connected to a LAN,
then periodically synchronized. However, during a test
of the application we found two of eight records with the
same primary key number. After a little trial and error
I found the following number sequence produced by the
random number generator for six sequentially entered
records:
1080228088
1080247740
1080258193
1080261960
1080262122
1080293097
The pattern does not look random.
Any thoughts on why Access is not producing random
numbers for this field? How do I fix this problem?
 
One of the things I've learned in database design is that for the
purpose of generating unique keys random does not mean mathematically
random. What I mean is that for the purpose of generating keys,
sequential is just as good as random, and as you've noticed, unique is
the important part. What you're asking for a GUID, which you won't get
from Access, you can't count on a random long to be unique among
multiple DBs.

That said, I work with an application that has a similar problem, which
is occasionally connected users. One solution you could look into is Jet
replication. This could allow you to let Jet handle keeping the DBs in
sync. I don't have much experience with it, but it may be worth looking
into. Another solution is to give the local records temporary ID values
and when the user syncs up to the server let the server generate the ID
and then sync the ID back to the local machine. A third solution, that
has worked best for me, is to use SQL server on the server as a
replication publisher and use MSDE on the client as subscribers using
merge replication. This may not be an option in your environment, but I
found it to be most reliable.

HTH,
Ben
 
Back
Top