After applying SP1 to a SharePoint 2010 farm we found we were unable to run any macros in an Access Services site, it gave a –4002 error. We had seen this error in the past, but the solutions that worked then did not help. As this site was critical, as a workaround, we moved the site to a non-patched SP2010 instance. This was done via a quick site collection backup and restore process. This allowed us to dig into the problem at our leisure.
Eventually we fixed the problem by deleting and recreating the Access Services application within SharePoint on the patched farm. We assume some property was changed/corrupted/deleted in the application of the service pack.
So we now had a working patched farm, but also a duplicate of Access Services site with changed data. We could not just backup and restore this as other sites in the collection had also changed. Turns out getting this data back onto the production farm took a bit of work, more than we expected. This is the process we used
- Open the Access Services site in a browser on the duplicate server
- Select the open in Access option, we used Access 2010, which it had originally been created in
- When Access had opened the site, use the ‘save as’ option to save a local copy of the DB. We now had a disconnected local copy on a PC. We thought we could just re-publish this, how wrong we were.
- We ran the web compatibility checker expecting no errors, but it reported a couple of them. In one form and one query extra column references had been added that referenced the auto created SharePoint library columns (date and id stamps basically) These had to be deleted by hand.
- We then could publish back to the production server
- We watched as the structure and data was publish
- Then it errored. On checking the log we saw that it claimed a lookup reference had invalid data (though we could not see offending rows and it was working OK). Luckily the table in question contained temporary data we could just delete, so we tried to publish again
- Then it errored .On checking the logs again we saw it reported it could not copy to http://127.0.0.1 – No idea why it looking for localhost! Interestingly if we tried to publish back to another site URL on the non-patched server it work! Very strange
- On a whim I repeated this whole process but using Access 2013 RC, and strangely it worked
So I now had my Access Services site re-published and fully working on a patched farm. That was all a bit too complex for my tastes
We have had an internal timesheeting system written in Access services running without any problems for the past through months. At the end of last week, when people tried to submit their timesheets they started getting a -4002 error saying the macro (that saves the weekly sheet) could not be started.
Checking the server event logs, Sharepoint logs and Access services log tables showed nothing. So as all good IT staff do we tried the traditional IISRESET command (on both our Sharepoint web servers) and it all leapt back into life. The only change on our server in the past week has been been the ASP.NET security fix, and associated reboot, but I cannot see why this should effect Access Services, it looked as if it had basically Access services just failed to restart fully after the server reboot.
One to keep an eye on.
Access MVP Ben Clothier has posted an article and video on Going beyond Web Macros: Using Event Receivers & .NET with Access Services. This takes some of the techniques I was proposing for .NET/Access integration and added to them using Ben’s extensive experience in the Access space.
Well worth a look if you want to make Access a RAD front end to legacy systems.
When you drop a combo box on a Access 2010 web form and databind to a query or table with a value column and display column (e.g. the query select id, description from table1) you don’t get what you would expect. It shows just the value e.g. a set of integers in the combo.
This is not the case if you are using a standard Access client form. the wizard that gets run sort it all out for you, and if it does not, you just set the ‘Bound Column’ property to sort it out.
On web forms the fix is simple, but not obvious.
- Databind the combo as you normal do to the query/table.
- Go to the combo’s properties format tab
- Set the column count to 2
- Set the column widths to 0cm;2cm (basically hide the first column and show the second)
Once this is done it work fine