How to Lock a SQL Server Record Using MS Access DAO

Mark

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

Jul 23 '08 #1

Rich P

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).

Here is some sample usage (need to make a reference in Tools/References
to Microsoft ActiveX Data Objects 2.x Library -- need 2.5 or higher):

Sub readFromSqlSvr()
Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourSvr;Database=yourDB;Trusted_Connection= Yes"
'--or
'--cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourSvr;Database=yourDB;UID=Steve;password= tiger;"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = "Select * from yourTbl"
Set RS = cmd.Execute
Do While Not RS.EOF
Debug.Print RS(0)
Loop
cmd.ActiveConnection.Close
End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***

Jul 23 '08 #2

Tom van Stiphout

On Wed, 23 Jul 2008 15:12:00 -0700 (PDT), Mark <mm********@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.

>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

Jul 24 '08 #3

Tom van Stiphout

On Wed, 23 Jul 2008 17:49:23 -0500, Rich P <rp*****@aol.comwrote:

Out of curiosity:
* If you think ADO is superior when working with a SqlServer backend,
would you not use ADP, which is a more pure ADO environment?
* If using MDB + ADO, how do you use bound forms? Me.RecordSource =
rs?

Thanks,

-Tom.
Microsoft Access MVP

>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).

<clip>

Jul 24 '08 #4

Mark

On Jul 24, 12:06*am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:

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.

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

Jul 24 '08 #5

Tom van Stiphout

On Thu, 24 Jul 2008 05:08:54 -0700 (PDT), Mark <mm********@gmail.com>
wrote:

No, you can't open a recordset on an Action query like an Update
query.
Currentdb.Execute strSQL, dbFailOnError
It will run so fast locking is not needed.

-Tom.
Microsoft Access MVP

>On Jul 24, 12:06*am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:

>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.

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

Jul 24 '08 #6

Rich P

>>
Out of curiosity:
* If you think ADO is superior when working with a SqlServer backend,
would you not use ADP, which is a more pure ADO environment?
* If using MDB + ADO, how do you use bound forms? Me.RecordSource =
rs?

Thanks,

-Tom.
Microsoft Access MVP
<<

Also based on years of experience, I have had more issues with ADP's
than with mdb's. If I have a large project that uses an ADP - I will
just migrate the whole thing over to .Net. But for smaller projects
which still use a sql server for the backend and Access for the
frontend, I find that using an mdb is easier to develop than the ADP -
plus you are in the native Access environment and only need ADO for
pulling/pushing and carrying out data edits.

The mdb is more like a pseudo .net app in that with ADO you are using
disconnected recordsets like in .Net -- except that instead of doing
everything in memory you do have I/O on the disk. But mdb tables
function pretty much the same as .Net dataTables - again - except for
the part where the mdb tables are persistent (written to the disk) and
the .Net tables are all in memory only. And with this configuation -
you can still use DAO within the mdb. There is no DAO with the ADP.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Jul 24 '08 #7