Move the tables in an Access database to a SharePoint site and link to them

If you have data in a Microsoft Office Access 2007 database, you can share it as lists on a Windows SharePoint Services 3.0 site, where team members can interact with it. They can share and manage the content by using the features of a SharePoint site while they continue to use forms, reports, and queries from Access.

In this article


Overview

When you move a database from Office Access 2007 to a SharePoint site, you create lists on the SharePoint site that remain linked to tables in your database. The Move to SharePoint Site Wizard helps you to move the data from all your tables at the same time and to maintain their relationships.

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. For example, you can move tables from an inventory database to a SharePoint site, and the relationships between the equipment and the employees who are responsible for the equipment are maintained as links between the SharePoint lists.

You can manage permission to the data on the SharePoint site by assigning various levels of permission to different groups or to specific people. You can manage versions of the data so that you can see who changed it or restore previous data. You can also use the Recycle Bin on the SharePoint site to recover items that are accidentally deleted. People can stay updated with changes to the data by using alerts or RSS technology.

Links to the SharePoint lists are stored in your Access database, and the forms, queries, and reports also remain in Access. You can enter the data by using a table or a form in Access or by editing the list on the SharePoint site.

If you choose to save a copy of the database on the SharePoint site and create shortcuts to the Access forms, reports, and queries, you can create special views for your Access lists. The forms, queries, and reports are available as views on the View menu menu image of the corresponding SharePoint list.

When you move your data and create linked lists, you enable multiple people to work on the database. You can also use the versioning features of a SharePoint site to change the data. People can subscribe to alerts for the data to know when new items are added or existing items are changed. If they need to run an Access query or distribute a formatted report, they can click the View menu for the SharePoint list to run an Access query or to open and print an Access report.

Top of Page Top of Page

How data is moved

When possible, the Move to SharePoint Site Wizard moves data to lists that are based on list templates on the SharePoint site, such as a Contacts list. If a table can't be matched to a list template, the table becomes a custom list in datasheet view on the SharePoint site.

Depending on the size of the database, its number of objects, and system performance, the operation can take some time. If you change your mind during the process, you can click Stop to cancel it.

The wizard creates a backup copy of the database on your computer. In Access, it creates links to the lists from the tables, so that it is easy to locate the data on the SharePoint site when you are working in Access. Where possible, the relationships among the tables are represented as links between the SharePoint lists.

If any issues arise, the Move to SharePoint Site Wizard reports the issues and saves them in the Access database as a log table that you can use to help troubleshoot.

Top of Page Top of Page

Use the Move to SharePoint Site Wizard

  1. On the External Data tab, in the SharePoint Lists group, click Move to SharePoint.

 Note   This option is available only if your database is saved in Office Access 2007 format.

  1. Follow the steps in the Move to SharePoint Site Wizard, including specifying the location of your SharePoint site.

To cancel the process, click Stop.

  1. On the last page of the wizard, select the Show Details check box to see more details about the migration.

This wizard page describes which tables have been linked to lists and provides information about a backup location and the URL for your database. It also provides a warning if some migration issues were encountered and provides the location of a log table where you can see more details about the issues.

  1. Click Finish when the wizard completes its actions.

If the wizard displays a warning, you should review the log table and take any actions necessary to ensure that your data was migrated successfully. For example, certain fields may not be moved or may be converted to another data type that is compatible with a SharePoint list.

 Note   To view your lists on the SharePoint site, click Lists on the Quick Launch, or click View All Site Content. You may need to refresh the page in your Web browser. To make your lists appear on the Quick Launch on the SharePoint site or to change other settings, such as enable versions to be tracked, you can change the list settings on the SharePoint site. For more information, see Help on the SharePoint site.

Top of Page Top of Page

Limitations of migration

When the Move to SharePoint Site Wizard finishes, you see a message if Access encountered any issues with the data. Access creates a log table called Move to SharePoint Site Issues and adds the table to the database. The Move to SharePoint Site Issues table is stored in the database but is not published as a list on the SharePoint site.

The following table lists the limitations in how data is migrated, typically when Access and SharePoint don't share the identical feature or, in some cases, don't share a data type. For example, if your Access table supports referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you add, update, or delete records.), it is not enforced in the list on the SharePoint site. The information in the following table may help you to decide whether to migrate your data, and it may be helpful if you are reviewing any issues reported in the Move to SharePoint Site Issues table.

Type of data or issue Issue Result
COM object data type SharePoint sites do not support the COM Object data type. Field is not moved.
Binary data type SharePoint sites do not support the Binary data type. Field is not moved.
Date SharePoint sites do not support dates prior to 1900. Data with dates prior to 1900 is not moved.
New line characters in text fields SharePoint sites do not support new line characters in a Single Line of Text field. Field is converted to a Multiple Lines of Text field or Memo field.
Decimal data type SharePoint sites do not support the Decimal data type. The Number field or Double Integer field is used instead.
Replication ID data type SharePoint sites do not support the Replication ID data type. A Single Line of Text data type is used instead, depending on the type of data.
Referential integrity SharePoint sites do not support referential integrity. Referential integrity is not enforced in the new list.
Default values that are not supported in a SharePoint list SharePoint sites accept default values that are static, such as text or a number, as well as standard dates. Default values from Access that are dynamic are not migrated. Certain default value properties are not moved.
Data validation on a field or table No data validation rules are moved to SharePoint sites. Any data validation on a field or table is not moved or enforced.
Unique index fields SharePoint sites use one unique index field for its ID column in a list. Other unique index fields or sets of fields are not moved.
Relationships with cascading deletes or updates SharePoint sites do not support cascading deletes to related records. Deletes are not cascaded to related records, and updates are not cascaded to related fields.
Relationships that enforce referential integrity SharePoint sites do not support referential integrity. Referential integrity is not enforced in the relationships between data in the lists.
Fields that enumerate automatically (other than the ID field) SharePoint sites support only automatic numbering for the field used for the ID column in a list. Automatic numbering is not applied to columns other than the ID column.
Relationships in which lookups cannot be created Some relationships are not supported in SharePoint sites, such as when the primary key is not related to the ID column or is not an integer. The relationship is not moved.

Top of Page Top of Page

Move to SharePoint feature is unavailable when you use earlier database file formats

If the database that you are working with does not use the Access 2007 database file format (ACCDB, ACCDE), the Move to SharePoint feature is unavailable. To use this feature, convert your database to the Access 2007 file format.

For more information about converting your database to the Access 2007 file format, refer to the links in the See Also section.

Top of Page Top of Page

 
 
Applies to:
Access 2007