Building an Access Database in Office 365

Looking for an easy way to move your Microsoft Access database to the cloud? Microsoft’s Office 365 service provides a central location where you can store and manipulate your Microsoft Access databases. This service has multiple benefits including leveraging Microsoft’s highly available environment to protect your data and enabling multi-user access to your database in a scalable fashion. In this article, we look at the process of moving your Microsoft Access database to Office 365.

The first thing you’ll need to do is establish an account with Microsoft’s Office 365 cloud services offering. This service isn’t free and the price varies per user per month. For this fee, you’ll receive access to a full suite of Office 365 services. All accounts include cloud-based email, shared calendars, instant messaging and video conferencing, viewing of Office documents, external and internal websites, and antivirus and antispam protection. The higher tiers of service provide additional options.

For more on Office 365, see the Office 365 pricing plan comparison document.

As an aside, the services provided by Office 365 are hosted by Microsoft SharePoint. While this article focuses on the Office 365 cloud environment, you can also publish your database to any SharePoint server that supports Access Services. If your organization is already using Microsoft SharePoint, check with your administrator to see if you have a local hosting option available to you.

Next, you’ll need to create the Access database that you wish to share on the web. You might do this by opening an existing database if you want to migrate one of your current databases to the web. Alternatively, you might create a brand new database for a web-specific application. If you need assistance, view our tutorial Creating an Access 2010 Database from scratch.

For the purposes of this tutorial, we will use a simple Access database that consists of a single table of staff information as well as a simple data entry form. You can either recreate this database or use your own database as you walk through the example.

Before you can publish your database to the web, you’ll need to verify that it is compatible with SharePoint. To do this, select “Save & Publish” from the File menu within Access 2010. Then choose the “Publish to Access Services” option in the “Publish” section of the menu that appears. Finally, click the “Run Compatibility Checker” button and review the results of the test.

Once you’ve established that your database is compatible with SharePoint, it’s time to publish it to the web. You can do this by selecting “Save & Publish” from the File menu within Access 2010. Then choose the “Publish to Access Services” option in the “Publish” section of the menu that appears. You’ll need two pieces of information to continue:

• The Server URL for your Office 365 site. This is typically of the form http://yourname.sharepoint.com/teamsite if you are publishing the database to the internal site of an Office 365 installation and have not altered the default settings.
• A name for your site. This name will become part of the site URL, so choose it carefully. In my example, I will use the name “Staff Directory”.

Once you have entered this information, make note of the Full URL provided above the text box where you entered the Server URL. This URL will be of the form “http://yourname.sharepoint.com/teamsite/StaffDirectory” and is how users will access your site.

After verifying these settings, click the “Publish to Access Services” button to continue. The Microsoft Office 365 login window will appear and ask you to provide your Office 365 user ID. Provide your username and password.

At this point, Access will take over and begin the process of publishing your database to the web. You will see several dialog boxes come and go as your database synchronizes with Microsoft’s servers. Wait patiently until you see the “Publish Succeeded” window.

Next, open your favorite web browser and navigate to the Full URL you noted in the previous step. Unless you’re already logged into Office 365 in the browser, you’ll be asked to provide your username and password again. Then you should see a window similar to the one above offering you access to the hosted version of your Microsoft Access database.

Congratulations! You’ve created your first cloud-hosted database. Go ahead and explore the online version of your database and get to know Office 365.