Introduction to SharePoint Lists

1. Create a new database by selecting the Office Button New. From the list of available templates, select "Blank Database," provide the file name and path , and press the Create button.

2. Click on the External Data tab and click on the SharePoint List Button, as shown Figure 8-7.

   
   Figure 8-7

3. The Get External Data – SharePoint Site Wizard will pop up, as shown in Figure 8-8. Specify the URL of your SharePoint site and select the Import the source data into a new table in the current database option, then click the Next button.

   
   Figure 8-8

4. The next step in the wizard is selecting which lists you want to import. Select all the lists, as shown in Figure 8-9. Click the OK button, and the tables will be imported into your database. Your database should look similar to Figure 8-11 when you are finished.

   
   Figure 8-9

   
   Figure 8-11

5. After the import is completed you are given the option to save the actions you just performed so that you can perform them again in the future as shown in Figure 8-10. Click the close button and you are finished.

   
   Figure 8-10

As shown in Figure 8-11, now that the tables are imported you can open each of them and compare the data in them to the lists on the SharePoint site to ensure that they are the same. There are times when importing makes sense; for example, when you want to perform some kind of data mining that looks back through the history of a list. However, it is more likely that you will want to simply link to a SharePoint list. Remember from Chapter 7 that with linked tables you maintain a connection to the data source (the list), and any changes you make to data in the linked table will be moved to the original data source.

There are two different ways to programmatically link to SharePoint lists within Access. The first is the TransferSharePointList method of the DoCmd object. The basic syntax of the Transfer SharePointList method is:

DoCmd.TransferSharePointList TransferType, SiteAddress, ListID, [ViewID], [TableName], [GetLookupDisplayValues]

Various parameters are used to specify how the method should execute. The following table explains the use of each parameter.

Open table as spreadsheet

Try It Out-Programmatically Linking a List Using DoCmd Object

Let’s look at an example. Suppose that you want to link to the Contacts list in your SharePoint instance and you want the linked table to be name LinkedContacts .

1. Insert a new module into the database you created earlier.

2. Add the following code to the module

   Public Sub LinkToContacts() Dim strSiteAddress As String Dim strListID As String Dim strTableName As String strSiteAddress = "
   
   https
   
   ://SharePointServer/sites/Site" strListID = "{}" strTableName = "LinkedContacts" DoCmd.TransferSharePointList acLinkSharePointList, strSiteAddress, _ strListID, , strTableName End Sub
   

3. Modify the value of strSiteAddress and strListID to your SharePoint instance and the ID of the Contacts list. To find the ID of the list you want use in SharePoint, open the list in SharePoint and click on the Modify Columns and Settings link (highlighted in Figure 8-12). In the Address Bar of your browser will see a URL similar to Figure 8-13. The part that comes directly after the List= portion is the ID you should use. Finally, in some cases the URL will be encoded, and you will need to decode it, which simply means replacing the characters %7B with a left brace ( { ), %7D with a right brace ( } ), and %2D with a hyphen ( - ).

   
   Figure 8-12

   
   Figure 8-13

4. From the Immediate Window in the Visual Basic Editor, type LinkToContacts and press Enter to run the procedure.

5. Open the Database Window, and you should see a screen similar to Figure 8-14.

   
   Figure 8-14

How It Works

In this example, you used the TransferSharePointList method to link to the Contact list of your SharePoint instance. The parameters of the TransferSharePointList method specified the various bits of information Access needed to perform the import:

Public Sub LinkToContacts() Dim strSiteAddress As String Dim strListID As String

Dim strTableName As String strSiteAddress = "https://SharePointServer/sites/Site" strListID = "{}" strTableName = "LinkedContacts" DoCmd.TransferSharePointList acLinkSharePointList, strSiteAddress, _ strListID, , strTableName End Sub

After you ran the procedure, you should have noticed in the Database Window that the new table was inserted into your database. You may have noticed an additional table that was linked that you hadn’t specified, the UserInfo1 table (as shown in Figure 8-14). This table was automatically linked because it had a relationship to the Contact list.

If you had wanted to import the table directly, instead of linking to it, you could have specified acImportSharePointList instead of acLinkSharePointList , which would have resulted in the Contact list being imported into the database as table LinkedContacts (along with the UserInfo1 table).

The second method of linking to SharePoint lists is a bit more advanced but just as effective. It involves creating a new TableDef object with the SharePoint list as its source. You then append the new TableDef object to the current collection of TableDefs.

Try It Out-Programmatically Linking a List Using a TableDef Object

In this example, we will link to the Announcements list in SharePoint. In our database we shall name it LinkedAnnouncements .

1. Open the same module you created earlier.

