Developer Forum

I built a multi-user contacts application as a split Access database
initially and then moved the back end to SQL Server 2005 using an ODBC
connection. I implemented an AutoNumber workaround to avoid primary key
collisions when users attempt to add records at the same time (probably
borrowed from Allen Browne or Albert Kallal?) by setting the default value of
the ContactID field = DMax("ContactID", "tblContacts") + 1 and trapping any
duplicate key errors as follows:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
On Error GoTo Err_Form_Error
Response = IncrementField(DataErr)
Exit_Form_Error:
Exit Sub
Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error
End Sub
---------------------------------------------------------------------------------
Function IncrementField(DataErr)
If DataErr = 3022 Then
Me!ContactID = DMax("ContactID", "tblContacts") + 1
IncrementField = acDataErrContinue
End If
End Function

This works well with an Access back end, but no longer works since
connecting to SQL Server 2005. I'm guessing the error returned by ODBC is
different? Is there a better way to manage pk fields in multi-user apps when
working with SQL Server?

Thanks.