Record locking - Integrating Access with Sharepoint List


D

Dale Fye

For those in the Access.FormsCoding newsgroup, this is a repost from
yesterday, with additional info. For the rest of you, this is new.

I'm attempting to use SharePoint 2007 lists as linked tables in an Access
application (long story). Up until yesterday, this was working well, with
my being able to open forms and edit list data without any problem. However,
my understanding is that Sharepoint doesn't do a very good job of handling
record locking, so I thought I might try to implement my own.

My concept was that I would lock each record, as it is displayed on my
Access form
(in the Current event), so that no other user can edit, the record, while
the user
that got to that record first has it displayed in his form. I do this by
setting the value of the IsLocked field to true in the Current event of the
form, but only do it if that value is not already set. If it is already set,
then I lock all of the data controls on the form. Then, when I move to the
next record or close the form, I check to see whether I locked the previous
record, and attempt to unlock it by setting the [IsLocked] value to False.
Unfortunately, I'm getting an error message (Runtime
error #3218: Could not update; currently locked) when I try to implement the
Currentdb.execute line).

Subsequent to posting my first message to the FormsCoding newsgroup, I
imported the applicable Forms, tables (lists), queries and code into a test
application (mdb), and imported the list data into an Access backend mdb. I
then opened two instances of this test application (front end), and the code
below worked properly, locking the form when another user is viewing the same
record.

This points me to the conclusion that the linkage to the Sharepoint list is
what is causing the error message. It only generates this message if I
locked a record (set [IsLocked] to true).

Does anyone have any idea why this code is not working with the SharePoint
list as a linked table?

Private Sub Form_Current()

Dim strSQL As String

'Refresh the current record to account for changes in the
'[IsLocked] field
Me.Refresh

'If the users previous record is locked, then unlock it
If lngID <> 0 Then
strSQL = "UPDATE tbl_POCs SET [IsLocked] = 0 " _
& "WHERE [ID] = " & lngID
CurrentDb.Execute strSQL, dbFailOnError
lngID = 0
End If

'If the current record is not locked, then lock it so
'no one else can edit it
If Not Me.IsLocked Then
lngID = Me.ID
Me.IsLocked = True
Me.Dirty = False
Call ControlLock(Me, False)
Else
MsgBox "This record is currently locked by another user!"
Call ControlLock(Me, True)
End If

End Sub

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Ad

Advertisements

D

david

Access caches data locally, in addition to whatever caching your
workstation and server are doing.

So after you set [IsLocked], and before you read [IsLocked],
you have to (at least) flush the Jet cache.


application.DBEngine.Idle dbRefreshCache

Or you can do the same thing using Jet transactions.

(david)

Dale Fye said:
For those in the Access.FormsCoding newsgroup, this is a repost from
yesterday, with additional info. For the rest of you, this is new.

I'm attempting to use SharePoint 2007 lists as linked tables in an Access
application (long story). Up until yesterday, this was working well, with
my being able to open forms and edit list data without any problem.
However,
my understanding is that Sharepoint doesn't do a very good job of handling
record locking, so I thought I might try to implement my own.

My concept was that I would lock each record, as it is displayed on my
Access form
(in the Current event), so that no other user can edit, the record, while
the user
that got to that record first has it displayed in his form. I do this by
setting the value of the IsLocked field to true in the Current event of
the
form, but only do it if that value is not already set. If it is already
set,
then I lock all of the data controls on the form. Then, when I move to
the
next record or close the form, I check to see whether I locked the
previous
record, and attempt to unlock it by setting the [IsLocked] value to False.
Unfortunately, I'm getting an error message (Runtime
error #3218: Could not update; currently locked) when I try to implement
the
Currentdb.execute line).

Subsequent to posting my first message to the FormsCoding newsgroup, I
imported the applicable Forms, tables (lists), queries and code into a
test
application (mdb), and imported the list data into an Access backend mdb.
I
then opened two instances of this test application (front end), and the
code
below worked properly, locking the form when another user is viewing the
same
record.

This points me to the conclusion that the linkage to the Sharepoint list
is
what is causing the error message. It only generates this message if I
locked a record (set [IsLocked] to true).

Does anyone have any idea why this code is not working with the SharePoint
list as a linked table?

Private Sub Form_Current()

Dim strSQL As String

'Refresh the current record to account for changes in the
'[IsLocked] field
Me.Refresh

'If the users previous record is locked, then unlock it
If lngID <> 0 Then
strSQL = "UPDATE tbl_POCs SET [IsLocked] = 0 " _
& "WHERE [ID] = " & lngID
CurrentDb.Execute strSQL, dbFailOnError
lngID = 0
End If

'If the current record is not locked, then lock it so
'no one else can edit it
If Not Me.IsLocked Then
lngID = Me.ID
Me.IsLocked = True
Me.Dirty = False
Call ControlLock(Me, False)
Else
MsgBox "This record is currently locked by another user!"
Call ControlLock(Me, True)
End If

End Sub

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top