How to Lock a SQL Server Record Using MS Access DAO

>Currently using MS Access 2000 and SQL Server Express. Using the
>current DAO OpenRecordset code listed below, however I keep getting
>the error message....
>3254: ODBC --Cannot lock all records
>
>The code below runs in a MS Access frontend while the table is linked
>using an ODBC connected to an SQL Server (backend). The table
>(located in SQL) only has one row and one column within it . I want
>to try and stop any other request (other frontend clients) from being
>able to access the record while someone is getting the case number (a
>unique number).
>
>This processed worked while all the tables were located in MS Access,
>but does not work since the tables have been upgraded. While in MS
>Access, all new request to access the table was given an chance to get
>a number once the previous client call was completed [would loop them
>in the error section until the table was released]
>
>However just not sure if the SQL table is setup wrong, or I need to
>use a different type of option within the DAO openRecordset command.
>Can't upgrade the system to ADO, not enough time....
>
>Would be thankful to any help that could be provided.....Here is the
>code:
>
>Set CaseRst = CurrentDb.OpenRecordset("SELECT CaseNumber FROM
>tblCaseNumber", dbOpenDynaset, dbDenyWrite, dbPessimistic)
>
>CaseRst.edit
With CaseRst
>'Get Case Number then Increase by one
CaseNumber = CaseRst![CaseNumber]
![CaseNumber] = CaseNumber + 1
.UPDATE
End With
>CaseRst.Close
>
>Exit_CaseNumber:
Exit Function
>
>Err_CaseNumber:
>'If caserst is locked - Program comes here
Select Case Err
Case 3197
intLockRetry = intLockRetry + 1
If intLockRetry < LOCK_RETRY_MAX Then
For i = 0 To intLockRetry * 1000
Next i
Resume
Else
ErrorMsg = "Date: " & Date & Chr(13) & Chr(10) & "Time: " &
>Time & Chr(13) & Chr(10) _
& "Possible Case Number Lost: " & CaseNumber & Chr(13)
>& Chr(10) _
& "Access Error: " & Err.Number & ": " &
>Err.Description
FormattedMsgBox "Case Number: " & CaseNumber & " Was NOT Added
>To The Database. Please Inform " _
& "Your Supervisor The Case Number, Date & Time" & Chr(13)
>& Chr(13) _
& "You Will Have To Press The Button Again", vbCritical,
>"Serious Error"
End If
Case 3260
intLockRetry = intLockRetry + 1
If intLockRetry < LOCK_RETRY_MAX Then
For i = 0 To intLockRetry * 1000
Next i
Resume
Else
ErrorMsg = "Date: " & Date & Chr(13) & Chr(10) & "Time: " &
>Time & Chr(13) & Chr(10) _
& "Possible Case Number Lost: " & CaseNumber & Chr(13)
>& Chr(10) _
& "Access Error: " & Err.Number & ": " &
>Err.Description
FormattedMsgBox "Case Number: " & CaseNumber & " Was NOT Added
>To The Database. Please Inform " _
& "Your Supervisor The Case Number, Date & Time" & Chr(13)
>& Chr(13) _
& "You Will Have To Press The Button Again", vbCritical,
>"Serious Error"
End If
Case 3186
intLockRetry = intLockRetry + 1
If intLockRetry < LOCK_RETRY_MAX Then
For i = 0 To intLockRetry * 1000
Next i
Resume
Else
ErrorMsg = "Date: " & Date & Chr(13) & Chr(10) & "Time: " &
>Time & Chr(13) & Chr(10) _
& "Possible Case Number Lost: " & CaseNumber & Chr(13)
>& Chr(10) _
& "Access Error: " & Err.Number & ": " &
>Err.Description
FormattedMsgBox "Case Number: " & CaseNumber & " Was NOT Added
>To The Database. Please Inform " _
& "Your Supervisor The Case Number, Date & Time" & Chr(13)
>& Chr(13) _
& "You Will Have To Press The Button Again", vbCritical,
>"Serious Error"
End If
Case 3262
intLockRetry = intLockRetry + 1
If intLockRetry < LOCK_RETRY_MAX Then
For i = 0 To intLockRetry * 1000
Next i
Resume
Else
ErrorMsg = "Date: " & Date & Chr(13) & Chr(10) & "Time: " &
>Time & Chr(13) & Chr(10) _
& "Possible Case Number Lost: " & CaseNumber & Chr(13)
>& Chr(10) _
& "Access Error: " & Err.Number & ": " &
>Err.Description
FormattedMsgBox "Case Number: " & CaseNumber & " Was NOT Added
>To The Database. Please Inform " _
& "Your Supervisor The Case Number, Date & Time" & Chr(13)
>& Chr(13) _
& "You Will Have To Press The Button Again", vbCritical,
>"Serious Error"
End If
Case Else
FormattedMsgBox Err.Number & ": " & Err.Description,
>vbCritical + vbOKOnly, "Function: CaseNumber"
Resume Exit_CaseNumber
End Select

>Use ADO instead of ODBC when interfacing between Access and Sql Server.
>
>Disclaimer: There are people in this NG who seem to have a problem
>either with using ADO or with me suggesting to use ADO over ODBC. My
>reply here is based on years of experience using ADO instead of ODBC for
>manipulating data on a sql server from Access. I have had significantly
>less problems with ADO than with ODBC (and even less than that with
>ADO.Net except that ADO.Net only works in a .Net environment).
>

On Wed, 23 Jul 2008 15:12:00 -0700 (PDT), Mark <mmazur7...@gmail.com>
wrote:
>
Take out ", dbDenyWrite, dbPessimistic" and try again.
>
Of course you realize you can do this with a simple SQL statement in a
tiny fraction of the time:
update tblCaseNumber
* set CaseNumber = CaseNumber+1
>
-Tom.

>On Jul 24, 12:06*am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:>
>
>Hey Tom, not sure what you mean by this
>
>Are saying that the code would look like this? And it would lock the
>record so that no one else could access it?
>
>Set CaseRst = CurrentDb.OpenRecordset("SELECT CaseNumber FROM
>tblCaseNumber", dbOpenDynaset)
update tblCaseNumber
set CaseNumber = CaseNumber+1
>CaseRst.Close
>