Unique primary-keys in recordsets ?

[Guest]

I get an Error -2147217887 when using rs.AddNew in multi user environment.

Both processes do a :

rs.AddNew
rs("RIID") = DMax("ID", "Table")
rs.Update

They compete for the same primary-keys vlaue. Is there a way to lock a
primary-keys vlaue so it would not be created twice ?

 

[David C. Holley]

Are the tables in a one-to-one relationship?

 

[Guest]

it is one single tabe in a back-end db. all front-ends do the AddNew at the
same time and report errors...........

maybe an error handler would do it ?

 

[David C. Holley]

The field rs("RIID") is an Autonumber, it is not neccessary to set it
using code. Access will do that when the record is added. If that's not
the case, could you please elaborate on what information the DMax() is
looking up? Is it trying to get the DMax() for the table that you're
trying to add the record to? Is it looking up the DMax() from 1 table
with the intent of using it for the table into which your adding the record.

 

[tina]

well, are you trying to look up the "highest" ID number in a table, and then
set the primary key of a new record *in the same table* ? perhaps you need
to increment the DMax value to achieve uniqueness, as

rs("RIID") = DMax("ID", "Table") + 1

hth

 

[Guest]

One way to avoid conflicts in a multi-user environment is to store the last
number in a separate database which is opened exclusively to get the next
number. The following module does this:

''module begins'
Option Compare Database
Option Explicit

Dim dbsCounter As DAO.Database, rstCounter As DAO.Recordset

Public Function GetNextNumber() As Long

Const NOCURRENTRECORD As Integer = 3021

Set rstCounter = dbsCounter.OpenRecordset("tblCounter")

On Error Resume Next
With rstCounter
.Edit
' insert new row if table is empty
If Err = NOCURRENTRECORD Then
.AddNew
!NextNumber = 1
.Update
GetNextNumber = 1
Else
' update row and get next number in sequence
!NextNumber = !NextNumber + 1
.Update
GetNextNumber = rstCounter!NextNumber
End If
End With

End Function


Public Function OpenCounterDb(strCounterDb) As Boolean

' Opens external Counter database exclusively
' Returns True if able to open external database

Dim n As Integer, I As Integer, intInterval As Integer

' make 10 attempts to open external database exclusively
DoCmd.Hourglass True
SysCmd acSysCmdSetStatus, "Attempting to get new number"
On Error Resume Next
For n = 1 To 10
Err.Clear
Set dbsCounter = OpenDatabase(strCounterDb, True)
If Err = 0 Then
Exit For
Else
intInterval = Int(Rnd(Time()) * 100)
For I = 1 To intInterval
DoEvents
Next I
End If
Next n
SysCmd acSysCmdClearStatus
DoCmd.Hourglass False

If Err = 0 Then
OpenCounterDb = True
End If

End Function

Public Function CloseCounterDb()

On Error Resume Next

' close recordset and external databse if open
rstCounter.Close
dbsCounter.Close

Set rstCounter = Nothing
Set dbsCounter = Nothing

End Function
'module ends'

The external database is Counter.mdb with a table tblCounter with a Long
Integer column NextNumber.

The code is called like so, in this example in the context of a form bound
to the table containing the RIID column:

Dim strCounterDb As String, lngID As Long

strCounterDb = "F:\SomeFolder\Counter.mdb"

'attempt to get next number
If Not OpenCounterDb(strCounterDb) Then
MsgBox "Unable to get ID number at present.", vbInformation, "Error"
Else
Me!RIID = GetNextNumber()
' close external counter database
CloseCounterDb
End If