Very Odd behavoir - Running Functions manually vs scheduled when functions use SharePoint Lists

[SIMMS7400]

Hi Folks -

I'm seeing some very odd behavior in MS Access and it seems to be stemming from interacting with 0365 SharePoint Lists. Allow me to explain.

I have a Function that updates an 0365 SharePoint List (using a linked file) and it runs perfectly fine when executing the Function manually (through VBA). However, I have a batch file scheduled that opens the MS Access DB and executes a macro which calls this Function and it just freezes when it hits the Function. All other Functions that do not interact with 0365 SharePoint run fine when scheduled.

Here is the Function:

Public Function BPRi_Check_Activity_Alias()

'::-- Error Handler --::'
On Error GoTo Proc_Err
    
    '::-- Initialize --::'
    If strActivate_Flag = 0 Then Call Activate_Modules
    
    Dim db As DAO.Database
    Dim ws As DAO.Workspace
        
    Dim rs As DAO.Recordset
    
    Dim strQuery As String, strDelim As String
    Dim strFunctName As String, strStartTime As Date, strEndTime As Date, strTimeDiff As String
    Dim strStep As String, strSubject As String, strBody As String, strTo As String, strProcError As String
    
    'Ensure file is delete before new file is exported
    Dim outputFileNameFull As String
    Dim strExportBin As String: strExportBin = str_LocalExport_Bin
    Dim outputFileName As String: outputFileName = "Updated_Internal_Order_Descriptions_" & Format(Date, "yyyyMMdd") & ".csv"
    Dim strMask As String: strMask = Len(outputFileName) - 12: strMask = Left(outputFileName, strMask)
    
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.Databases(0)
        
    strFunctName = "Check_Activity_Alias": strStartTime = Format(Now, "mm/dd/yyyy hh:mm:ss")
    strQuery = "qry_Check_Activity_Alias"

    strStep = "Step 1 : Open Query " & strQuery
    
    Set rs = db.OpenRecordset(strQuery, dbOpenDynaset)
    Exit Function
    'Ensure records are available for processing
    If rs.RecordCount > 0 Then
      
        strStep = "Step 2 : Purge Files in bin " & strExportBin
        Call DeleteExportFile(strExportBin, strMask)
       
        outputFileNameFull = strExportBin & outputFileName
        strDelim = ","

        strStep = "Step 3 : Export File"
        Call ExportToTextFile(strQuery, outputFileNameFull, strDelim, True, True)

        strStep = "Step 4 : Email file"
        strSubject = "ATTENTION : Updated Internal Order Descriptions"
        strBody = "Hi Financial Systems -" & vbNewLine & vbNewLine & _
                     "Attached are existing Internal Orders with updated descriptions." & vbNewLine & vbNewLine & _
                     "Please reach out to person if you have any questions or concerns." & vbNewLine & vbNewLine & _
                     "Thank you," & vbNewLine & _
                     "R&D Finance"
        strTo = email.com"
        strCC = strMDMSupportEmail
        strAttach = outputFileNameFull
       
        Call Email_Utility(strSubject, strBody, strTo, strCC, strAttach)
            
    End If
        
Proc_Exit:

    '::-- Update Table with Procedure Information --::'
    strEndTime = Format(Now, "mm/dd/yyyy hh:mm:ss")
    strTimeDiff = strEndTime - strStartTime
    Call ADD_RUN_TIMES( _
                        strFunctName, _
                        strStartTime, _
                        strEndTime, _
                        Hour(strTimeDiff) & " hours " & Minute(strTimeDiff) & " minutes " & Second(strTimeDiff) & " seconds", _
                        Switch(strProcError = "", "Success", Not (strProcError = ""), "Failed"), _
                        strProcError _
                       )
    
    If Not rs Is Nothing Then rs.Close
    If Not ws Is Nothing Then ws.Close
    If Not db Is Nothing Then db.Close
    
    Set rs = Nothing
    Set ws = Nothing
    Set db = Nothing
    
    Exit Function

Proc_Err:

    '::-- Rollback Transaction --::'
    If strTFlag = 1 Then ws.Rollback
    
    '::-- Capture VB Error --::'
    strProcError = Err.Description
    
    strSubject = "WARNING : Function '" & strFunctName & "' Failed " & strEnvType
    strBody = Switch(strStep = "", "", Not (strStep = ""), strStep & vbNewLine & vbNewLine) & _
              "VB Error : " & strProcError & vbNewLine & vbNewLine & _
              "Profile : " & CurrentUser() & vbNewLine & _
              "VB Module : " & Application.VBE.ActiveCodePane.CodeModule.Name
    strTo = strMDMSupportEmail
    Call MDM_Routines.Email_Utility(strSubject, strBody, strTo, "", "")
    
    Resume Proc_Exit
    
End Function

To confirm 0365 is the issue, I commented out the line which sets the strQuery variable (strQuery = "qry_Check_Activity_Alias") and then the Function will run but obviously fail since that variable isn't defined, but just proves it's the interaction with SharePoint (as that query leverages a SharePoint list).



MS Access is using the "Admin" profile when manually and I assume running as something else when scheduled? It's scheduled using a Windows Server Service account and "Run with highest priv's" selected. I tried all different combinations of Task users with no resolution.

Why would this be?

[SIMMS7400]

Quick update : it works if I chose "Run only when user is logged on", whether I have "Run with highest privileges" checked off or not.

Very odd, why would it work with that option?

[Micron]

Who's logged in when the batch file runs? Maybe that entity doesn't have permissions on the SP site. Where I worked, they had a dedicated pc for running updates via Windows Task Scheduler. That pc had to be treated like any other user in terms of giving permissions to directories and applications in a NAS environment.

[SIMMS7400]

Who's logged in when the batch file runs? Maybe that entity doesn't have permissions on the SP site. Where I worked, they had a dedicated pc for running updates via Windows Task Scheduler. That pc had to be treated like any other user in terms of giving permissions to directories and applications in a NAS environment.

By logged do you mean the server or the 0365 SharePoint instance? If the server, thats where I keep scratching my head.

The user I'm using to log into the server is not a user in the 0365 tenant. I wonder when using the Run only when use is logged in is using the user that I connected the MS Access database to 0365 with?

[Micron]

I meant who's logged in on the pc? Windows passes user tokens to applications to authenticate the user (or at least it used to). The only point I had to make on this is whatever/whomever is accessing the SP file through the db and via the batch must have the required permissions all the way from the batch file to SP, and that includes running code from Trusted Locations. My experience with running tasks after hours is limited to using Windows Task Scheduler so I don't have any definitive answers to your questions about Windows Server Service, sorry to say.