Working data on a SharePoint server can mean several different things. The options run the gamut from having the entire application on SharePoint, including the front end and forms, to just relying on data backup and revision management features. With the caveat that SharePoint can protect the data and enforce permissions, the following sections focus on two of the most common configurations.
Publishing the data file to SharePoint but keeping the application on a local workstation could be perceived as the best of both worlds. Access uses linked tables to connect the data stored on the SharePoint server, and you get to take advantage of Access's rapid development environment to create a flexible user interface. Having the data on the server facilitates collaboration and satisfies a host of security and IT concerns. It provides the reliability of SQL Server combined with the universal access of a website.
When data is published to SharePoint, users can still download lists and have the freedom to work offline. When they reconnect to SharePoint, they can synchronize the data. The process prompts for the resolution of conflicts because of the potential for records to be updated in multiple locations. This functionality is an alternative to replication that is easier to manage. The fact that replication is not supported by the ACCDB file format may be a strong incentive to start working with SharePoint.
Moving the data to SharePoint initiates the Move to SharePoint Site Wizard. The wizard manages moving all of the tables at the same time, and it creates SharePoint lists with the appropriate relationships. It also creates a new front-end file linked to the newly created SharePoint lists. The new front-end file stores the links to the lists on the SharePoint site, and can be distributed without the hassles of relinking tables.
The External Data tab includes a prompt to link to SharePoint lists, making it easy to create a linked table or to import the data from any SharePoint list. Additionally, Access and SharePoint share several templates, so opening the SharePoint lists instantiates the matching template in Access. You can also create Access views on SharePoint.
Access forms, reports, and datasheets can be listed in a SharePoint document library along with other documents. When a user chooses one of these objects, Access opens on the SharePoint site. The user not only gets the current data, but also the correct version of the form or report, and he does not have to run Access on his computer.
While it's great to publish an Access data file, what about working with data already contained in SharePoint lists? As you would expect, there's a wizard to handle this, too. Just open the list in SharePoint and select Actions O Open In Microsoft Access. When you choose to link to the data on the SharePoint site, you can build queries, forms, and reports to work with the SharePoint list as well as with any other data source.
If you publish the database to a SharePoint library, the forms, reports, and other objects are listed on the View menu for others to use.
Was this article helpful?