2. Add the following code to the module:

   Public Sub LinkToAnnouncements() 'declare the TableDef object we will use Dim tdAnnouncements As TableDef 'create the TableDef object, naming the table LinkedAnnouncements Set tdAnnouncements = CurrentDb.CreateTableDef("LinkedAnnouncements") 'set the DSN to SharePoint site and correct List ID tdAnnouncements.Connect = "WSS;HDR=NO;IMEX=2;ACCDB=YES;" & _ "DATABASE=https://SharePointSite/sites/Site;" & _ "LIST={};" & _ "VIEW=;RetrieveIds=Yes" 'Set the source table name tdAnnouncements.SourceTableName = "Announcements" 'include it in the current list of this DB's TableDefs CurrentDb.TableDefs.Append tdAnnouncements End Sub
   

3. This method of linking to tables requires that we use a DSN. Although it looks quite a bit different than other DSNs it holds the same basic information. Replace the value of the DATABASE portion to your SharePoint instance and replace the value for List with the correct ID from your Announcement list.

4. From the Immediate Window in the Visual Basic Editor, type LinkToAnnouncements and press Enter to run the procedure.

5. Open the Database Window, and you should see a screen similar to Figure 8-15. (It may take a few minutes for the table to appear as Access refreshes its table definitions and attaches to the data source.)

   
   Figure 8-15

How It Works

In this example, you created a TableDef to connect to the SharePoint instance. The first thing you need to do is to create a TableDef object to use. The two code bits below do just that. The first code line declares an object of type TableDef . The second code line instantiates that variable and sets the name to LinkedAnnouncements.

'declare the TableDef object we will use Dim tdAnnouncements As TableDef 'create the TableDef object, naming the table LinkedAnnouncements Set tdAnnouncements = CurrentDb.CreateTableDef("LinkedAnnouncements")

The next thing to do is to tell the TableDef object where to get the object. This is done by setting the Connect property of the TableDef object to the DSN of the Announcement list. The SharePoint DSN looks a bit like other types of DSNs discussed in earlier chapters but with some differences.

'set the DSN to SharePoint site and correct List ID tdAnnouncements.Connect = "WSS;HDR=NO;IMEX=2;ACCDB=YES;" & _ "DATABASE=https://SharePointSite/sites/Site;" & _ "LIST={};" & _ "VIEW=;RetrieveIds=Yes"

Note that the DSN for this example begins with WSS , which indicates that a SharePoint instance will be used. The DATABASE portion is the fully qualified URL to the SharePoint site. The LIST portion is the ID of the list to connect to. The last bit of the DSN is some options relating to using a view and whether to retrieve lookup IDs or not.

Next, the SourceTableName property is set to the name of the list:

'Set the source table name tdAnnouncements.SourceTableName = "Announcements"

Finally, we append the new TableDef object to the current database’s collection of TableDefs . This action is what initiates the link between the Access database and SharePoint, thus making it appear in the Database Window.

'include it in the current list of this DB's TableDefs CurrentDb.TableDefs.Append tdAnnouncements

The previous two examples accomplish the same thing in that both attach a SharePoint list to your database. The first is easier than the second, so why would you want to use the second? Imagine a scenario where you did not want to keep the link open, but rather you wanted to connect briefly to insert some entries and then disconnect. The second method makes this a fairly painless process. Consider the following example:

Public Sub InsertAnnouncements() 'declare the TableDef object we will use Dim tdAnnouncements As TableDef 'create the TableDef object, naming the table LinkedAnnouncements Set tdAnnouncements = CurrentDb.CreateTableDef("LinkedAnnouncements") 'set the DSN to SharePoint site and correct List ID tdAnnouncements.Connect = "WSS;HDR=NO;IMEX=2;ACCDB=YES;" & _ "DATABASE=https://SharePointSite/sites/Site;" & _ "LIST={};" & _ "VIEW=;RetrieveIds=Yes" 'Set the source table name tdAnnouncements.SourceTableName = "Announcements" 'include it in the current list of this DB's TableDefs CurrentDb.TableDefs.Append tdAnnouncements 'create the sql to create an entry Dim sql As String sql = "INSERT INTO LinkedAnnouncements ( Title, Body, Expires )"

sql = sql & " VALUES ( 'Access 2007 Released!', " sql = sql & "'Access 2007 has been released...', '2/1/2007')" 'execute the sql DoCmd.RunSQL sql 'now we detatch the linked table, we do not need it anymore CurrentDb.TableDefs.Delete "LinkedAnnouncements" End Sub

The first part of this method is exactly the same as the second example. You will notice though that once the table has been attached we insert a new announcement and then remove the linked table from the database. If you open the target SharePoint instance, you will see that the new entry is there, as shown in Figure 8-16.

Figure 8-16