This year, however, SharePoint is moving from the wings to center stage. It's been redesigned to use SQL Server and ASP.NET as their foundations, and integrated more closely with other Microsoft software products, including Office 2003. This integration extends to Access 2003, which is able to treat SharePoint as another source of data for import, export, and data link.
Introduction to Windows SharePoint Services 2.0
Windows SharePoint Services (formerly SharePoint Team Services) is Microsoft's solution for working and department quislingism. SharePoint Services allows you to communicate, share documents and work together on projects using nought more than a web browser or any application of Microsoft Office 2003.To make use of SharePoint in Access 2003, you must install the new version of SharePoint Services. Although there are ways to extract data from the original release of SharePoint to Access 2003, the deep integration possible with Access 2003 requires the use of SharePoint Services version 2.0. You will need a server to meet these minimum requirements:
- Windows Server 2003 operating system
- The NTFS file system
- IIS 6.0 installed on your worker process isolation mode default
- ASP.NET installed
If you meet these requirements, the installation of SharePoint Services is easy. SETUPSTS.EXE Just run the file in the Windows SharePoint Services CD-ROM. This installs SharePoint Services on the default Web site for the computer, and sets its own example of SharePoint Microsoft Desktop Engine (MSDE) Microsoft SQL Server (in this case the data set in SharePoint Services). For this article, I used the Beta 2 Technical Refresh version of SharePoint Services and Access 2003.
Figure 1 shows an open process in the SharePoint site in Internet Explorer 2.0. While this is the default to act with the data from SharePoint, which is far from being the only way. In particular, Access 2003 SharePoint seen as a source of first class in its own right. Note the "lists" category in the navigation bar to the left of the window. A SharePoint list is the equivalent of an Access table, which is an orthogonal matrix of data fields and records.
Exporters, importers, and linking data
While Web browsers do not provide ubiquitous access to data in SharePoint, you just provide the best possible user experience. Of course, as Access developers already know is an application program designed for a top-notch user experience when browsing structured data: Access. So it's good to know that Access 2003 SharePoint Services allows you to be treated like any other data source. You can export data from Access to SharePoint, SharePoint data relevant to access, or (perhaps most interesting from the standpoint of collaboration applications) of data link SharePoint for access and data access in real time. I will demonstrate each of these fundamental operations.
First, a warning. I'm using the beta version of Access 2003, so some details may change the final version.
Export data from Access to SharePoint
For starters, here is how you can export a table from Access to SharePoint:
1. Click on a table in the database window and select Export.
2. Select SharePoint Team Services in the Save as type of jazz band (presumably this will change to "Windows SharePoint Services" in the final version of Access 2003). This opens the Export to Windows SharePoint Services Wizard.
3. Enter the URL of the SharePoint site, where the exported data should reside. You can also specify a list of name and description, by default, they are populated with the name of the table access and description. Click Finish.
Figure 2 shows the exported table as a SharePoint list. As you can see, SharePoint exposes a very rich user interface for working with list data (for a rich web application, in any case).
Figure 2 Import data from SharePoint to access
Of course, if you can export, you can import. Import data from SharePoint to access is a bit more complicated than it exported, but still relatively simple:
1. Select File | Get External Data | Import.
2. Select SharePoint Team Services in the Files of type combo box (again, hopefully this will change for the release version). This will open the import of Windows SharePoint Services Wizard.
3. Enter the URL of the SharePoint site that contains the data to import and click Next.
4. In the next wizard panel, you can choose to import the lists and views. A view is similar to an Access query: That it is a subset of a list (eg "My Tasks" is a view of the standard task list). You can use Ctrl + click to select multiple lists to import.
5. It is also necessary to select which columns to search. The check box "Retrieve IDs lookup columns" is somewhat confusing. If you uncheck the box, then any lookup columns in the data are filled with data from the other list. For example, the task list has some lookup columns to get names from the list of UserInfo. If you uncheck this box, then imported the Access table will contain the actual names instead of numeric identifiers. If you check the box, numeric identifiers will be imported.
6. If you choose to recover IDs, the next step wizard will help you decide what to do with lists. You can choose to create linked tables pointing to these lists, or import into their own tables in Access.
7. The final panel of the wizard to confirm your choices. Click Finish to import the table or tables that you chose.
When you import data from SharePoint to Access, Access will import as field properties until you can. For example, if a SharePoint list contains a currency column, the import process will create a currency field in Access. Access will also inherit that required, the default, Format and Decimal Places settings SharePoint column (remember that it is MSDE / SQL Server that is storing data in SharePoint, so there are a lot of overlap with the Jet tables). Moreover, access is going to build a validation rule to enforce minimum and maximum values set in SharePoint.
Linking SharePoint data for access
Finally, you can link access to SharePoint data. This has the usual advantage of related data: It lets you keep data stored in SharePoint, and still work with it in Access. Linking works and import, unless you can only link (not import) related to the tables. Access uses a stylized calendar icon to represent a linked list of SharePoint. Figure 3 shows the window of the database with two linked lists in SharePoint.
Figure 3 Microsoft supports editing of data simultaneously access and SharePoint with a pessimistic locking scheme. If users change the same record in both environments, while the second copy to be saved will cause an error message. On the SharePoint side, the error appears as a Web page that tells the user that someone has changed the topic and providing the opportunity to save your data on top of changes. On the side of access, users get the dialog box Create Family Conflict if they try to overwrite changes made by another user.
The result of this coordination is that in some circumstances, SharePoint lists offer an ideal way to extend the reach of an Access database to work with users who have no access. For example, suppose you have field representatives that have to be able to view a table of parts that have been stored in an Access database. Instead of writing your own web interface to data, you can export the table to a SharePoint site on a server with Internet connection and link from the list back to your Access database.
Programming SharePoint access
traditional access points has been that all I could do from the user port could also be done from VBA code. That is still true when working with SharePoint, but as I will show in a moment, there is no branching rare. As with other data transfer operations, DoCmd.TransferDatabase method is used to move data to and from SharePoint programming. To export a table called orders to a call list OrdersList SharePoint, you can use a command like this:
DoCmd.TransferDatabase acExport, "WSS". _
"http://Server/sites/testsite", acTable, "Orders", _
"OrdersList", False
The final parameter controls whether to export only the structure of the structure, or export of both the structure and table data.
The significance or link a SharePoint list, you can also use the method TransferDatabase, but this is where the branch comes in. Here's a VBA command to link a SharePoint list named part
DoCmd.TransferDatabase acLink, "WSS", _
"WSS;HDR=NO;IMEX=2;" & _
"DATABASE=http://Server/sites/testsite;" & _
"LIST={800BE2B7-FA3C-4CFC-BBB3-8500C4EDCF22};" & _
"VIEW=;RetrieveIds=Yes;TABLE=Parts", acTable, , _
"Parts"
Ugly, huh? The problem is that SharePoint does not refer to the lists by name. GUID uses SharePoint internally to identify the lists (which is how they can store, for example, 10 different lists of contacts in the same database MSDE). When using the Access user interface to import or link SharePoint data, access is responsible for matching the names of the GUID. But when you're dealing with the Jet engine directly through VBA, you're on your own. The long chain (starting with "WSS" and ends with "TABLE = Parts") identifies a particular SharePoint list.
If you're faced with the problem of programming to import or link data from SharePoint, there are two ways to get the correct GUID. The easy way is to manually link the wanted list to a Access database and open the linked table in design view, the Description property of the linked table will contain the link chain requirement. Obviously, this is less useful when you need to be able to link to arbitrary lists, however. In that case, the interface can use the SharePoint Web service method Lists.GetList expurgated one. This method takes the name from a list at a given location and returns the detailed information, including the GUID that identifies the list.
0 comments:
Post a Comment