Moving your existing Access database to Office 365
A few years ago I had a client who since they started their business used a Microsoft Access database to track their projects and the time their consultants worked on projects. As the company expanded the database grew up to a point where they realized an upgrade is needed. I was asked to move the database to a Microsoft SQL server backend, whilst keeping MS Access as the front-end. This worked fine for a year or so until they realized they needed to give their consultants the ability to add their time worked from anywhere.
Another year and a considerable amount of money later a new system was written (not by me unfortunately :)) to enable their employees/consultants to bill from anywhere.
In today’s post I want to look at how they could’ve leveraged Office 365 Access services to web-enable their database.
First, let’s look at the Access database. It has a simple project list, which displays the current projects:
When the user clicks on the ID field a new window opens where the employees can capture details about the project:
Before you can publish your database to Office 365 you need to upgrade it to Microsoft Access 2010. Once the database is saved in Access 2010 format, you can publish it by opening it in Access and selecting Save & Publish from the File menu (Backstage View). Under the Publish heading, there is the option to Publish to Access Services.
Before we can safely publish our database, it is recommended to first check whether your database is compatible with the web. To do this all you need to do is click on the “Run Compatibility Checker” button in the Access backstage view.
In my case there are some compatibility issues. To view them click the “Web Compatibility Issues” button. Access will create a Web Compatibility Issues table which contains all the compatibility issues in your database. Most of the errors are generally easy to fix. For example, the columns in your Access table cannot have a slash (/) in their name and you cannot use custom currency formats. See the Microsoft Office site for a list of General and Schema errors.
Once you’ve fixed all the compatibility errors, you’re ready to publish your database to the web. Before we publish, we’ll first create a new SharePoint site collection.
Creating a SharePoint Site Collection
To create a new site collection, log into your Office 365 account. From the Admin Overview page, click on the Manage link under SharePoint Online:
The SharePoint Online administration center will open in a new page. Click on the “Manage site collections” link. On the Site Collections page, you’ll see a list of all your site collections; add a new one by clicking on the new button and selecting “Private Site Collection“. A “New Site Collection” modal dialog will open, set the field values to the following:
- Title: MS Access
- Website Address: sites/Access
- Template Selection: Blank Site
- Administrator: Your user name
- Storage Quota: 250
- Resource Usage Quota: 300
Once the new site collection is ready, you will see the New indicator next to its name:
Publishing the database
With our site collection in place, let’s go ahead and publish our database. On the MS Access backstage view, complete your server url, give the new site a name and click the “Publish to Access Services” button.
The publishing process will synchronize the data, forms and tables with SharePoint. All the tables in the database will be turned into SharePoint lists.
If all goes well, you should be greeted with a window informing you that the database was successfully published.
If you click on the link, you will be taken to the new web version of your database.
Thank you for reading. Until next time, keep Office 365-ing!