Linking to SharePoint Lists - MS Access

In this section, you will look at linking Access to SharePoint. For example and testing purposes, use the demonstration database WebDatabase.accdb, which is a Web Legal Access Web Database that can be published to Access Data Services

. If you want to try this yourself and you don’t have the facilities to publish Access Web Database, you can follow along using a version of SharePoint, which has some lists of data to which you can link. In this example, you have a standard Access .accdb file, and you want to link this database to SharePoint Lists.

In the example, these SharePoint Lists are actually an Access Web Database which has been published. Using a new Access database (not a web database) which contains only our linker software components, go to External Data | Import & Link | More | SharePoint List. In the dialog box that opens, choose the option to Link to data by using a URL similar, for example, to the following:

http://xxx.accesshosting.com/NorthWind, and then log on. You are then presented with a list of available SharePoint Lists, as shown in Figure.

The Available SharePoint Lists on the site.

The Available SharePoint Lists on the site.

This then provides updateable lists of the data on the SharePoint site, as shown in Figure.

Updateable links to the lists in SharePoint.

Updateable links to the lists in SharePoint.

Looking at the Connect property of the TableDefs, you see a connection string similar to the following example:

Relinking SharePoint Lists

As with other application links, you need a method to relink the SharePoint Lists if you switch between a development and test or production environment. The key to understanding how this is achieved is in recognizing that the long code LIST={857C11A0-CE1D- 420E-A774-018D03571D1E} needs to be changed. If you can change these, then you can relink to the alternative site. You might also notice that the Linked Table Manager is not available for relinking these links.

You will need an application linked to Site1, and then an application linked to Site2. If you then scan the TableDefs collection for an application linked to each of the sites, all you need is some program code to update the LIST information in the target application to be relinked, and then refresh the links. The process is shown in Figure.

The Site2 application uses the table in Site1 to update the LIST values. It then repoints the application at the new SharePoint Site.

The Site2 application uses the table in Site1 to update the LIST values. It then repoints the application at the new SharePoint Site.

In the previous example, we published our sample web database to Site1 and linked a new standard Access database to the SharePoint Lists in Site1. To follow along here, you need to publish a second copy of the web database to Site2, and link a new standard Access database to it, as well. The following refers to the two standard Access databases, now linked to Site1 and Site2.

Because our relinker will get this information, we run the relinker in two applications that populates the local table ztblLinkedTable. Figure demonstrates in Site2, that you have also linked to the table ztblLinkedTable in Site1 (ztblLinkedTable1).

Showing the LIST settings in the current site and the values you need to change to connect to the second site.

Showing the LIST settings in the current site and the values you need to change to connect to the second site.

You then run the following SQL, which updates the relinker table in the active site to have the correct values for the second site:

Figure illustrates how the relinker table is now updated.

Showing the LIST settings in the current site and the values that need to change to connect to the second site.

Showing the LIST settings in the current site and the values that need to change to connect to the second site.

The last remaining step is to edit the information shown in Figure for the Production- Path so that it refers to Northwind, rather than Northwind2, which is the test system.

Changing the production path in preparation to relink the tables.

Changing the production path in preparation to relink the tables

Then you simply run the relinker and press the button to relink the application to the production system.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

MS Access Topics