Can anyone advise me on the following?

I have an application that controls/views/edits a set of six databases, each has the same structure. I am using VB6 and a DAO data object.  When a record is added the program indexes the records, moves to the last record and increments the number of the last record to give the reference number of the record that is about to be added.  The application is run by 8 PC's across a network (LAN), each PC can view the same database at the same time.  Currently only one user adds records to one database at a time.

Here's the question:-

If two users are adding records to the same database, how do I ensure I never get a duplicated reference number.

i.e. if a user adds a record, and is in the process of entering the information, then another user attempts to add a record to the same Dbase, they will recieve the same reference number because the last record has not yet been added by the previous user.

One solution I am considering is the following:-

Data1.recordset.addnew
Call AssignReference
Data1.recordset.update
LastRecord=Data1.recordset.bookmark
data1.refresh
Data1.recordset.bookmark=LastRecord
Data1.Recordset.edit

i.e. create the new record and update/refresh the database straight away and then go back to the record to edit it, the user would not see and great slow down in entering the record, and the database always has the latest reference number to work from.

Is there still a possibility that the same reference number could crop up if two users add a record at exactly the same time??

Any ideas/information/code greatly appreciated.

Regards

Shady



____________________________
I don't wanna die... but I ain't keen on livin' either

just a quick one....

a suggestion would be to insert code to check an instance of the record, before adding it.

another would be to use time measurements to accept the one who keyed in first, in case they seem to add at the same time.

another would be to use a cueing system, though this may be more tedious.

sorry, for a short one, shady.

____________________________
Been busy trying to take a second degree <--it's not working out...

In addition to what's been said...

You could make the field a primary key (no duplicates), then put error handling in to trap for a duplicate key. Although I'd also check before adding the record also, just in case.

You can't make it an autonumber field?

Reason why I'd do both primary key and check for a record:

Comp1: reference1
Comp2: reference1 (causes error)
Comp3: reference2
Comp2: receives reference2 since not stored yet from Comp3
Comp3 or Comp2 errors since reference2 already used

Thanks for the ideas guys, I have had an idea myself, and would appreciate a little help on how to execute it properly.

What if the next reference number for each database is held in a separate dbase.

The procedure would be something like this:-

Open Reference Dbase exclusively
Get reference number
Add one to reference number
Update reference Dbase
Use new reference number in main Dbase

This means that you can only add a record if you have exclusive access to the number reference DBase.

So what I require is the code to open a Dbase exclusively, and the error handler when two users attempt to access the dbase at the same time.

Any offers??

Regards

Shady

____________________________
I don't wanna die... but I ain't keen on livin' either

another short one...

try a first come first connected thingy, that actually begins at boot time.

the idea would be around openning a connection between the first to log in, and then, after the final button would be pressed, and the record or database is updated, then close his connection, and the connecting would be to the next one who cues in first.

i know, i know... it's a little messy.
i'm sorry.     

____________________________
Been busy trying to take a second degree <--it's not working out...