Negative Autonumbers

[lsh2001]

I trying to create a table where the primary key is a customerID, using the autonumber function. I would like to leave the new value setting on Random, since two people may be using database. However, I'm getting a lot of negative numbers as CustomerID's. If there a way I can limit the pool of random numbers to positive only.

 

[llkhoutx]

Don't worry about the negative numbers. A key is a key is a key. You just get double the random numbers.

More important is to re-seed the random numbe generator with a new number each time the program is executed, I use Now(). Otherwise, without re-seeding. your random numbers are generated starting in the same (set) sequence.

 

[Pat Hartman]

Usually the only time you would use random for autonumbers is if you are using replication (there is actually no choice in this case. You MUST use random) or if the sequential value would not allow enough values.

Just because multiple users are using the database doesn't require you to use random autonumbers. As long as the be is shared on a server, Jet will handle the autonumber assignment properly so change back to the increment option.

llkhoutx, rather than having a user controlled incrementing sequential number in addition to an autonumber, many people use the autonumber as a "visible" number such as CustomerID or InvoiceID. This is perfectly acceptable as long as you don't get hung up on the possible skipped numbers.

 

[llkhoutx]

Pat -

If you don't re-seed the random number generator on program startup, my experience is that without a re-seed the same exact sequence of randon numbers are regenerated. I did not say

hav(e)[ing] a user controlled incrementing sequential number

I use random table keys because, from time to time, a portion of one of my databases is exported and used outside the network where additional records and modifications are generated with additions/changes being reintegrated back into the network BE. Without random keys, I'd get duplicates.

 

[Pat Hartman]

Your situation is similar to replication. Multiple separate db each collecting data that eventually need to be merged. When you NEED to use random autonumbers, I agree, they should be reseeded. In that case you also need your own generated numbers if you need some ID that customers see.