One challenge in Access Services is ensuring that the logic behind various objects will be portable between Access itself and the SharePoint site hosting the Access web database. To that end, the Access team has used macros to support the goal of portability. In practical terms, this means we cannot use VBA behind the Access objects in context of a web browser.
This in turn posted a new challenge for all of us as Access developers: what if the web macros cannot perform a required functionality for our client?
I was delighted to learn from Richard Fennell, a Visual Studio Team System MVP who has written about his experiences with Access 2010, that we can use SharePoint object model and Visual Studio 2010 to introduce business logic to the applications. With access to the .NET framework and a full-bodied language, web databases need not be limited by the macros. I went about creating a proof of concept to demonstrate how we can use SharePoint’s events to provide extended functionality for our new Access web databases.
To that end, Iâ€™ve also created a video showing a case study of the solution applied included with this blog post. The video is about 10 minutes long and can be downloaded here: AccessServicesEventReceiver.mov (64 MB).
Visual Studio 2010 and SharePoint Templates
One big enhancement featured in Visual Studio 2010 was how much easier it is now to work with SharePoint object model – there are many templates ready made for different applications within SharePoint including but not limited to web parts, workflows, and event receivers. As shown in the video, Visual Studio 2010 now supports automating of what used to be a tedious and manual process of packaging the solution, adding it to a feature then activating the feature among other things. The setup effort is relatively simple â€“ use GuidGen.exe (a part of Visual Studio tools) to create a new GUID to uniquely identify the assembly then identify what lists and events you want to customize, enabling you to focus bulk of effort toward coding the custom business logic. Once done, one can then choose “Deploy the Solution” and Visual Studio will automatically perform all tasks required to deploy the updates into the SharePoint and thus make that solution usable by all other parts that consumes this solution.
Of particular interest here is a template known as “Event Receiver”. In a normal SharePoint setting, you would select a site then choose a list to associate the event receiver with, then choose which events you want to receive and start coding away. Though an event receiver can only be associated with single list, it may support any events built-in to the list and for case where more than list requires custom functionality, we can add as many event receivers as many list we need to associate with into the same project.
When we publish an Access web database to SharePoint, the web tables get transformed into SharePoint lists, enabling us to use SharePoint object model & Visual Studio to customize the operations. Mr. Richard Fennell was gracious enough to share his code required to associate the events with a SharePoint list, which is done manually rather than depending on a wizard which will not work for this application. Using his code, we can then choose any Access Services’ list(s) to associate various events with.
Attaching events to the list
In this demo, we want to be able to read data from a MySQL database into Access web database, and we need to be able to do so whether we’re using a web browser or a rich client. In rich client, the solution is simple: we link the table. Done. In web browser, we are limited to only lists within the published application and web macros does not allow running code or linking to external data (Note: the absence of linked tables in a web database is something Access team has reassured that they will address in a future version). Thus, we attach a receiver for two events; ItemAdding and ItemAdded. The event receiver is then associated with a list, “Action”. On the first form, the HR staff is presented with a filter form where the Human Resources (HR) staff enters the filter criteria to determine what subset of HR data should be retrieved from MySQL. When the “Load” button is pushed, this saves the new filter criteria into the Action list, thus triggering the ItemAdding event.
When the C# code within the event is triggered, We can then examine what was just added, open a connection to MySQL database and feed the filters into MySQL’s stored procedures. We can then use DataReader class and in this event, read only one Human Resources record. This then completes the ItemAdding event. By design, all â€œ-ingâ€ events in SharePoint object model run synchronously so the user has to wait for it to complete.
When ItemAdded is fired, it will be running in its own thread asynchronously separate from the thread driving the application, whether running in Access client or inside a web browser. Picking up the DataReader from ItemAdding, it will then continue to add records. In practical terms, this means the user only has to wait for single record to be loaded before the Access Services’ own macros will resume after the SaveRecord command, invoking the subsequent BrowseTo macro to open the form bound to the HR records. By the time the form has loaded in the user’s browser, at least one records has been fetched from MySQL and inserted into the Access Services’ lists, ready for the user’s browsing & editing. As the user reviews the HR records, the ItemAdded event continues to run and the user can click on the “Get More Records” to reload the form with new records added by the ItemAdded event.
The best part of this all is that this requires no explicit code to manage threading – this is already handled by the SharePoint, freeing the developer to focus on the business logic within the events. With Visual Studio 2010’s enhancements for SharePoint, developing a SharePoint solution targeted toward Access web applications is quite very feasible and a viable choice for developers who requires more customizations and power beyond what the built-in web macros will allows.
Communication Across the Boundary
One challenge that came up was figuring out a way to communicate the progress and the result of the .NET code execution back to the application. As noted earlier, the web application cannot interact with the outside world and .NET code is outside. The solution to that dilemma was to make use of Action list to store information so the code can update it as it progresses through the steps. Here’s a glimpse at the Action list layout:
the video will show that as the .NET code steps through a complete personnel record (note that an employee record has three child relationships, so all related records from those three child tables must be fetched to consider it ‘complete’), it increments the ‘fetched’ and update the Action list before it proceeds to the next personnel record. Side note: for performance reasons, it may be better to update the Action list in batches rather than for each record so that should be considered when moving the code to production.
Within the web form’s footer, it references back to the Action list and report different status depending on which part of code is being executed. While code is running asynchronously, the footer will display this:
The user is able to press the button to refresh the web form and thus see the latest records fetched by the code since last reload. This basically function as a static progress bar.
Once the code has completed collecting all matching records, it updates the ‘matched’ column with the final number that also serves as a sanity check that all fetched records were in fact fetched. The footer will then look like this:
This provides the user with very useful feedback on how things are progressing as the user reviews the employee’s history. Also, all of this is accomplished using the web macros & examining the Action list’s row.
Access Client & Offline mode
Because the event receiver is associated with the SharePoint List, the code will run regardless whether we access the application via a web browser or Access client (or any other client for that matter). However, Access client has the capability to work in an offline mode. When Access client is working in offline code, this may mean the code attached to the event receiver will not execute until the next synchronization. In this demo, this means offline mode would not function for the HR users because they’d be then taken to the employees review form that’s blank because the code hasn’t fired and won’t process until the lists are brought back online Therefore, we must plan for the events where the process depends on code being triggered or not and handling the case when offline mode is enabled.. perhaps something simple as alerting the users that this operation is not available in offline mode, for instance.
Ramifications on Concurrency
One issue we need to consider carefully is how the code will function in multi-user settings. SharePoint’s object model is well-designed for multi-users settings, but it remains the developer’s responsibility to verify that code will run in such fashion that the changes made to other lists does not interfere with other users’ workflow, at least in an undesirable manner. It should be also understood that the changes the code within the Event Receiver makes to any other SharePoint lists is effectively viewable by all other users, which may be desirable or not.
In order to keep things simple, I chose to use a dedicated table solely to receive filter criteria and use only Insert event, which frees me from the need to worry about update conflicts when two users modify two rows at near same time. Another possible approach is to dedicate a row to the user and allow that user to update only their row. Furthermore because the aim of this demo was to provide each user a “workspace” with their own filter, it is necessary to filter which data belong to what user and how to handle the case where a user requests an employee record already “checked out” by another user. Therefore, if this were to go to production, it would be necessary for me to implement some kind of audit trail upon the employees table to identify which row was called by which action_id and thus effectively associating an employee record with the user’s requested criteria represented as a row in the Action list. In different applications, it may not be necessary but the onus still remains- the developer must verify that the code will work well in multi-user settings.
With great power comes great responsibility. There is a reason for loading only one record in the ItemAdding event, and it’s the same rationale Access team used for limiting web macros to precious few actions within the Before Change event of the data macros: ItemAdding is synchronous and excessively long operations can cause timeouts among other problems. This is especially true in context of browsers working in a disconnected state – if it does not get a response from the server, it may time out and users may have to re-perform the actions. Not a pleasant state of affair to be in! There is also another reason for narrowing the scope of web macros: SharePoint server is a shared resource and a solution that requires intensive processing will hurt SharePoint’s performance,harming all users’ experience. Though SharePoint team has provided many features such as sandboxing and throttles to handle such scenarios, you do not want to be sitting across the table with the system administrator, furtively explaining why you did what you did!
As will be shown in the video, Visual Studio makes it possible to debug and step through the code by attaching the solution to an IIS worker process (w3wp.exe) and you can observe the code in action. However, if breakpoints are set and you step through code, this may cause time out and Access Services will then disconnect the tables in the client and assume that the recent save has failed. Once the code has been stepped through and Access client has been directed to reconnect the tables, it will ask you to re-attempt previous save, but that is actually redundant because save did succeed; just not within the timeout length.
Another anomaly I’ve observed is when stepping through ItemAdded event (which should run asynchronously) the web browser will continue to wait for the ItemAdded to complete should a breakpoint be raised within ItemAdded. However, it does run asynchronous when breakpoints are not enabled or set within the ItemAdded.
With all this in mind, should our client ever ask us this question, “Can Access Services do this?” we need not confine ourselves to only web macros. For vast majority of solutions out there, web macros will do the job quite admirably. For that last ten percent of the solution, on the other hand, we can tell clients that we can turn to .NET to take us all way home.
Although the demo provides a way to “link” into external data source, which has been indicated that this will be made a standard feature in future version, this isn’t the only possible use. Here are few other possible uses to help spur the imagination:
a) Using SQL Server Reporting Services to generate a complex report and email it to the users asynchronously. They only need to fill out a web form then few minutes later, it’ll be in their mailbox.
b) Provide hooks into existing SharePoint workflows so Access web applications then participate in a longer and complex workflow.
c) Communicating with other web services.
d) ________ (left blank for you to fill in)