Updating a SharePoint list via an Access query and VBA code exhausts system resources

Question

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 query:

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 query:

UPDATE     AppFileSystemFin
INNER JOIN RAppAH
ON         AppFileSystemFin.ID        = RAppAH.ID
SET        AppFileSystemFin.Server    = [RAppAH].[Host],
           AppFileSystemFin.Directory = [RAppAH].[Directory],
           <
            snip
           >               
WHERE      AppFileSystemFin.ID        = [ID];

Answer 1

The issue is now resolved. In the update query shown above:

WHERE AppFileSystemFin.ID = [ID];

This line does not refer to SharePoint's system-generated ID column. Instead, it refers to an internally generated key field that we had to use in order to be able to perform SQL join operations between lists.

The query has been updated to use SharePoint's generated ID column instead. This minor update resolves the memory allocation issue and in turn, allows updates to proceed more quickly - now requiring only about a third of the previous runtime to complete execution.