SharePoint 2010 Access Services Setting up a Demo

Thursday, March 17, 2011

I have had a few customers recently that have been looking at replacing Access Databases and have talked about connecting the database to SharePoint to retrieve the data. This may sound like a daunting task and as anyone that has used Microsoft Access in anger with a large amount of users knows it can be a nightmare to govern. Firstly lets look at the reasons that you may want to use Microsoft Access as a platform for Storing Data:-

  1. There is a requirement to store relational data without high performance requirements.
  2. There is a relatively small number of users 10 – 20 (would you want more using Access)
  3. There is a high number of users 20 – 200 but there is no budget to develop/buy an Enterprise System and an immediate issue that needs resolving. 
  4. Your users are trained in Microsoft Access and could not be up-skilled to SQL.
  5. You already have an Access Database that has been developed over a number of years so why change.


If these reasons seem familiar then you are probably going to want to keep that Microsoft Access Database and use it in conjunction with SharePoint. So what are the options? You know that SharePoint can do a lot of different things with data and is a huge platform. Most IT Pros/Developers get told to connect the Access Database to SharePoint and that is as far as the brief goes. Having been asked to do the same recently and assuming you have SharePoint 2010 Enterprise you should seriously consider Access Services. The benefits of Access Services is that you can use your existing Access skills whilst having the benefits of a scalable web based user interface. 

The customer is going to want to see this for themselves before they show any commitment so what I did was to setup a simple demo that the customer could log into and see the benefits of SharePoint 2010 Access Services.

1. Setup Access Services within SharePoint

Lets assume that Access Services is not deployed as a Service Application(by default it should be). In Central Admin Select “Manage Service Applications” under Application Management. Click new and Select “Access Services”, you should  see this screen. Fill in the form as required.

You now need to make sure that you web application is connected to this service. In Central Admin under “Application Management” click “Manage Web Applications”. Select the Application  that you want to use Access Services with and then select “Service Connections” from the ribbon. You will have to select custom as it is not a default and then tick Access Services(along with the other services you are using). That is it, you will now be able to publish Access Databases to SharePoint.

2. Upload an Access Database and provide access to the customer

There are a number of standard templates that you can use to show the capabilities of Access Services. In Access 2010  select new then select Contacts

Once you are ready to publish go to SharePoint go to the File Menu and select Info. Select Publish to Access Services. As this is a new Database you won’t have to run the compatibility checker. Enter the URL of the server you want to publish too and enter a Site name.

Click Publish to Access and that is the end of the process, open your site and verify that it works ok. Setup a  username and password for your customer to login.

3. Using existing databases  - converting to Access Services

So you have proved that Access Services works and your business users are excited and are asking you to get their existing databases into Access Services as quickly as possible. There are a number of options for this, a step back from Access Services you can actually upload the database and use SharePoint’s as a central store for your Access Database files, if you decide to use Access Services there are a number of options ranging from a straight upload to SharePoint, to using SQL Server as a Database for performance reasons. I won’t go into the full detail here – maybe in a later post.

You must convert any 2000, 2003, 2007 Access Database to Access 2010 and it must be compatible with a Web Database to be able to publish it to Access Services.  

blog comments powered by Disqus