SharePoint Online (and Server) support Access databases. More exactly, using Access Services users are able to upload a database in SharePoint sub-site, to work in it and to sync changes.
With this feature, people can publish Access 2010 databases to SharePoint and share them with others. The Access Services feature allows SharePoint Online users to view, create, edit, and save Access databases while working inside a web browser. Users can publish and share information across teams, and create and modify databases where no Access client is needed.
All you need is a web database (you can use a template or create your own) and a start-up form. Of course, you need to add data into the DB (form, query’s, etc.)
Still, there is a very big limitation: reports.
Access reporting is not supported by SharePoint Online Access Services because SQL Server Reporting Services (SSRS) is not currently enabled for SharePoint Online. Access reporting requires SSRS.
Informations from Microsoft SharePoint Online for Enterprises Service Description.docx
Now, let’s see how to upload a DB into a SharePoint sub-site.
1. See if the DB is Web compatible: File – Save & Publish – Publish to Access Services – Run Compatibility Checker.
If the database is not compatible, a new table will be created. In that table are shown all the incompatible elements: tables, query’s, reports, etc. Also, a solution and a link to a help page will be provided.
The current database is OK and I have no errors.
2. We must select a Web Display Form, because in SharePoint we won’t see the tables (you must create one before).
- File – Options – Current Database and setup a Web Display Form.
Now, it’s time to publish the DB to SharePoint: File – Save & Publish – Publish to Access Service – fill in the Server URL and the Site Name and then click on Publish to Access Services.
In this moment, every object from the DB is published.
After the publishing operation is over, a link to the sub-site is displayed.
Here is the DB in SharePoint:
Now we can browse through the records, add new ones, search records, etc.
More than that a copy of the DB is created on the local machine during publishing.
From the Options menu we can choose to share this sub-site, open the DB in Access, or to view the DB components.
We also have two option to export a object to Excel, to modify it, to view it, etc.
If we want to edit the DB, we have to access the Modify this web database, add new fields, customize forms and reports link.
After we edit, add data, etc., we must sync the DB: File – Info – Sync All
It’s possible that from time to time (especially when you publish it), you will get a error message like the one from the bellow screenshot.
The solution is to close the DB and then re-open it.