MS Access Multiuser - Pessimistic Locking with Access MDB Frontend and SQL Backend

Author

Pessimistic Locking with Access MDB Frontend and SQL Backend

Andreas Stransky via AccessMonster.com

2005-04-27, 7:24 am

I have a case in which i use a MDB as frontend and some MsSQL 2000 tables
as backend (i CANNOT use ADP, since i also have to connect to linked Tables
in some other MDBs, this doesn't work in ADP as far as i know).

I want to implement a pessimistic locking (locking on record level) with a
form that connects to a sql table, but i didn't manage it yet.

I have tried the same with MDB frontent / MDB backend, here it works fine.
When a user ist editing a record and another user tries to enter edit mode
on that record he gets a prohibit sign instead of the pencil icon that
normally appears in edit mode. This just the functionality that i want for
the MDB frontend with SQL backend case.

Can someone tell me how i can have this pessimistic locking feature with my
MDB / SQL combination?

thx in advance
Andy

Alex White MCDBA MCSE

2005-04-29, 3:27 am

Hi,

I wrote my own functions to do this work, simply put, add 2 fields to your
table r_locktext., r_lockuser

when a user opens that recordset in a form CheckLock is run, if that is ok
then PlaceLock is run, when the user quits the form RemoveLock is run, when
the db is first run the ClearUsrLock is run to clear all the locks for the
user, the reason I wrote these functions was pessimistic locking has all
sorts of issues in multi-user environments and these functions were written
5+ years ago in a database supporting 150+ users, sorry for the coding but
it gives you some idea of where to go if you are going to code this.

Function PlaceLock(strTable As String, strSQL As String) As String
10 On Error GoTo Err_PlaceLock
Dim adoLock As New ADODB.Recordset
Dim sqlLock As String
Dim strMachine As String
Dim strLock As String
20 sqlLock = "Select * From " & strTable & " Where " & strSQL
' ' call statusbar("Checking to see if the policy is locked")
30 strLock = CheckLock(sqlLock, strTable)
40 If strLock = "Record Deleted" Then
50 PlaceLock = strLock
60 ElseIf strLock = "" Then
70 If strUser = "" Then
80 strUser = UCase(ReturnNTLogin(
))
90 End If
100 strMachine = ModNetwork.CurrentMachineName
110 adoLock.Open sqlLock, CurrentProject.Connection, adOpenStatic,
adLockOptimistic
120 With adoLock
130 !r_locktext.Value = "Locked on: " & strMachine & " at: " &
Format(Now(), "short Time") & " " & Format(Now(), "d/m/yyyy")
140 !r_lockuser.Value = strUser
' ' call statusbar("Locking the policy record")
150 .Update
160 End With
170 adoLock.Close
180 PlaceLock = ""
190 Else
200 PlaceLock = strLock
210 End If
' ' call statusbar(" ")
Exit_PlaceLock:
220 Exit Function
Err_PlaceLock:
230 If Err.Number = 3021 Then
240 PlaceLock = "Deleted Record"
250 GoTo Exit_PlaceLock
260 Else
270 MsgBox Err.Description & vbCrLf & Erl() & vbCrLf &
Str(Err.Number), vbCritical, "dbFuncs [PlaceLock]"
280 Resume Next
290 End If
End Function

Function CheckLock(strSQL As String, strTable As String) As String
10 On Error GoTo Err_CheckLock
Dim adoLock As New ADODB.Recordset
20 adoLock.Open strSQL, CurrentProject.Connection, adOpenStatic,
adLockReadOnly
30 If Not IsNull(adoLock!r_loc
kuser.Value) Then
40 If Len(Trim(adoLock!r_l
ockuser.Value)) > 0 Then
50 CheckLock = adoLock!r_locktext & vbCrLf & "By: " &
adoLock!r_lockuser & vbCrLf & "Table Name: " & strTable
60 If MsgBox(CheckLock & vbCrLf & " Press OK to unlock!",
vbOKCancel + vbExclamation, "Confirmation!") = vbOK Then
70 If RemoveLock(strTable,
strSQL) = True Then
80 CheckLock = ""
90 End If
100 End If
110 Else
120 CheckLock = ""
130 End If
140 Else
150 CheckLock = ""
160 End If
170 adoLock.Close
Exit_CheckLock:
180 Exit Function
Err_CheckLock:
190 If Err.Number = 3021 Then
200 CheckLock = "Record Deleted"
210 GoTo Exit_CheckLock
220 Else
230 MsgBox Err.Description & vbCrLf & Erl() & vbCrLf &
Str(Err.Number), vbCritical, "dbFuncs [CheckLock]"
240 Resume Next
250 End If
End Function

Function RemoveLock(strTable As String, strSQL As String) As Boolean
10 On Error GoTo Err_RemoveLock
Dim sqlLock As String
' ' call statusbar("Removing all locks")
20 sqlLock = "Update " & strTable & " Set r_lockuser = '', r_locktext
= '' Where " & strSQL
30 CurrentProject.Connection.Execute (sqlLock)
40 RemoveLock = True
' ' call statusbar(" ")
Exit_RemoveLock:
50 Exit Function
Err_RemoveLock:
60 MsgBox Err.Description & vbCrLf & Erl() & vbCrLf &
Str(Err.Number), vbCritical, "dbFuncs [RemoveLock]"
70 Resume Next
End Function

Function ClearUsrLock(strTabl
e As String, strUser As String) As Boolean
10 On Error GoTo Err_ClearUsrLock
Dim sqlLock As String
' ' call statusbar("Clearing locks for " & strUser & " on table
" & strTable)
20 sqlLock = "Update " & strTable & " Set r_lockuser = '', r_locktext
= '' Where r_lockuser='" & UCase(Trim(strUser))
& "'"
30 CurrentProject.Connection.Execute (sqlLock)
40 ClearUsrLock = True
' ' call statusbar(" ")
Exit_ClearUsrLock:
50 Exit Function
Err_ClearUsrLock:
60 MsgBox Err.Description & vbCrLf & Erl() & vbCrLf &
Str(Err.Number), vbCritical, "dbFuncs [ClearUsrLock]"
70 Resume Next
End Function

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

"Andreas Stransky via AccessMonster.com" <forum@nospam.AccessMonster.com>
wrote in message news:40807a3f4a4e4a8
3a07ffab85b5d5201@Ac
cessMonster.com...
>I have a case in which i use a MDB as frontend and some MsSQL 2000 tables
> as backend (i CANNOT use ADP, since i also have to connect to linked
> Tables
> in some other MDBs, this doesn't work in ADP as far as i know).
>
> I want to implement a pessimistic locking (locking on record level) with a
> form that connects to a sql table, but i didn't manage it yet.
>
> I have tried the same with MDB frontent / MDB backend, here it works fine.
> When a user ist editing a record and another user tries to enter edit mode
> on that record he gets a prohibit sign instead of the pencil icon that
> normally appears in edit mode. This just the functionality that i want for
> the MDB frontend with SQL backend case.
>
> Can someone tell me how i can have this pessimistic locking feature with
> my
> MDB / SQL combination?
>
> thx in advance
> Andy

Andreas Stransky via AccessMonster.com

2005-04-29, 8:27 pm

Hi Alex,

thank you very much for your code listing, It helps me a lot.
I was already considering about handling the locking this way,
you posting has convinced me doing so and your code is a very good help for
creating my locking routines.

regards
Andy

--
Message posted via http://www.accessmonster.com

david epsom dot com dot au

2005-05-02, 3:25 am

> as backend (i CANNOT use ADP, since i also have to connect to linked
> in some other MDBs, this doesn't work in ADP as far as i know).

Nothing is ever 'CANNOT' :~)

If you wanted to do this, SQL Server can
link to Jet tables, just like Jet can link
to SQL Server tables.

(david)

"Andreas Stransky via AccessMonster.com" <forum@nospam.AccessMonster.com>
wrote in message news:40807a3f4a4e4a8
3a07ffab85b5d5201@Ac
cessMonster.com...
>I have a case in which i use a MDB as frontend and some MsSQL 2000 tables
> as backend (i CANNOT use ADP, since i also have to connect to linked
> Tables
> in some other MDBs, this doesn't work in ADP as far as i know).
>
> I want to implement a pessimistic locking (locking on record level) with a
> form that connects to a sql table, but i didn't manage it yet.
>
> I have tried the same with MDB frontent / MDB backend, here it works fine.
> When a user ist editing a record and another user tries to enter edit mode
> on that record he gets a prohibit sign instead of the pencil icon that
> normally appears in edit mode. This just the functionality that i want for
> the MDB frontend with SQL backend case.
>
> Can someone tell me how i can have this pessimistic locking feature with
> my
> MDB / SQL combination?
>
> thx in advance
> Andy

Sponsored Links