Want to combine and enhance both Access and SharePoint? To do so, you can import, link, or move data between them. Importing creates a copy of the SharePoint list in an Access database. Linking connects to data in another program, so that you can view and edit the latest data both in SharePoint and Access. Moving creates lists on the SharePoint site that remain linked to tables in your database and maintains their relationships.
Warning: Although you can save an Access database file to OneDrive or a SharePoint document library, we recommend that you avoid opening an Access database from these locations. The file may be downloaded locally for editing and then uploaded again once you save your changes to SharePoint. If more than one person opens the Access database from SharePoint, multiple copies of the database may get created and some unexpected behaviors may occur. This recommendation applies to all types of Access files including a single database, a split database, and the .accdb, .accdc, .accde, and .accdr file formats. For more information on deploying Access, see Deploy an Access application.
What do you want to do?
Import a SharePoint list
When you import data, Access creates a table and copies the columns and items from the source list (or view) into that table as fields and records. 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. At the end of the import operation, you can choose to save the details of the import operation as a specification. An import specification helps you to repeat the import operation in the future without having to step through the Import Wizard each time.
Here are common reasons for importing a SharePoint list into an Access database:
-
To permanently move data, such as a contacts list, to an Access database, because you no longer need the information on your SharePoint site. You can import the list into Access, and then delete the list from the SharePoint site.
-
Your department or workgroup uses Access, but you are occasionally pointed to a SharePoint list for additional data that must be merged into one of your databases.
Complete these steps before you import the list
-
Locate the SharePoint site that contains the lists that you want to copy, and make a note of the site address.
A valid site address starts with http:// or https:// followed by the name of the server, and ends with the path to the specific site on the server.
-
Identify the lists that you want to copy to the database, and then decide whether you want the entire list or just a particular view. You can import multiple lists in a single import operation, but you can import only one view of each list. If necessary, create a view that contains just the columns and items that interest you.
-
Review the columns in the source list or view.
The following table explains some considerations to keep in mind when importing different elements:
Element
Considerations
Columns
Access imports only the first 256 columns, since it supports only 256 fields in a table. To avoid this problem, create a view of the list in SharePoint and add to it only the columns that you want, ensuring that the total number of columns doesn't exceed 256. Then use the ImportSharePointList Macro Action to specify the intended View ID.
Folders
Each folder in the SharePoint list becomes a record in the Access table. Items inside a folder also appear as records, immediately below the record corresponding to that folder.
Lookup columns
If a source column looks up values in another list, Access imports the display values as part of the field itself. Access doesn't import the looked up table. If you want to recreate the lookup to another table, see Link to a SharePoint list and Move data to SharePoint.
Calculated columns
The results in a calculated column are copied to a field whose data type depends on the data type of the calculated result. The expression that performs the calculation is not copied.
Attachments
The attachment column of the list is copied to a field named Attachments.
Multivalued columns
A column of type Choice or Lookup can contain multiple values. When you import a column that supports multiple values, Access creates a column that supports multiple values.
Rich text formatting
Columns containing rich text formatting are imported into Access as Long Text fields. The Text Format property of the Long Text Field is set to Rich Text, and the formatting is preserved.
Relationships
Access does not automatically create relationships between related tables at the end of an import operation. You must manually create the relationships between the various new and existing tables by using the options on the Relationships tab. To display the Relationships tab, on the Database Tools tab, in the Relationships group, click Relationships.
-
Identify the database into which you want to import the lists.
Ensure that you have the necessary permissions to add data to the database. If you don't want to store the data in any of your existing databases, create a blank database.
-
Review the tables in the database.
The import operation creates a table with the same name as the SharePoint list. If that name is already in use, Access appends "1" to the new table name — for example, for Contacts1, if Contacts1 is also already in use, Access will create Contacts2, and so on.
Import the list
-
Open the Access database in which the imported data will be stored. If you don't want to store the data in any of your existing databases, create a blank database.
-
The location of the import/link text wizard differs slightly depending upon your version of Access. Choose the steps that match your Access version:
-
If you're using Microsoft 365, Access 2021, or Access 2019, on the External Data tab, in the Import & Link group, click New Data Source > From Online Services > SharePoint List.
-
If you're using Access 2016, on the External Data tab, in the Import & Link group, click the More button to drop down a list of options and then click SharePoint List.
-
-
Access opens the Get External Data – SharePoint Site dialog box.
-
In the wizard, specify the address of the source site.
-
Select the Import the source data into a new table in the current database option, and click Next.
-
From the list that the wizard displays, select the lists that you want to import.
Note You can link to SharePoint libraries, but you can only add documents in SharePoint.
-
In the Items to Import column, select the view that you want for each selected list.
-
The check box labeled Import display values instead of IDs for fields that look up values stored in another list controls which data is imported for lookup columns in the selected lists. Do one of the following:
-
To import the display values as part of the field itself, select the check box. In this case, the field will not look up another table for values.
-
For the destination field to look up another table for values, clear the check box. Doing this will copy the IDs of the display value rows to the destination field. The IDs are necessary for defining a lookup field in Access.
When importing IDs, you must import the lists that currently supply the values to the lookup columns (unless the destination database already has tables that could act as lookup tables).
The import operation places the IDs in the corresponding field, but it does not set all of the properties necessary to make the field work like a lookup field. for more information on how to set the lookup properties of such a field, see Create or delete a lookup field.
-
-
Click OK.
Access imports the lists, and then displays the status of the operation on the last page of the wizard. If you plan to repeat the import operation at a later time, you can save the details as an import specification. Access does not overwrite a table in the database as part of an import operation, and you cannot append the contents of a list or a view to an existing table.
What else should I know about importing?
-
For information on how to save the details of your import into a specification that you can reuse later, see the article Save the details of an import or export operation as a specification.
-
For information on how to run saved import specifications, see the article Run a saved import or export operation.
-
For information on how to schedule specifications to run at specific times, see the article Schedule an import or export operation.
-
For information on how to change a specification name, delete specifications, or update the names of source files in specifications, see the article Manage Data Tasks.
Link to a SharePoint list
When you link to a SharePoint list, Access creates a new table (often referred to as a linked table) that reflects the structure and contents of the source list. Unlike importing, linking creates a link only to the list, not to any specific views of the list.
Linking is more powerful than importing in two ways:
-
Adding and updating data You can make changes to data either by browsing to the SharePoint site or by working in Datasheet or Form view within Access. The changes that you make in one place are reflected in the other. But, if you want to make structural changes, such as removing or changing a column, you must do so by opening the list on the SharePoint site. You cannot add, delete, or modify the fields in a linked table while working in Access.
-
Lookup tables When you link to a SharePoint list, Access automatically creates linked tables for all lookup lists (unless the lookup lists are already linked to the database). If the lookup lists contain columns that look up other lists, those lists are also included in the linking operation, so that the lookup list of every linked table has a corresponding linked table in the database. Access also creates relationships between these linked tables.
Common scenarios for linking to a SharePoint list
Typically, you link to a SharePoint list from an Access database for these reasons:
-
Your department or workgroup uses Access for rich reporting and querying, and uses SharePoint for team collaboration and communication. Individual teams create lists to track various things (such as contacts and issues), but often this list data must be brought into a database for aggregation and reporting. Linking is the appropriate choice, because it allows users of both the SharePoint site and the database to add and update data, and to always view and work with the latest data.
-
You are an Access user who only recently started using SharePoint. You migrated several of your databases to your team SharePoint site, and most of the tables in these databases are linked tables. From now on, instead of creating local tables, you will create SharePoint lists, and then link to these lists from your databases.
-
You want to continue storing your lists on SharePoint sites, but you also want to work with the most recent data inside of Access to run queries and print reports.
Prepare to link to a SharePoint list
-
Locate the SharePoint site that has the lists to which you want to link, and make a note of the site address.
A valid site address starts with http:// or https:// followed by the name of the server, and ends with the path to the specific site on the server.
-
Identify the lists to which you want to link. You can link to multiple lists in a single linking operation, but you cannot link to surveys, discussions, or a specific view of any list.
-
Review the columns in the source list. The following table explains some considerations to keep in mind when linking to different elements.
Element
Considerations
Columns
Access links only the first 256 columns, since it supports only 256 fields in a table. To avoid this problem, create a view of the list in SharePoint and add to it only the columns that you want, ensuring that the total number of columns doesn't exceed 256. Then use the ImportSharePointList Macro Action to create the linked table specifying the intended View ID.
Folders
Each folder in the SharePoint list appears as a record in the Access table. Items inside a folder also appear as records, immediately below the record corresponding to that folder.
Lookup columns
If a source column looks up values in another list and the related list isn't already in the database, Access automatically creates linked tables for the related lists.
Note Access also creates a UserInfo table that corresponds to the SharePoint User Information List. SharePoint uses this list to look up user account information, such as email, picture, user name, for SharePoint columns, such as CreatedBy, ModifiedBy, and Person or Group. This SharePoint User Information List is only visible to site administrators.
Calculated columns
The results in a calculated column are displayed in the corresponding field, but you are not able to view or modify the formula in Access.
Attachments
The attachment column of the list is displayed as a field named Attachments.
Read-only columns
The columns that are read-only in a SharePoint list will continue to be read-only in Access. In addition, you might not be able to add, delete, or modify columns in Access.
Multivalued columns
A column of type Choice or Lookup can contain multiple values. For such columns, the linking operation creates fields that support multiple values. Multivalued lookup columns are created in the linked table if the source column is of type Lookup.
-
Identify the database in which you want to create the linked tables. Ensure that you have the necessary permissions to add data to the database. If you don't want to store the data in any of your existing databases, create a new, blank database.
-
Review the tables in the database. When you link to a SharePoint list, a table having the same name as the source list will be created. If that name is already in use, Access will append "1" to the new table name — for example, Contacts1. (If Contacts1 is also already in use, Access will create Contacts2, and so on.) The same rules apply for related lists.
Link to the data
-
Open the destination database.
-
The location of the import/link text wizard differs slightly depending upon your version of Access. Choose the steps that match your Access version:
-
If you're using Microsoft 365, Access 2021, or Access 2019, on the External Data tab, in the Import & Link group, click New Data Source > From Online Services > SharePoint List.
-
If you're using Access 2016, on the External Data tab, in the Import & Link group, click the More button to drop down a list of options and then click SharePoint List.
-
-
Access opens the Get External Data – SharePoint Site dialog box.
-
In the wizard, specify the address of the source site.
-
Select Link to the data source by creating a linked table, and then click Next
The wizard displays the lists that are available for linking.
-
Select the lists that you want to link to, and then click OK.
Note: If some lists are already linked to the current database, the check boxes corresponding to those lists will be selected. If you want to remove any links, clear the check boxes of the links you want to remove.
-
Access attempts to create linked tables, both for the lists that you selected during this operation and for each of the related lists. Also, Access attempts to refresh the linked tables corresponding to the lists that were selected in the wizard. Access also creates the relationships between the tables. Unlike an import operation, a linking operation maintains the lookup property settings between a lookup field and the related table. You need not manually set the properties of the lookup field in table Design view.
-
Review the new linked tables in Datasheet view. Ensure that all of the fields and records are displayed correctly.
Access selects the right data type for each field that corresponds to a source column. It is important to note that each time you open either a linked table or the source list, you would see the latest data displayed in it. But, structural changes made to a list are not automatically reflected in a linked table. To update a linked table by applying the latest list structure, right-click the table in the Navigation Pane, point to More Options, and then click Refresh List.
Note: Access never overwrites a table in the database as part of a link operation. Also, you cannot append the contents of a SharePoint list to an existing table.
Move data to SharePoint
Moving data to SharePoint is a way to effectively create a backend database, but in this case, the data is contained in SharePoint lists. The front-end is still an Access database, and you can deploy it in a similar way as a split database. When possible, the Export Tables to SharePoint 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 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. If any issues arise, the Export Tables to SharePoint Wizard reports the issues and saves them in the Access database as a log table that you can use to help troubleshoot.
Tip: Consider creating a separate SharePoint site to keep the lists in a self-contained location.
Use the Export Tables to SharePoint Wizard
-
On the Database Tools tab, in the Move Data group, click SharePoint. This option is only available if your database is saved in the .accdb file format.
-
Follow the steps in the Export Tables to SharePoint Wizard, including specifying the location of your SharePoint site.
To cancel the process, click Stop.
-
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.
-
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: Access also creates a UserInfo table that corresponds to the SharePoint User Information List. SharePoint uses this list to look up user account information, such as email, picture, user name, for SharePoint columns, such as CreatedBy, ModifiedBy, and Person or Group. This SharePoint User Information List is only visible to site administrators.
Limitations you may encounter
When the Export Tables to SharePoint 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, it is 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. |
|
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. |
|
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. |
|
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. |
Advantages of linking data between Access and SharePoint
After linked SharePoint lists are created, people can work with the lists either on the SharePoint site or in the linked tables 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. The following guidelines and tips can help you take advantage of linked lists between Access and SharePoint and leverage the combination of the two.
Issue Tracking Access has an Issue tracking template that interacts directly with the Issue Tracking list 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.
Retrieve data from the Recycle Bin You can use the Recycle Bin on a SharePoint site to easily view deleted records and recover information that was accidentally deleted.
Quick Launch To view your lists on the SharePoint site, click View All Site Content on the Quick Launch. You may need to refresh the page in your Web browser. To make your lists appear on the Quick Launch on the SharePoint site, you can change the list settings on the SharePoint site. For more information, see Customize the navigation on your SharePoint site.
Track change history In Access, you can set the Append property of a Long Text field to Yes so that Access retains a history of changes to that field. Similarly in SharePoint, you can view version history of a column. For example, you can recover a previous version of a column or track when the change occurred. If you link to a SharePoint list with version history enabled, Access creates a Long Text field with the Append property set to Yes. If you move an Access table that has a Long Text field with its Append property set to Yes, a SharePoint list with version history is created.
To summarize, Access can view historical changes made in SharePoint, and SharePoint can view historical changes made in Access. For more information, see Create or delete a Long Text field and View the version history of an item or file in a list or library.
Work offline You can use Access to work offline with data that is linked to SharePoint lists. This can be helpful if you need to continue working when SharePoint is not available. Once SharePoint becomes available, you can synchronize your changes and easily resolve any conflicts. For more information, see Work offline with tables that are linked to SharePoint lists.
Subscribe to alerts You can subscribe to alerts so that you know when changes are made to list items. You can receive the alerts from email or text messages (SMS). For more information, see Create an alert to get notified when a file or folder changes in SharePoint.
Manage SharePoint list permissions It’s a good idea to check SharePoint permissions on linked lists to make sure you don’t inadvertently give access to confidential or private data. In SharePoint, you can assign varying levels of permission 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 even set permissions on specific list items on a SharePoint site. For more information, see Customize permissions for a SharePoint list or library.
Bulk editing Sometimes you need to make a lot of changes to list data, such as catching up on status fields, adding lots of comments, and bringing data up-to-date. This is called bulk-editing, and it is usually more efficient for you to use Access to make these changes.
Report distribution If you want to use Access to create reports from linked list data, you can broadly distribute these reports as PDF files by exporting them to a SharePoint library. This library can effectively act as a report center because PDF files open in Adobe Acrobat reader for easy reading, paging, and searching. It’s always a good idea to time-stamp a report so people understand when the data was captured.
Use SharePoint to create an Access table You can create an Access table based on a SharePoint list. You might find this a convenient and quick way to create a table with a similar purpose and fields to the Contacts, Tasks, Issues, and Events lists. Access will also create a corresponding UserInfo table. For more information, see the section "Use a SharePoint site to create a table" in Create a table and add fields.
Microsoft Power Platform There are several ways you can tap the functionality in Microsoft Power Platform by using a linked SharePoint list in Access to present information on many devices:
-
Set up Microsoft Power Automate in SharePoint Online, such as approving a request or pushing a notification. For more information, see Create a flow for a list or library in SharePoint Online.
-
Use Microsoft PowerApps to customize a list form and build business apps, such as Line of Business portals and Business Intelligence dashboards. For more information, see Create a PowerApp for a list in SharePoint Online.
Mobile apps You can work with Access data linked to SharePoint lists by using a mobile device. You can view list items and do light editing. For more information, see SharePoint mobile app for Android and SharePoint mobile app for iOS.
See Also
Export a table or query to a SharePoint site
