Autonumber Workaround with SQL Server 2005 BE

Autonumber Workaround with SQL Server 2005 BE

Post by Si4gTXVsbG » Wed, 12 Dec 2007 06:10:00

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 Sub
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?


1. SQL Server 2005 DTS Migration Tool (SQL Server 2000 to SQL Server 2005)

2. Synchronising SQL Server 2005 mobile with SQL Server 2005 Express

I have an application which runs on a desktop.
I want to synchronise the DB with a PDA app, which also uses SQL Server 2005
(mobile and express).

I believe that I will need to write my own synchronization application, in
order to update the data across the machines.
My questions are:
- Is there a way of picking up an event that will notify my application when
the PDA is connected to the desktop??
- How can I connect to the PDA DB from the Desktop application?? (ie. do I
just use a connection string from the Desktop machine, but which connects to
the PDA DB??)
- Once I have this connected, I presume I will be able to compare the
data on the desktop machine, against the data on the PDA, and update each

Any examples of this being done, or details of best practices for this would
be much appreciated


3. Can I restore SQL Server Express 2005 to SQL Server 2005?

4. Do SQL Server 2005 Standard and SQL Server 2005 co-exist?

5. Sync SQL Server Express 2005 and SQL Server Mobile 2005.

6. SQL Server 2005 standard edition vs SQL Server 2005 express

7. MCTS SQL Server 2005 & MCTS SQL Server 2005 Business Intelligence

8. SQL Server 2005 EE DB auf SQL Server 2005

9. SQL Server 2005 Express with SQL Server 2005 Standard install

10. SQL Server 2005 Management Studio with SQL Server 2005 Express

11. SQL Server Express 2005 and SQL Server 2005 Standard Side-by-

12. Upgrading the SQL Server 2005 Standard to SQL Server 2005 Enterpri

13. Upgrade fails: SQL Server Express 2005 to SQL Server 2005

14. Confusion - Reporting services, VS 2005, SQL Server 2005, VS 2003, SQL Server 2000

15. Can SQL Server 2005 Express and SQL Server 2005 be installed on same machine?