How to Lock a SQL Server Record Using MS Access DAO

**Rich P** · Jul 23 '08, 10:55 PM

Re: How to Lock a SQL Server Record Using MS Access DAO

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.ActiveConne ction = "Provider=SQLOL EDB; Data
Source=yourSvr; Database=yourDB ;Trusted_Connec tion=Yes"
'--or
'--cmd.ActiveConne ction = "Provider=SQLOL EDB; Data
Source=yourSvr; Database=yourDB ;UID=Steve;pass word=tiger;"
cmd.ActiveConne ction.CursorLoc ation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = "Select * from yourTbl"
Set RS = cmd.Execute
Do While Not RS.EOF
Debug.Print RS(0)
Loop
cmd.ActiveConne ction.Close
End Sub

Rich

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

**Tom van Stiphout** · Jul 24 '08, 04:15 AM

Re: How to Lock a SQL Server Record Using MS Access DAO

On Wed, 23 Jul 2008 15:12:00 -0700 (PDT), Mark <mmazur7973@gma il.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.....He re is the
>code:
>
>Set CaseRst = CurrentDb.OpenR ecordset("SELEC T 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.Clos e
>
>Exit_CaseNumbe r:
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.Descriptio n
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.Descriptio n
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.Descriptio n
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.Descriptio n
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

**Tom van Stiphout** · Jul 24 '08, 04:15 AM

Re: How to Lock a SQL Server Record Using MS Access DAO

On Wed, 23 Jul 2008 17:49:23 -0500, Rich P <rpng123@aol.co mwrote:

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>

**Mark** · Jul 24 '08, 12:15 PM

Re: How to Lock a SQL Server Record Using MS Access DAO

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...@gma il.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.OpenR ecordset("SELEC T CaseNumber FROM
tblCaseNumber", dbOpenDynaset)
update tblCaseNumber
set CaseNumber = CaseNumber+1
CaseRst.Close

**Tom van Stiphout** · Jul 24 '08, 01:35 PM

Re: How to Lock a SQL Server Record Using MS Access DAO

On Thu, 24 Jul 2008 05:08:54 -0700 (PDT), Mark <mmazur7973@gma il.com>
wrote:

No, you can't open a recordset on an Action query like an Update
query.
Currentdb.Execu te 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...@gma il.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.OpenR ecordset("SELEC T CaseNumber FROM
>tblCaseNumber" , dbOpenDynaset)
update tblCaseNumber
set CaseNumber = CaseNumber+1
>CaseRst.Clos e
>

**Rich P** · Jul 24 '08, 03:45 PM

Re: How to Lock a SQL Server Record Using MS Access DAO

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

How to Lock a SQL Server Record Using MS Access DAO

How to Lock a SQL Server Record Using MS Access DAO

Comment

Comment

Comment

Comment

Comment

Comment