Introduction to integrating data between Access and a SharePoint site
You can share and manage data in many ways by using Microsoft Office Access 2007 with Windows SharePoint Services 3.0. You can benefit from the collaboration features of a SharePoint site while continuing to use the data entry and analysis features of Access. For example, you can track versions of data, subscribe to alerts so that you know when changes are made, and manage permissions for the data.
In this article
Ways to work with data
When you use Office Access 2007, you can share, manage, and update data from SharePoint sites in several different ways.
Move data to a SharePoint site When you move a database from Access to a SharePoint site, you create lists on the SharePoint site that are linked as tables in your database. When a database is moved, Access creates a new front-end application that has all the old forms and reports and the new linked tables that were just exported. The Move to SharePoint Site Wizard helps you to move the data from all your tables at the same time.
After the SharePoint lists are created, people can work with the lists on the SharePoint site or in the linked tables in Access while they use the features of a SharePoint site to manage the data and stay updated with changes. As an administrator, you can manage permission to the data and versions of the data so that you can see who changed it or restore previous data.
Publish data to a SharePoint site If you are collaborating with others, you can store a copy of a database in a library on a SharePoint server and continue to work in the database by using the forms and reports in Access. You can link lists as tables in the database, which is helpful if you want to track data on the SharePoint site, and then you can create forms, queries, and reports to work with the data. For example, you can create an Access application that provides queries and reports for SharePoint lists that track issues and manage employee information. When people work with these lists on a SharePoint site, they can open the Access queries and reports from the View menu for the SharePoint lists. For example, if you want to view and print an Access issues report for a monthly meeting, you can do it directly from the SharePoint list.
When you publish a database to a server for the first time, Access provides a list of Web servers that makes it easier to navigate to the location where you want to publish, such as a document library. After you publish the database, Access remembers the location so that you don't need to locate the server again when you publish your changes. When you publish a database to a SharePoint site, the database becomes available to people with permission to work with the SharePoint site.
Open Access forms and reports from a SharePoint site Users can open lists in rich Access views from a SharePoint site. Access forms, reports, and datasheets can appear alongside other views on a SharePoint site. When you choose an Access view, Access starts and opens the requested form, report, or datasheet. This makes it easy for you to run a rich Access report on a SharePoint site without having to first start Office Access 2007 or navigate to the right object.
Create databases from SharePoint lists You can open a SharePoint list in Access. If a database doesn't exist, you can create it in Access and then create a set of forms and reports based on your list.
Take SharePoint lists offline with Access If you need to take some work home with you, you can take your SharePoint lists offline with one click by using Access. You can work on your data in Access and then synchronize your changes or reconnect with the SharePoint site at a later time.
Import or link to a SharePoint list You can bring a SharePoint list into Access by importing or by linking. Importing a SharePoint list creates a copy of the list in an Access database. During the import operation, you can specify the lists that you want to copy, and, for each selected list, you can specify whether you want to import the entire list or only a specific view. Linking lets you connect to data in another program without importing that information, so that you can view and edit the latest data both in the original program and your Access database without creating and maintaining a copy of the data in Access.
Integrating and managing data
After you move your Access data to a SharePoint site, you can manage who views the data, track the versions, and recover any data that is accidentally deleted. Better data integration between Office Access 2007 and SharePoint sites enhances performance and application design.
Manage permissions on SharePoint sites You can assign varying levels of permission to lists and Access databases on SharePoint sites. You can assign limited reading permissions or full editing permissions to groups, and you can selectively allow or deny access to certain users. If you need to limit access to just a few sensitive items in a database, you can set permissions on specific list items on a SharePoint site.
Track and manage versions on SharePoint sites You can track versions of list items and view the version history on a SharePoint site. You can recover a previous version of an item if needed. If you need to know who changed a row or when it was changed, you can view the version history.
Retrieve data from the Recycle Bin You can use the new Recycle Bin on SharePoint sites to easily view deleted records and recover information that was accidentally deleted.
Improved performance for tables linked to SharePoint lists Optimizations were made to the internal processing of tables that are linked to SharePoint lists to enable a faster and smoother experience than with previous versions.
Enhanced mapping of Windows SharePoint Services data types With new support for multivalued fields and attachments, Access now supports more of the data types found on SharePoint sites, making the design and building of shared applications much simpler.
Tracking your data and workflow
You can use Office Access 2007 with information from SharePoint sites to track your organization's data, issues, and workflow.
Tracking Access now has a tracking template that interacts directly with the Issue Tracking template on a SharePoint site. The schemas are the same, and the Access solution can be used as a front end — for example, with forms and queries — against data from a SharePoint site.
Tracking the history of Memo fields Memo fields are useful for storing large amounts of information. You can set a property that tells Access to retain a history of all changes to a Memo field. You can then view a history of those changes. This feature also supports the versioning feature on SharePoint sites, so that you can also use Access to view the changes that were made to the SharePoint list.
Integration with workflow on SharePoint sites You can now use Access to interact with and report on workflows so that you can better gauge the project's status and evaluate your business processes. The workflow reports can span multiple lists as well as information from multiple workflows.