MS Access didn't update XX Records due to lock violations

[accvbalearner]

Hello All,

Hope someone has had a similar issue to the one that I am having.

Using: SQL, VBA in Access, Sharepoint List

What I'm trying to do: Update a record in a SQL table with updated data from a Sharepoint List using a button in a form in MS Access.

What is happening: When I run the Update Query I get "Microsoft Access didn't update XX record(s) due to lock violations" message. But it only effects SOME of the records, most of the changes from Sharepoint are updated, but some of the records aren't updated. I cannot find a common denominator on the records, they're being entered by multiple users in Sharepoint (over 20 users).

My Workaround: Delete the records from the SQL Table and Append them from the Sharepoint Source. After I do that, IF one of the records that was locked before is changed in Sharepoint when I re-run the update query, the records update just fine.

I have read several different articles that mention adding Me.Dirty = False to the VBA code so that the subform is cleared when the update runs, but I am not using a subform. I have a form with a button on it that runs the update query when the button is clicked.

I have attempted to add Me.Dirty = False to the code immediately before the DoCmd.OpenQuery command, but still get the error regarding the locked records.

Because I can append records from Sharepoint to SQL AND Update changes from Sharepoint to SQL in most cases, I don't believe that this has anything to do with Sharepoint, I think that the records are locked either in the Access database or in the SQL table itself.

Any ideas on what I'm doing wrong? Or what I need to change to fix it?

Here is the Update Query I am using in Access to update the SQL Table from the Sharepoint List:

UPDATE sel_EstLogUdData
INNER JOIN sp_EstLog ON sel_EstLogUdData.ELID = sp_EstLog.ELogID
SET sp_EstLog.EstNBr = [sel_EstLogUdData].[EstNbr], sp_EstLog.SvcTyp = [sel_EstLogUdData].[ScopeTypeDesc], sp_EstLog.Estimator = [sel_EstLogUdData].[Estimator], sp_EstLog.DeptLead = [sel_EstLogUdData].[DeptLead], sp_EstLog.SrvTypCode = [sel_EstLogUdData].[SUCode], sp_EstLog.RecdDate = [sel_EstLogUdData].[Rec'd Date], sp_EstLog.ReqBy = [sel_EstLogUdData].[ReqBy], sp_EstLog.Company = [sel_EstLogUdData].[Company], sp_EstLog.ProjName = [sel_EstLogUdData].[ProjName], sp_EstLog.RefNbr = [sel_EstLogUdData].[RefNbr], sp_EstLog.BDDate = [sel_EstLogUdData].[Bid Due Date], sp_EstLog.SubMeth = [sel_EstLogUdData].[SubMethod], sp_EstLog.SbDate = [sel_EstLogUdData].[SubDate], sp_EstLog.BAxn = [sel_EstLogUdData].[BidAction], sp_EstLog.BidStat = [sel_EstLogUdData].[BidStatus], sp_EstLog.JobStat = [sel_EstLogUdData].[JobStatus], sp_EstLog.BidVal = [sel_EstLogUdData].[BidVal], sp_EstLog.ModDate = [sel_EstLogUdData].[Modified], sp_EstLog.EstFldr = [sel_EstLogUdData].[BidFolder], sp_EstLog.PJName = [sel_EstLogUdData].[PJName], sp_EstLog.FldrPth = [sel_EstLogUdData].[FolderPath];

Here is the VBA Code that runs when I hit the button:

Private Sub app_spEstLog_Click()
On Error GoTo Err_btn_app_spEstLog_Click

Dim stDocName As String
Dim stDocName2 As String
Dim stLinkCriteria As String

'stDocName & stDocName2 are the names of the object that you want to open

stDocName = "XX_XXXXXX"
DoCmd.OpenQuery stDocName
stDocName2 = "XX_XXXXXX"
DoCmd.OpenQuery stDocName2

Exit_btn_app_spEstLog_Click:
Exit Sub

Err_btn_app_spEstLog_Click:
MsgBox Err.Description
Resume Exit_btn_app_spEstLog_Click
End Sub

Thanks in advance for any help you can provide!

 

[Uncle Gizmo]

Looks like your question has been missed by the illustrious members here. It might help if you were to take the time and trouble to format the code with one of the formatting options so that it's easier to read. People will respond to a thread they can read easily. I'm bumping it up the list so it gets an extra look...

 

[CJ_London]

Also suggest use table aliases - makes the sql code shorter and easier to read

the locks will be on the sel_EstLogUdData table since that is the one you want to update. Implication is a user has selected a record. Even if the form/table/query is just opened, they will have set the focus on the first record

Depends on what your users are doing are doing with locking - for each user see File>Options>Client Settings and check the default record locking values - default is no locks and 'open databases by using record level locking'.

Not sure of the relevance of the sql you provided - is that one of the two queries you run? and why run 2 queries?

 

[ebs17]

Query and calling code don't cause the problems.

It looks like records to be updated are locking each other. I read about one such case where fields of data type Text were updated from short content to long content. Data is stored in memory blocks and memory pages (I'll say it in layman's terms, I'm not deep into the subject). As a result of the case mentioned above, such memory areas of adjacent data records collided and consequently led to locks.

 

[dietrichvida]

The issues are not brought on by the query or calling code.

Records that need to be updated appear to be locking one another. I read about one such instance where long content was updated into short content fields of the data type Text. In memory blocks and memory pages, data is kept (I'll put it simply because I don't know much about it). Such memory regions of adjacent data records collided in the aforementioned case, resulting in locks.