From Access to SharePoint

Today I learnt you can move an Access database to SharePoint. You could do that since so much time ago!!! … I’m sure… but I learnt it today.

SharePoint provides a service (named Access Service) that allow us to share and publish Access DBs in a web environment. This service is able to build a web site where:

• The Access tables are converted in SharePoint lists.
• The forms are converted in ASP.Net pages.
• Macros are converted in JavaScript.
• Access reports are converted in Sql Server Reporting Services.

The creation of databases is done through the publication process to SharePoint from Access 2010 client.

• First, the SharePoint compatibility is checked (there is a compatibility check function)
• Second, execute ‘Publish Access Services’ specifying the web site URL.
• Third, the central SharePoint administration service is activated

Depending of the complexity of the data model, you can find performance issues with the proposed chosen architecture by SharePoint. In this case, you may want to consider the following approaches:

1. Use the SQL Server up-sizing wizard. The data will be moved into a SQL Server instance, and the front end will remain as an Access client. It can work very well depending on the specifics of the application.
2. You can try ‘Access Services’ in SharePoint (requires Enterprise Edition). SharePoint lists are very good for simple flat table structures, if your Access schema is more complicated, it is better to go down the SQL Server route. If you really want to use SharePoint for the front end, move the data into SQL Server then surface it using either BCS (Enterprise again) or custom Web Parts, Silverlight etc.