My apologies: I asked this on StackOverflow already but have received no responses. I am hoping that reposting on a site more oriented to SharePoint might lead to more insight.
For reasons beyond my control we are using Access 2010 to update linked SharePoint lists to keep them synchronized to our CMDB (we are using SharePoint 2010 as well). We obtain reports from the CMDB in CSV format, and link them to Access as well. We then use a combination of Access VBA and predefined queryies to add new data, or update or soft delete existing data. One list in particular is causing problems. Specifically, inserts/soft deletes seem to work, but Access exhausts resources and crashes when running the update query. Pulling up the resource monitor shows that memory usage constantly increases as the application runs, and Access finally fails when ~ 1.6 GB or RAM has been allocated to it (on a 4 GB machine with a 6 GB swap file, Windows 7 64 bit, but 32 bit Access).
I use two queries in addition to the VBA code. One query retrieves a result set that allows me to determine which row in the SharePoint list is to be updated (if any), while the other identifies which columns from the report update corresponding columns in the SharePoint list, the join condition between the linked report and the corresponding list, and the row in SP to be updated, identified by by its composite key. I then iterate over the first result set, pausing .1 seconds between each row, and applying the appropriate parameters to the update query. Fairly standard stuff, I think.
We have to use this approach (or one substantially similar) due to the fact that the SharePoint list has associated workflows. We found that if we wrote our SQL to perform standard set-type updates, the updates occurred too quickly, overloading Sharepoint's workflow engine and causing the workflows to fail, thus the need for the .1 second delay within the VBA loop.
NOTE: I receive no hint of any issue or error up to the point where Access crashes, and SharePoint is being updated - again until Access crashes due to system resource exhaustion.
I've tried a number of alternate techniques:
- Using a recordset edit/update sequence rather than the query/exec shown below. That consumes memory even more quickly, and spikes the CPU to 26% vs. 12%.
- As shown in the VBA code below, I've tried closing and reopening the queries every
100 rows, as well as using transactions. Neither technique results in an improvement.
- I've tried disabling then re-enabling Access' SharePoint caching mechanism, with no success.
- I've tried using parameterized queries. This technique does not work as we must update a number of memo fields, and query parameters max out at 255 characters.
- Running a database compact/repair does not release allocated memory.
This is the VBA code to execute the queries:
Private Sub runUpdt() Dim oQdfUpdt As DAO.QueryDef Dim oRs As DAO.Recordset Dim oWrkSpc As DAO.Workspace Dim strmsg As String On Error GoTo Handler logMsg "Entering method runUpdt in class clsAppFsFin" Debug.Print "Entering method runUpdt in class clsAppFsFin", Now() Set oRs = CurrentDb.QueryDefs("slctAppFsFinRowsForUpdt").OpenRecordset(dbOpenDynaset, dbReadOnly) Set oQdfUpdt = CurrentDb.QueryDefs("updtAppFsFin") Set oWrkSpc = DBEngine.Workspaces(0) Do While (Not oRs.EOF) oWrkSpc.BeginTrans If (isUpdated(oRs)) Then oQdfUpdt.Parameters("CHGTXT") = "System Change" oQdfUpdt.Parameters("ID") = oRs.Fields("RYCID") oQdfUpdt.Execute ' inserts a row into the flg_is_updt table oFlgUpdt.insFlgIsUpdt oRs.Fields("RYAID") ElseIf (oRs.Fields("SPCTX") <> "System NoChange") Then oQdfUpdt.Parameters("CHGTXT") = "System NoChange" oQdfUpdt.Parameters("ID") = oRs.Fields("RYCID") oQdfUpdt.Execute ' inserts a row into the flg_is_updt table oFlgUpdt.insFlgIsUpdt oRs.Fields("RYAID") End If oWrkSpc.CommitTrans ' At 100-row intervals, print out a progress report, then close and re-open the recordset ' and reposition to the last row processed using FindFirst If (oRs.RecordCount Mod 100 = 0) Then strmsg = "Updated " & oRs.RecordCount & " rows. Class: clsAppFsFin, Method: runUpdt." Debug.Print strmsg, Now() logMsg strmsg Dim curFSCID As String curFSCID = oRs.Fields("RYCID") oRs.Close Set oRs = Nothing oQdfUpdt.Close Set oQdfUpdt = Nothing Set oRs = CurrentDb.QueryDefs("slctAppFsFinRowsForUpdt").OpenRecordset(dbOpenDynaset, dbReadOnly) Set oQdfUpdt = CurrentDb.QueryDefs("updtAppFsFin") oRs.FindFirst "RYCID = '" & curFSCID & "'" End If ' sleep .1 seconds to avoid overloading the upstream workflow Sleep SLEEPTIMEINMILLIS oRs.MoveNext Loop strmsg = "Final update count: " & oRs.RecordCount & " rows. Class: clsAppFsFin, Method: runUpdt." logMsg strmsg Debug.Print strmsg, Now() oRs.Close oQdfUpdt.Close Set oRs = Nothing Set oQdfUpdt = Nothing Debug.Print "Exiting method runUpdt in class clsAppFsFin", Now() logMsg "Exiting method runUpdt in class clsAppFsFin" Exit Sub Handler: oWrkSpc.Rollback Debug.Print Err.Number, Err.Description logError Err.Number, Err.Description End Sub
Here are the select and update queries executed by the VBA code
SELECT APFF.[App ID] AS SPAID, APFF.Server AS SPHST, APFF.Directory AS SPDIR, RAppAH.AppID AS RYAID, RAppAH.Host AS RYHST, RAppAH.ID AS RYCID < snip > FROM (AppCert INNER JOIN AppFileSystemFin AS APFF ON AppCert.[App ID] = APFF.[App ID]) LEFT JOIN RAppAH ON APFF.FSCID = RAppAH.FSCID WHERE APFF.FSCID = [RAppAH].[FSCID] AND AppCert.State = "8 - Complete" AND RAppAH.FSCID IS NOT NULL AND APFF.[Change In SoR - Text] <> "System Remove" ORDER BY APFF.ID;
UPDATE AppFileSystemFin INNER JOIN RAppAH ON AppFileSystemFin.ID = RAppAH.ID SET AppFileSystemFin.Server = [RAppAH].[Host], AppFileSystemFin.Directory = [RAppAH].[Directory], < snip > WHERE AppFileSystemFin.ID = [ID];