Pieter van der Westhuizen

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:

Access database

When the user clicks on the ID field a new window opens where the employees can capture details about the project:

Project details

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.

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:

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:

New indicator next to the new site collection 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.

Publish to Access Services

The publishing process will synchronize the data, forms and tables with SharePoint. All the tables in the database will be turned into SharePoint lists.

Synchronizing data in tables

If all goes well, you should be greeted with a window informing you that the database was successfully published.

The database was successfully published

If you click on the link, you will be taken to the new web version of your database.

New web version of your database

Thank you for reading. Until next time, keep Office 365-ing!

You may also be interested in:

3 Comments

  • http://0.gravatar.com/avatar/e2c1f4028f0a4fa01d642ff1faaf2705?s=32&d=http%3A%2F%2F0.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Peter Hensel says:

    I have published the companies 2010 database to sharepoint succesfully I know whwt to deploy the access database to other users so the can work on the database but when I copy the database o another pc I get the message unreconisable format. Any suggestions.
    Thanks

  • http://0.gravatar.com/avatar/e1a4c2b21a5186e0b27c1c601f418b76?s=32&d=http%3A%2F%2F0.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Pieter van der Westhuizen says:

    Hi Peter,

    Hmmm…Do your users work on a desktop version of the database or on the web version? If you’ve copied the database to another pc, something could have gone wrong with the copy progress that corrupted the file…If I had to guess :)

    Maybe, try to open the file on your pc, compact and repair it, zip it and copy the zip file to the user pc and extract it.

    Hope this helps!

  • http://1.gravatar.com/avatar/d94f8b81bed9b514b3051060f3d940de?s=32&d=http%3A%2F%2F1.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Stan says:

    Pieter,

    Awesome guide. Really helpful when it came to pushing an Access DB I had up to 365. One thing to keep in mind is that Compatibility Check won’t catch every potential error prior to publishing to the 365 site. I had a clean check and then ended up having to fix a few extra errors (blank values in columns that had a look-up). These errors would only display when you tried to publish to access, the publish would fail, and then generate a report.

    Thanks!
    Stan

Post a comment

Have any questions? Ask us right now!