Advertisements

Import, link, or move data to SharePoint

Applies to: Access for Microsoft 365, Access 2019, SharePoint Online, Access 2016, Access 2013, Access 2010, Access 2007.

Want to merge and advance both Access and SharePoint? To do so, you can import, link, or transfer data between them. Importing creates a copy of the SharePoint list in an Access database. Linking connects to data in another programme, so that you can monitor and alter the latest data both in SharePoint and Access. Moving designs lists on the SharePoint site that stay linked to tables in your database and safeguards 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

Link to a SharePoint list

Move data to SharePoint

Advantages of linking data between Access and SharePoint

Publish a database by using Access Services

Import a SharePoint list

Once you import data, Access makes a table and imports the columns and items from the source list (or view) into that table as fields and records. During the import operation, you can set the lists that you want to copy, and, for each chosen list, you can determine whether you want to import the whole list or only a specific view. At the end of the import operation, you can select to save the details of the import operation as a specification. An import specification gives you the benefit of repeating the import operation in the future without going through the Import Wizard every 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, since you no longer need the information on your SharePoint site. You can import the list into Access, and then remove the list from the SharePoint site.

  • Your department or workgroup uses Access, but you are infrequently pointed to a SharePoint list for additional data that is required to be combined into one of your databases.

Complete these steps before you import the list

  1. Find the SharePoint site that includes the lists that you want to copy, and record the site address. A valid site address begins with http:// or https:// followed by the name of the server, and ends with the path to the specific site on the server.

  1. Capture the lists that you want to copy to the database, and then deliberate over whether you want the full list or only a specific view. You can import several lists in a single import operation, but you can import only one view of each list. If preferred, design a view that has merely the columns and items that interest you.

  1. Check the columns in the source list or view. The following table explains some considerations to keep in mind when importing different elements:
ElementConsiderations
ColumnsAccess 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.
FoldersEach 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 columnsIf 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 columnsThe 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.
AttachmentsThe attachment column of the list is copied to a field named Attachments.
Multivalued columnsA 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 formattingColumns 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.
RelationshipsAccess 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.
  1. Establish the database into which you aim to import the lists. Know for certain that you have the required permissions to create data to the database. If you don’t want to store the data in any of your existing databases, produce a blank database.

  1. Review the tables in the database. The import operation makes 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 generate Contacts2, and so on.

Import the list

  1. Enter 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.

  1. The location of the import/link text wizard changes minimally depending upon your version of Access. Choose the steps that match your Access version:

  • If you’re using Microsoft 365 or Access 2019, on the External Data tab, in the Import & Link group, pick New Data Source > From Online Services > SharePoint List.
  • If you’re using Access 2016, Access 2013, or Access 2010, on the External Data tab, in the Import & Link group, pick the More button to drop down a list of options and then select SharePoint List.

  1. Access launches the Get External Data – SharePoint Site dialogue box.Select to import or link to a SharePoint site on the Get External Data - SharePoint Site dialog box.
  2. In the wizard, state the address of the source site.

  1. Choose the Import the source data into a new table in the current database option, and press Next.

  1. From the list that the wizard displays, confirm the lists that you want to import.

Note – You can link to SharePoint libraries, but you can only add documents in SharePoint.

  1. In the Items to Import column, choose the view that you want for each selected list.

  1. The checkbox labelled Import display values instead of IDs for fields that look up values stored in another list dictates 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, click the checkbox. In this case, the field will not look up another table for values.
  • For the destination field to look up another table for values, empty the checkbox. Doing this will relay the IDs of the display value rows to the destination field. The IDs are essential for defining a lookup field in Access. Whilst importing IDs, you must import the lists that presently provide the values to the lookup columns (unless the destination database already has tables that could act as lookup tables). The import operation arranges the IDs in the corresponding field, but it does not set all of the properties required 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.

  1. Press OK.

