Access Services Unexpected error occurred while running macro

Question

I have a Before Change macro that will execute during insert - and it works when I execute it from the Access Client.  However, when I insert a record from the SharePoint Web I get the error of "Access Services Unexpected error occurred while running macro." (This is the follow up to Capturing Record ID during BeforeChange Data Macro?)

CalcTrackNumber is a query over the table that I'm inserting into to retrieve the last number used.  (It doesn't appear that the DMax recommended is available for Web Databases, so I had to get creative.)

I'm I getting this error because of the query or is there a configuration problem in Access Services?

Thank you for any help!

XML of the macro:

<?xml version="1.0" encoding="UTF-16" standalone="no"?>

<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"&gt;&lt;DataMacro Event="BeforeChange"><Statements><ConditionalBlock><If><Condition>[IsInsert]</Condition><Statements><LookUpRecord><Data><Reference>CalcTrackNumber</Reference><WhereCondition>[CalcTrackNumber].[calcTrackNbr] Is Not Null</WhereCondition></Data><Statements><Action Name="SetLocalVar"><Argument Name="Name">varNewMax</Argument><Argument Name="Value">[CalcTrackNumber].[calcTrackNbr]+1</Argument></Action></Statements></LookUpRecord><LookUpRecord><Data><Reference>DocumentConnections</Reference><WhereCondition>[DocumentConnections].[ID]>=1</WhereCondition></Data><Statements><Action Name="SetLocalVar"><Argument Name="Name">varConPath</Argument><Argument Name="Value">[DocumentConnections].[ConPath]</Argument></Action></Statements></LookUpRecord><Action Name="SetField"><Argument Name="Field">calcTrackNbr</Argument><Argument Name="Value">[varNewMax]</Argument></Action><Action Name="SetLocalVar"><Argument Name="Name">varTextTrackNbr</Argument><Argument Name="Value">Mid(Str([calcTrackNbr]),1,3)</Argument></Action><Action Name="SetLocalVar"><Argument Name="Name">varYear</Argument><Argument Name="Value">Mid(Str(Year([DateTime])),3,2)</Argument></Action><Action Name="SetField"><Argument Name="Field">TrackNbr</Argument><Argument Name="Value">[varYear] & "-" & [varTextTrackNbr]</Argument></Action><Action Name="SetField"><Argument Name="Field">SupportingDocs</Argument><Argument Name="Value">[TrackNbr] & "#" & [varConPath] & [TrackNbr] & "#"</Argument></Action></Statements></If></ConditionalBlock></Statements></DataMacro></DataMacros>

ULS logs:

