Help: Creating a unique reference number in a database

I need to use MTS and also create unique numbers in a database so that it is hopefully unique for any process attaching to it. However I
can not seem to be able to do this.

I am using Access (SQL Server and Oracle can supposedly automatically produce this) but access can't do it.

The following function grabs a record and adds one after a specified number
of seconds.

I can fire off 2 apps using this MTS function. App 1 is set to 10 second
delay and app 2 is zero delay. App 1 grabs a number, then App 2 grabs a
number and App 2 inserts the new one. App 1 then tries to insert the same
value.

How can this be over come.

Thank you

David

Public Function Test(N As Integer) As String
Dim oContext As ObjectContext
Set oContext = GetObjectContext
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
Conn.Open "Driver={Microsoft Access Driver
(*.mdb)};DBQ=hr.mdb;DefaultDir=F:\HR;Uid=Admin;Pwd=;"
Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
Conn.BeginTrans
RS.Open "SELECT MAX(ID) FROM tTest", Conn, adOpenDynamic,
adLockPessimistic
Dim nID As Long
nID = RS(0) + 1
Dim strSQL As String
Dim Start
Start = Timer
While Timer < Start + N
DoEvents
Wend
strSQL = "INSERT INTO tTest (ID) VALUES (" & CStr(nID) & ")"
Conn.Execute strSQL
RS.Close
Set RS = Nothing
Conn.CommitTrans
Conn.Close
Set Conn = Nothing
Test = CStr(nID)
'Test = "Completed"
If Not oContext Is Nothing Then GetObjectContext.SetComplete
End Function

Is there a reason why you do not use Access' AutoNumber fields?

I agree AutoNumber will generate a unique number, but how do you guarantee to obtain theis number when you insert it. ie with 2 processes...
Process 1: Insert -> ID = 100
Precess 2: Insert -> ID = 101
Process 2: Select -> ID = 101
Process 1: Select -> ID = 101

Hence Process 1 has the wrong number.

Need some locking mechanism for Access or something to get the value back on an insert.

The Jet Database Engine will assign the number for you, you cannot set it. In Access the only way that I know of the get the number back is to re-read the record. You should also be aware that just because the last record in the database has a key of 101, that does not mean that the next record inserted will have a key of 102. If someone inserted 1000 records after the 101 record then deleted them the next insert will produce a key of 1102 not 102.

Is it possible to write a record and read the same record guaranteed? Can you AddNew with a recordset object and requery the record you added?
Could you supply me with a bit of code to do this?
Thank you

Why not just create a small single field, single record table called, for example, "NextID". The single field you can call "ID". Then whenever you need to insert a new record you: lock the table, get the current value of ID and store it in a variable, increment the value of the field, and release the lock. Since each process has an exclusive lock on the little table, it is assured of getting a unique value. You can then use this value as your unique key to the table you want to insert in.

BTW, if you have numerous tables where you need this functionality you can just add fields to the NextID table for each one. Thus you will never need to have more than one record in this table. Access should be very fast.

Let me know if this works for you by posting a reply on the board. Thanks.

How do you lock Access? I have thought of this, but my problem has been actually locking Access. I have tried Conn.IsolationLevel = adXactRepeatableRead with SQL Server and it works, but I can not find an equivalent locking mechanism for Access.
Is it possible?
What code have you got for this?
Thanks

You lock Access by using the appropriate parameter when you open the recordset/resultset you are using. I don't know if you're getting to the Access database from within Access or via VB, or if you are using DAO, RDO or ADO. But, for example, in DAO when you open a recordset you use the "lockedits" parameter (the 4th parameter) to set the locking option you want.

Set rstRS = dbCurrent.OpenRecordset(strSQL,,,dbPessimistic)

where strSQL is the SQL string you are executing, and dbPessimistic is the lockedits parameter.

In RDO, which you would use only from VB, you use a similar syntax except the parameter is called the "locktype" and it is the third parameter. For example,

Set rstRS = gRDOConn.OpenResultset(sSQL, rdOpenDynamic, rdConcurLock)

I haven't studied the ADO version of this.

Hope this helps.

Steven

I'm still confused about why you can't just use autonumber. Even if you have two processes going, connecting to the same table, you set the lock to pessimistic in your code which means one process can't write while the other process is writing and you opened both recordsets as dynamic which means they both recieve updates made to the table. Anyway when one process finishes writing the autonumber will automatically step at the next write call so...
Trying to create your own unique ID number is probably not a good idea but you can set a constraint on the field to make the DB enforce the fields unique value if your worried about that.
Autonumber will increment automatically each time a new record is added, you don't need to supply the value with an INSERT statement.