Access imports the lists, and then shows the status of the operation on the last page of the wizard. If you plan to repeat the import operation further down the line, you can save the details as an import specification. Access avoids replacing a table in the database as part of an import operation, and you are unable to insert the contents of a list or a view to an existing table.

What else should I know about importing?

  • 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.

Related tasks on importing

As soon as you have done the import operation, contemplate running some further tasks:

Verify the data types  –  Access picks the relevant data type for every field corresponding to a source column. Confirm each field and its settings to ensure each of the fields are set up in your preferred format.

Investigate additional fields  –  According to the type of list on which the table is based, you may even notice the addition in the table of a few extra fields (such as Title, Modified, or CreatedBy). If these fields serve no purpose to you in the Access database, you can safely erase them.

Top of Page

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 set changes to data either by browsing to the SharePoint site or by working in Datasheet or Form view within Access. The changes that you apply in one location are corresponded in the other. But, if you wish to apply structural changes, such as removing or changing a column, you need to do so by accessing the list on the SharePoint site. You cannot add, delete, or modify the fields in a linked table while working in Access.

  • Lookup tables  –  After you link to a SharePoint list, Access promptly forms linked tables for all lookup lists (unless the lookup lists are already linked to the database). If the lookup lists include columns that lookup other lists, those lists are also contained in the linking operation, so that the lookup list of every linked table has a related linked table in the database. Access also forges 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 design lists to track various things (such as contacts and issues), but usually this list data must be carried into a database for aggregation and reporting. Linking is the suitable choice, given it enables users of both the SharePoint site and the database to create and revise data, and to always see and work with the latest data.

  • You are an Access user who only recently started using SharePoint. You migrated a number of your databases to your team SharePoint site, and most of the tables in these databases are linked tables. From hereon in, rather than making local tables, you will generate SharePoint lists, and then link to these lists from your databases.

  • You want to carry on storing your lists on SharePoint sites, but you also want to work with the latest data inside of Access to run queries and print reports.

Prepare to link to a SharePoint list

  1. Find 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 begins with http:// or https:// followed by the name of the server, and ends with the path to the specific site on the server.

  1. Ascertain the lists to which you want to link. You can link to numerous lists in a single linking operation, but you cannot link to surveys, discussions, or a specific view of any list.

  1. Inspect the columns in the source list. The following table explains some considerations to keep in mind when linking to different elements.
ElementConsiderations
ColumnsAccess 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.
FoldersEach 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 columnsIf 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 columnsThe results in a calculated column are displayed in the corresponding field, but you are not able to view or modify the formula in Access.
AttachmentsThe attachment column of the list is displayed as a field named Attachments.
Read-only columnsThe 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 columnsA 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.

  1. Decipher the database in which you want to create the linked tables. Make sure that you have the essential permissions to create data to the database. If you don’t want to store the data in any of your existing databases, create a new, blank database.

  1. Check the tables in the database. Once 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 apply “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 are enforced for related lists.

Link to the data

  1. Enter the destination database.

  1. The location of the import/link text wizard varies a little based upon your version of Access. Select the steps that match your Access version:

  • If you’re using Microsoft 365 or Access 2019, on the External Data tab, in the Import & Link group, choose New Data Source > From Online Services > SharePoint List.

  • If you’re using Access 2016, Access 2013, or Access 2010, on the External Data tab, in the Import & Link group, pick the More button to drop-down a list of options and then select SharePoint List.

  1. Access enters the Get External Data – SharePoint Site dialogue box.Select to import or link to a SharePoint site on the Get External Data - SharePoint Site dialog box.
  2. In the wizard, input the address of the source site.

  1. Pick Link to the data source by creating a linked table, and then select Next. The wizard presents the lists that are available for linking.

  1. Choose the lists that you want to link to, and then press OK.

Note:

