Using Data from SharePoint Lists


Before you start using Access with SharePoint, you need to have a SharePoint instance available to you. If you do not have an instance of SharePoint available, you can download a trial version from Microsoft. At the time of writing, you could download a trial version from http://office.microsoft.com/en-us/sharepointserver/FX100492001033.aspx. As one further caveat, SharePoint can only be installed on Microsoft Windows Server 2003 Service Pack 1 or later. If you prefer not to host the SharePoint software yourself, there are many Internet hosting companies that offer SharePoint hosting on their servers for a small monthly fee. One example of such a company is 1&1 Internet (www.1and1.com). Some of these Internet hosting companies offer a free trial.

Now that you know what a SharePoint List is and what the different types are, it is time to start working with them.

Linking and Importing SharePoint Lists

Importing a SharePoint list is very similar to importing data from other external sources, as described in Chapter 7. There are two ways to import a SharePoint list: using the Access menus and through VBA. This section will demonstrate both.

Try It Out-Importing Lists Using the Wizard

image from book
  1. Create a new database by selecting the Office Button image from book 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.

    image from book
    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.

    image from book
    Figure 8-8

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

    image from book
    Figure 8-9

    image from book
    Figure 8-11

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

    image from book
    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

Parameter

Description

TransferType

Type of transfer to be performed. Valid choices are acImportSharePointList and acLinkSharePointList.

SiteAddress

The fully qualified URL of the SharePoint instance you are connecting to.

ListID

The ID of the list you are going to link to or import.

ViewID

Optional. The ID of the SharePoint view you would like to use when the list is imported or linked.

TableName

Optional. Name of the table as it will be seen within Access.

GetLookupDisplayValues

Optional. Whether to get the associated lookup values when attaching the data or use the IDs. The default is True.

image from book

Try It Out-Programmatically Linking a List Using DoCmd Object

image from book

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 ( - ).

    image from book
    Figure 8-12

    image from book
    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.

    image from book
    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.

image from book

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

image from book

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

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

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

  3. 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.)

    image from book
    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.

image from book
Figure 8-16

image from book




Beginning Access 2007 VBA
Beginning Access 2007 VBA
ISBN: 0470046848
EAN: 2147483647
Year: 2004
Pages: 143

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net