Many clients - increment by1 field problem

[geo__]

Hi all,

One of the columns in my database is a protocol number. This number has to increment by 1 each time there is a new entry, and has to be unique.

The problem is that this database has already protocol values and is accessed by many clients.

So just finding the last protocol and incrementing by 1 will not work.(This is what they did till now and they ended up with duplicate values which I cleaned).

I don't think I can make this field autoincrement, can I? It already has about 6000 entries (1-6000 but some are missing).

So I would like the next entry to be 6001. The problem is the many clients.

Any ideas would be more than welcome,
Thnx,
George

 

[geo__]

Hi Pat,
Quoting you from another article that I found:
"Using DMax() to find the most recent sequence number and adding 1 to it to obtain the next number may sound like a simple solution but if your people are really doing simultaneous data entry, that code is quite likely to generate duplicate id's...."

which of course I don't want duplicate ids because protocol number should be unique.

I will try the first solution.

In that case does this mean that I should NEVER do compact in the database?

Because compacting will change the ids(=protocols) which I don't want to do EVER.

For instance if i have ids 1,2,3,5,6 i think when compacting it will do 1,2,3,4,5

 

[geo__]

Thnx Pat,
everything is clear now