If some lists are already linked to the existing database, the checkboxes corresponding to those lists will be chosen. If you want to delete any links, clear the checkboxes of the links you want to delete. Access attempts to design linked tables, both for the lists that you picked during this operation and for each of the related lists. Also, Access tries to refresh the linked tables corresponding to the lists that were clicked in the wizard. Access also makes the relationships between the tables. Unlike an import operation, a linking operation sustains the lookup property settings between a lookup field and the related table. You are not required to manually assign the properties of the lookup field in table Design view.

  1. Review the new linked tables in Datasheet view. Check that each of the fields and records are presented correctly. Access picks the correct data type for each field that relates to a source column. It is significant to remember that each time you enter either a linked table or the source list, you would view the newest data showcased in it. But, structural changes made to a list are not instantly reflected in a linked table. To amend a linked table by setting the most recent list structure, right-click the table in the Navigation Pane, aim at More Options, and then choose Refresh List.

Note:

Access never substitutes a table in the database as part of a link operation. Also, you are unable to add the contents of a SharePoint list to an existing table.

Top of Page

Move data to SharePoint

Moving data to SharePoint is a way to proficiently design a backend database, but in this case, the data is enclosed in SharePoint lists. The front-end is still an Access database, and you can release it in a similar way as a split database. Once possible, the Export Tables to SharePoint Wizard moves data to lists that are linked to list templates on the SharePoint site, such as a Contacts list. If a table can’t be matched to a list template, the table transforms to a custom list on the SharePoint site. According to the size of the database, its number of objects, and system performance, the operation can happen in a fair amount of time. If you decide to reconsider during the process, you can select Stop to cancel it.

The wizard forms a backup copy of the database on your computer. In Access, it generates links to the lists from the tables, so that it is simple to find the data on the SharePoint site once 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 employ to help troubleshoot.

Tip:

Try designing a separate SharePoint site to keep the lists in a self-contained location.

Note:

In Access 2007, the Export Tables to SharePoint Wizard is called the Move to SharePoint Site Wizard and it doesn’t create referential integrity on the SharePoint lists.

Use the Export Tables to SharePoint Wizard

  1. On the Database Tools tab, in the Move Data group, select SharePoint. This option is only available if your database is saved in the .accdb file format.

  1. Follow the steps in the Export Tables to SharePoint Wizard, including stating the location of your SharePoint site. To cancel the process, press Stop.

  1. On the last page of the wizard, choose the Show Details checkbox to notice more details about the migration. This wizard page summarises which tables have been linked to lists and offers information about a backup location and the URL for your database. It even offers a warning if some migration issues were experienced and supplies the area of a log table where you can view further details about the issues.

  1. Choose Finish once the wizard finishes its actions. If the wizard presents a warning, you should check the log table and implement any essential actions to secure a successful migration of your data. For example, certain fields may remain static or may be transformed to a different data type that is associated 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

After the Export Tables to SharePoint Wizard finishes, you notice a message if Access endured any issues with the data. Access forms a log table called Move to SharePoint Site Issues and appends the table to the database. The Move to SharePoint Site Issues table is retained 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, often when Access and SharePoint don’t share the similar feature or, in some cases, don’t share a data type. For example, if your Access table facilitates referential integrity, it is enforced in the list on the SharePoint site. The information in the following table can assist you with choosing whether to migrate your data, and it could be helpful if you are reviewing any issues reported in the Move to SharePoint Site Issues table.

Type of data or issueIssueResult
COM object data typeSharePoint sites do not support the COM Object data type.Field is not moved.
Binary data typeSharePoint sites do not support the Binary data type.Field is not moved.
DateSharePoint sites do not support dates prior to 1900.Data with dates prior to 1900 is not moved.
New line characters in text fieldsSharePoint 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 typeSharePoint sites do not support the Decimal data type.The Number field or Double Integer field is used instead.
Replication ID data typeSharePoint 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 listSharePoint 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 fieldsSharePoint 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 createdSome 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

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 type the data by using a table or a form in Access or by editing the list on the SharePoint site. The listed guidelines and tips can aid you to fully benefit from linked lists between Access and SharePoint and leverage the mixture of the two.

