Migrating Access 2010 Database to SharePoint 2010 Access Service Application

I have worked at a number of companies that have Microsoft Access databases that were created by non-IT departments. Eventually, IT is called upon to make these Access databases production systems. Typically, this involves converting the Access application into some from of a .Net application with a SQL Server backend. Now with SharePoint 2010, there is another vehicle that can be used to take an Access database and expose it to a larger audience. One major benefit is that people do not need to have Access installed on their computer to be able to have access to this system.

The first thing we need is an existing Access database. Years ago, I created a simple time tracking Access database that I will use for this example. The TimeTracking database contains 2 tables.

This is the Task table schema



This is the TimeTracker table schema.



For the TaskID properties. Click on the Lookup tab, change the Display Control to Combo Box and set the remaining values as shown here.



Save the access database with these two tables. We now have our very basic sample database to represent the source Access database we wish to migrate to SharePoint 2010.




We need to have an Access Web Database to load into SharePoint. I found that the easiest way to covert an existing Access database into a new Access Web Database was to import the tables, queries, etc. Therefore, we need to use Access 2010 to create our new Access Web Database as shown here.



The next step is to Import the tables, forms, etc. into the new Web Database. To import the Access objects, you will need to click on External Data on the top ribbon, then click on the Access button.


You will now see the Get External Data wizard. Select the Access database and objects you wish to import. For our example, select the Task and TimeTracker tables and click on the OK button to perform the import. You may see errors caused by Web Compatibility Issues. These issues need to be corrected before the objects can be imported. Once all of the compatibility issues are resolved, you should see the tables listed in our new Access Web Database.


The next thing we want to do is create a web form to give user access to the tables. Do this by clicking on the Create link on the ribbon, then click on the Form button.


Access automatically creates a form like the image shown. It is interesting to note that the table import logic automatically added fields to our table schema. These fields are not for user input so just remove them from this form.


Once the fields have been removed the web form should look like this


Next you will need to set the default web form. This is under File -> Help -> Options. This opens the Access Options window. Select a default Web Display Form to the TimeTracker1 form we created


We now have a very basic Access Web Database. To publish the Access 2010 web database to SharePoint 2010 Access Services goto File -> Save & Publish -> Publish to Access Services. You will need to provide the Site Collection URL plus the site name for the site that will be created when the Access Web Database is published to SharePoint. In this case, I am calling the site TimeTracker


Once the Access Web Database is published you will receive this message.



 
Now open the site collection and click on the All Site Content link.


The published Access Web Database appears as a site within this Site Collection.



Go into the site the default web form appears

What I went through was a very basic Access database migration. I took that example and added a form for the task table, a report and a navigational form. I then re-published the site and this is the results.

Although this is a simplistic example, it does show the potential to leverage Access data via SharePoint especially with simple Access databases.