Name=Request (POST:https://sharepoint:443/sites/XYZ/SAS/ScRpTrk/_vti_bin/accsrv/accessportal.json/InsertRecords)

AccSrv UserName=0#.w|domain\username

UserSessionId=1993649228.44.-1367820641.1653311402

Site=/sites/XYZ

AccSrv Application URL=https://sharepoint/sites/XYZ/SAS/ScRpTrk

AccSrv UserName=0#.w|domain\username

AccSrv Application URL=https://sharepoint/sites/XYZ/SAS/ScRpTrk

AccSrv SessionID=36.43ba244e-bc24-4264-a91a-ef5ec5d2ec17162.1.V21.4tDUHCSEcWlXSLD5WpM5590.5.en-US5.en-US73.+0300#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.00000000-0000-0000-0000-0000000000001.U

MossHost.TryCreateHostExtension: Host extension Microsoft.Office.Excel.Server.HostExtensionWac.HostExtensionWac not present

MossHost.LoadHostExtensions: Failed to create host extension HostExtensionWac

Leaving Monitored Scope (EnsureListItemsData). Execution Time=9.98143618811888

AccessPortal.InsertRecordsInternal: new records count: '1'.

Session=1.V21.4tDUHCSEcWlXSLD5WpM5590.5.en-US5.en-US73.+0300#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.00000000-0000-0000-0000-0000000000001.U

User=NT AUTHORITY\IUSR

ServerSession.ExecuteWebMethodCore: sending request of type PartnerMethod, to server=http://APPLICATIONSERVER:32843/e92e758101444a69a5861684c2a79359/AccessService.svc, session=1.V21.4tDUHCSEcWlXSLD5WpM5590.5.en-US5.en-US73.+0300#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.00000000-0000-0000-0000-0000000000001.U, state id=-1, health score=0, error delta=0

AccessServicesHost.GetEndpointAddress: Server endpoint Uri: http://APPLICATIONSERVER:32843/e92e758101444a69a5861684c2a79359/AccessService.svc.

WcfSendRequest: RemoteAddress: 'http://APPLICATIONSERVER:32843/e92e758101444a69a5861684c2a79359/AccessService.svc' Channel: 'Microsoft.Office.Access.Server.Application.IAccessServiceSoap' Action: 'http://schemas.microsoft.com/office/Access/Server/WebServices/AccessServerInternalService/AccessServiceSoap/InsertData' MessageId: 'urn:uuid:addcc134-db47-4e75-bf3d-97a3d042ca4e'

Leaving Monitored Scope (ExecuteWcfOperation:http://schemas.microsoft.com/office/Access/Server/WebServices/AccessServerInternalService/AccessServiceSoap/InsertData). Execution Time=232.098492964888

Result=Microsoft.Office.Access.Server.Application.AccessServerSessionException: Unexpected error occurred while running macro.     at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession.ExecuteWebMethodCore(WebMethodType webMethodType, WebMethodBehaviorAttribute webMethodBehavior, CommandParameter parameter, CoreWebMethod coreWebMethod)     at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession.ExecuteWebMethod(WebMethodType webMethodType, WebMethodBehaviorAttribute webMethodBehavior, CommandParameter parameter, CoreWebMethod coreWebMethod)

AccessPortal.ExecuteWebMethodHandler: Access Server exception: Microsoft.Office.Access.Server.Application.AccessServerSessionException: Unexpected error occurred while running macro.     at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession.ExecuteWebMethodCore(WebMethodType webMethodType, WebMethodBehaviorAttribute webMethodBehavior, CommandParameter parameter, CoreWebMethod coreWebMethod)     at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession.ExecuteWebMethod(WebMethodType webMethodType, WebMethodBehaviorAttribute webMethodBehavior, CommandParameter parameter, CoreWebMethod coreWebMethod)     at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession.ExecuteWebMethod(WebMethodType webMethodType, CommandParameter parameter, CoreWebMethod coreWebMethod)     at Microsoft.Office.Access.Server.Application.AccessServerSession.InsertData(String moniker, String listName, IDictionary values, Int32& recordsInserted, String& macroInstanceId)     at Microsoft.Office.Access.Server.Data.AccessServicesDataSourceView.ExecuteInsert(IDictionary values)     at Microsoft.Office.Access.Server.Data.AccessServicesDataSourceView.ExecuteInsertInternal(IDictionary values)     at Microsoft.Office.Access.Server.UI.AccessPortal.InsertRecordsInternal(SharedDataBaseInfo dataBaseInfo, UpdateRecord updateRecord)     at Microsoft.Office.Access.Server.UI.AccessPortal.<InsertRecords>b__6(SharedDataBaseInfo database, Object param)     at Microsoft.Office.Access.Server.UI.AccessPortal.ExecuteWebMethodHandler(CoreWebMethodHandler coreWebMethodHandler, SharedDataBaseInfo dataBaseInfo, Object param, Boolean writeOperation)

Leaving Monitored Scope (Request (POST:https://sharepoint:443/sites/XYZ/SAS/ScRpTrk/_vti_bin/accsrv/accessportal.json/InsertRecords)). Execution Time=288.459287423402

Answer 1

After a support call with Microsoft, it turns out Access Macros are converted into Workflows when run by the Access Database Service. When the server running on Access Database Services does not have the Microsoft SharePoint Foundation Web Application service, the Access Database Services is unable to retrieve the Workflow configuration settings from the Web Application's Web.config file.  Our current farm configuration is two Web Front Ends and two back ends, one for search and one for services.

There were a few options for the solution.  On was to enable the Microsoft SharePoint Foundation Web Application service on the server running Access Database Services.

Another was to copy the web.config setting regarding workflows to the Web Application settings by running the below PowerShell Command-lets:

$WebApp = get-spWebApplication <URL>

$WebApp.UpdateWorkflowConfigurationSettings

Because we have licensing for third-party tools that count the number of Web Front Ends, we decided to start the Access Database Services on our WFE servers and stop them on the back-end services server.

Answer 2

hi,

I have run the powershell command.

But the error still persists.

powershell window output is:

PS C:\Users\xxxx> $WebApp = get-spWebApplication "web application url"

PS C:\Users\xxxxx> $WebApp.UpdateWorkflowConfigurationSettings

MemberType          : Method

OverloadDefinitions : {System.Void UpdateWorkflowConfigurationSettings()}

TypeNameOfValue     : System.Management.Automation.PSMethod

Value               : System.Void UpdateWorkflowConfigurationSettings()

Name                : UpdateWorkflowConfigurationSettings

IsInstance          : True

Answer 3

Hi,

See the above ULS logs, you want to insert a record from SharePoint Web using WCF service. Based on my understanding, the error was throwed during execute the web method. On the server in order to send the exception information back to the client, you'd better set the IncludeExceptionDetailInFaults to true under the <serviceDebug> tag in web.config. you can also enable WCF tracing on the server side.

1.enable WCF tracing.

http://blogs.msdn.com/b/madhuponduru/archive/2006/05/18/601458.aspx

Here is a article related to SharePoint 2010 service applications which you can refer to.

http://blogs.msdn.com/b/sowmyancs/archive/2010/07/16/sharepoint-2010-service-applications-bcs-metadata-access-service-are-not-working.aspx

I think you'd better post your question on the following forum to get more helps.

2.Windows Communication Foundation (WCF)

http://social.msdn.microsoft.com/Forums/en-US/wcf/

Best regards