Issue Tracking  –  Access has an Issue tracking template that engages directly with the Issue Tracking list on a SharePoint site. The schemas are identical, and the Access solution can be utilised 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 quickly check removed records and regain information that was mistakenly deleted.

Quick Launch  –  To see your lists on the SharePoint site, select View All Site Content on the Quick Launch. You might have to refresh the page in your Web browser. To allow your lists to display on the Quick Launch on the SharePoint site, you can edit the list settings on the SharePoint site. For more information, see Customise the navigation on your SharePoint site.

Track change history  –  In Access, you can amend the Append property of a Long Text field to Yes so that Access saves a history of changes to that field. Similarly in SharePoint, you can monitor 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 generates 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 formed.

To summarise, Access can see historical changes made in SharePoint, and SharePoint can track 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 useful if you must continue working when SharePoint is not available. After SharePoint becomes available, you can synchronise your revisions and rapidly 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 enabling you to know when changes are applied to list items. You can obtain the alerts from e-mail 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 best practice to check SharePoint permissions on linked lists to guarantee you don’t inadvertently disclose access to confidential or private data. In SharePoint, you can delegate contrasting degrees of permission and you can customise which users you allow or deny access to. If you must restrict access to only a few sensitive items in a database, you can also format permissions on particular list items on a SharePoint site. For more information, see Customise permissions for a SharePoint list or library.

Bulk editing  –  Typically you have to impose multiple changes to list data, such as catching up on status fields, inserting many comments, and revising data to the latest details. This is known as bulk-editing, and it is often more efficient for you to use Access to confirm these changes.

Report distribution  –  If you want to use Access to formulate reports from linked list data, you can widely disseminate these reports as PDF files by exporting them to a SharePoint library. This library can profoundly behave as a report centre because PDF files launch in Adobe Acrobat reader for quick reading, paging, and searching. It’s persistently a helpful idea to time-stamp a report so people acknowledge when the data was collected.

Use SharePoint to create an Access table  –  You can make an Access table associated to a SharePoint list. You may find this a clever and swift way to produce a table with a related purpose and fields to the Contacts, Tasks, Issues, and Events lists. Access will even generate a matching 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 numerous ways you can tap the functionality in Microsoft Power Platform by using a linked SharePoint list in Access to display information on many devices:

Mobile apps  –  You can cooperate with Access data linked to SharePoint lists by relying on a mobile device. You can monitor list items and do minimal editing. For more information, see SharePoint mobile app for Android and SharePoint mobile app for iOS.

Top of Page

Publish a database by using Access Services


Important    Microsoft no longer recommends creating and using Access web apps in SharePoint. As an alternative, consider using Microsoft Power Apps to build no-code business solutions for the web and mobile devices.

Once you publish a web database, Access Services generates a SharePoint site with the database. Every database object and data transfer to SharePoint lists in that site.

After you publish a database, you send it to the Web. You can design Web forms and reports that operate in a browser window, and can even form standard Access objects (sometimes called “client” objects to distinguish them from Web objects). You must have Access installed on your computer to use client Access objects, but all database objects on SharePoint are shared.

Note: When you have Access installed in your computer, you can use the client objects from a Web database otherwise you can only use the Web database objects.

Access Services supplies a platform for you to add databases that you can use on the Web. You create and publish a web database by using Access 2010 and SharePoint, and people use the web database in a Web browser.

Note: You would need Designer permissions on the SharePoint site where you want to publish the database.

Forms, reports, and UI macros function inside the browser.

Data is kept in SharePoint lists if you’re using a web database: All tables are converted into SharePoint lists, and records evolve into list items and you can use SharePoint permissions to manage access to your web database.

Queries and data macros function on the server: All SQL processing occurs on the server. This aids enhanced network performance by controlling overall traffic to result sets.

Top of Page

See Also

Export a table or query to a SharePoint site

Ways to share an Access desktop database

Deploy an Access application

Advertisements

Leave a Reply

%d bloggers like this: