Note: This is the third part of a three part series, you can find part one here and part two here.

Authored by Ben Clothier

To continue the series on Access and SharePoint integration (part 1 and part 2), we’ll be looking at the latest kid on the SharePoint’s block; Access Services. Access Services is included with SharePoint Server 2010 and there are hosting solutions available, including AccessHosting.com and/or Office365 for those who do not want or need or cannot have a local SharePoint installation.

Access Services: Bringing your Access database to the web

You are undoubtably aware that with Access Services, you can publish web databases. For this article, we’ll be looking at how it helps us solve our business problems. A web database is practically a different beast from a regular Access database (referred to in Microsoft literature as “client database”), and there are several considerations we have to give as to whether we actually need web database. So we’ll be examining the business needs to help us decide an optimal strategy for brining our Access applications forward.

First question: Do you need it in web browser or just the ability to use your application across large distances?

Access MVP Albert Kallal asks the excellent question and gives some rationale behind whether to choose a web database or the traditional solutions for enabling Access databases to be used across WAN. It’s important to consider that we’ve always had solutions for enabling distributed access using Access, chiefly using either one of two methods: 1) Using a server-based RDBMS or SharePoint as the backend, 2) Using Terminal Server/Citrix or similar technology. I should note there’s also the Jet replication but since it’s not under active development, this is not something we may want to use in new projects anyway. Nonetheless, either methods enable you to keep your investment in your Access application while enabling it to be used across WANs and both can be cheaper (both in terms of money and time) to implement compared to web-enabling your database. Also because it’s still a rich-client application, you have more flexibility in regards to development.

Second question: Do you need your entire application online or just a subset?

When you think about it carefully, I expect that the answer to the question is going to be almost invariably a subset. To illustrate, if we had an Access database that’s used to track employees’ records for the HR department, does it makes sense to put it online? With all of employees’ private personal and payroll data? Likely not. We DO want to have a web page where employees can log in and request time off on so and so days. We DO want to have some kind of separation in how we use our data. Employees should always only be able to view and edit their own data and never others. Only HR can do that.

And here’s the important point: The existence of Access Services enables us to add features to our existing Access application that was previously not possible. Back to the scenario of employee requesting time off — it’s not always practical and/or desirable to ask the employee download an Access application and if necessary Access runtime also in order to be able to request time off. The download requirement also means they can’t request time off anywhere but their workstation.

For this reason, I do believe that a “hybrid application” is the best approach we may want to take toward enabling our application on the web. I’ll talk more about the hybrid applications later.

Third question: Who’s your audience? What will they do? What are your security requirements for those users?

Access Services currently requires some kind of login even though SharePoint itself supports anonymous access, thus ruling out the possibility of having a public-facing web database. For example, If you’re thinking about building surveys that should be available to any anonymous users to fill out, you’re going to have to look elsewhere, (e.g. SurveyMonkey.com).

What about different database roles? If it’s being used only by employees and hosted internally it’s a fairly simple matter but non-employees, (vendors, contractors and customers), can get more complicated. When you use a hosted solution on the web than there is no way to distinguish between an employee and a non-employee. You would have to require a separate login for each user to your database to ensue they are assigned to correct set of permissions. A mix of these groups of users will require an approach that will allow you to segment data security. That approach may involve creating a hybrid solution where some users have Access Services and others a traditional Access application.

There are three general security approaches available to us:

1) Block non-privileged users from using a downloaded Access application.

This is a very simple and no nonsense approach. Because you control the web forms & reports, they can’t just circumvent the protections you have in place, neither would they have access to the sensitive code you use to manage the security. AccessHosting.com has a video tutorial showing how download can be blocked.

2) Create separate Access files 

For scenarios where we need to use ACCDEs & Access client for some users, there is no reason why an existing client database can’t link to web tables (using SharePoint Lists) and be available to users via SharePoint’s Document Library for easy download. Though you can only have one web database file associated with one site, you have as many client databases as you need linking to the same site. By separating out the duties per-file & using SharePoint permissions to control access to the files, you can provide your users the best of both worlds. An extension of this approach is to create separate web databases accessible at different URLs. You could direct your vendors to http://mysite.com/vendors/ and your contractors to http://mysite.com/contractors while your employees uses a traditional Access client database that links to both sites’ data.

3) Use SharePoint permissions to manage access to the web tables

The approach allows for more granular control at expense of being more involved and possibly costing you some performance. If this is important enough, you may get better performance by denormalizing lists into multiple instances based on a common list template. I recommend managing permissions at the list level and not at list item levels, since you incur in higher overhead with the later. Using SharePoint permissions is much more complicated, consider it only if you have complex security requirements and are willing to invest time/money into it.

Also, note that SharePoint allows you to restrict editing of other users list items with a checkbox. Check it off if you just need a broad policy of allowing users to add and edit but not see & edit other users’ items.

Fourth question: Do you have plans for other features of SharePoint?

One common complaint with Access Services is the fact that you have to have SharePoint, whether installed locally or via a hosting company. Understandably, people were asking for web databases for long time but they may not have anticipated the SharePoint tie-in. Because SharePoint is a big investment in itself, it can be an issue. If you’re thinking about getting web databases and nothing more, you may be actually missing out. To help see what SharePoint can also do for us, let’s consider the following SharePoint strengths:

Do you have a need to track, manage and share large set of documents?

Document management is arguably SharePoint’s strong point and its Document Library offers many great features built-in. Versioning is a great example, suppose we have a set of templates that periodically need to be edited, going through group approval every time. If every person decides to save the template with a new name indicating the latest version, you quickly end up with a morass of versions. But if you place the document in the SharePoint document library with versioning enabled, you no longer you need to rename each draft. Everyone gets access to either the latest draft or the final at all times without confusion or uncertainty.

Do you have open ended processes that need to be tracked?

A natural offshoot of document management is tracking the process of a document, SharePoint provides a simple programming model from draft to final. Let’s say we have a need for writing contracts and a contract can contain several options that may be included or not and they also need to be modified by responsible parties then submitted to the other party. By using workflows, you can describe what happens when a step is completed, for example, you can design a workflow where a reviewer saves the document to the library, it then triggers an email to the supervisor notifying it’s ready for review, with a reminder in the next week. When the supervisor edits and decides it needs more editing, they save it back to the library as a draft which goes back to the reviewer, repeating the first step. When the supervisor is satisfied with the doc, he marks it as final which then forwards the document to the department head for final approval and by then it becomes the template. Workflows are a wonderful fit for review processes and provides great flexibility.

I’ve only touched on one aspect of SharePoint, and there’s others such as ability to self-administor a set of websites or searching documents & content within an organization. Bottom line, with SharePoint a database may not be the best solution. If you have any more questions or want to know more about SharePoint and Access, feel free to share and we’ll be more than happy to answer.