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:
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?
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:
Code:
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?