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!
15 Comments
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
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!
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
Dear,
the above is very useful and this help us to move Access 2010 Application to the Sharepoint and accordingly the users can easily open it through the Open with Access.
but the problem is the list of tables is appearing and the user can open them and edit/Add/etc.. without using the MS Access application.
the question is:
how can i hide the button OPTIONS with appears in the last screen shots and is there any URL link to open directly the MS Access application from the Sharepoint?examples: https://toshi:41515/sites/access/Projects/? testdb.accdb
Please advise
Thank You
regards, Yasser
Hi Yasser,
I’ve done some research and unfortunately I was not able to hide the options button. I was also not able to find any information about how to hide it.
It appears that it is not possible, although I could have missed something :)
Good luck!
Will this enable multiple employees to access the database at the same time?
Hi Ashely,
Yes. Multiple users should be able to access the database.
I published an Office 2013 Access DB to my sharepoint site, but it faile to publish (URL links were too long).
Now, I can’t open it or any copies of it without it showing Excel tables instead of Access tables.
How can I unlink this? There is obviously something in the accdb that says this is a Sharepoint database (registry? .ini?), but I can’t find it.
Thanks.
Correction – Office 15
Hi Larry,
From what I could gather there does not seem to be an easy way to do this. Have you tried following the instructions detailed here?
Hi dear, may I ask for any assistance to move a ms access 2007 database to Office 365.
I need some technical guide to do this.
Thank you
We have some applications that use Access 2013 front ends to link SQL Server data.
Can we upgrade the front ends to O365 while keeping the data in SQL Server?
Hello Everett,
I *suppose* you can. I’d suggest that you check this.
[…] 6. Moving your existing Access database to Office 365 […]
[…] 6. Moving your existing Access database to Office 365 […]