Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 15.2 Create a SharePoint Data View Web Part Based on Access Data

15.2.1 Problem

FrontPage 2003 makes it easy to extend
SharePoint sites to include data from databases, web services, XML
documents, and other sources and display that data using the Data
View Web Part. You can easily link to SQL Server data and display that
within a Data View Web Part but
it's not clear how you link to data in an Access
database. Is this possible?

15.2.2 Solution

This solution requires FrontPage 2003 and a web server running Windows SharePoint Services.

It's not totally obvious, but by hand-coding an
OLEDB connection string you can
create a Data View Web Part based on an Access database connection.

To create an Access database connection, follow these steps:

1. Startup FrontPage 2003 and either open an existing SharePoint site or
   create a new SharePoint site.

A SharePoint site can only be created on a Windows 2003 Server machine running Windows SharePoint Services.

2. Select Data Insert Data View... to display the FrontPage
   Data Source Catalog task pane.

3. Under the Database Connections section of the Data Source Catalog,
   click the Add to Catalog... hyperlink. FrontPage displays the Data
   Source Properties dialog box.

4. From the Data Source Properties dialog box, click the Configure
   Database Connection... button.

5. At the Configure Database Connection dialog box, select
   "Use custom connection string"
   option, and click on the Edit... button as shown in Figure 15-4.

Figure 15-4. In order to connect to an Access database, you must select the "Use custom connection string" option and click on the Edit button

6. FrontPage presents the Edit Connection String dialog box. At this
   point you are on your ownFrontPage offers absolutely no help
   in building the OLEDB connection string. Fortunately,
   it's not that difficult to create a connection
   string.

7. At the Edit Connection String dialog box, enter a connection string
   that points to the

   15-02.MDB sample database as
   shown in Figure 15-5 and click Next.

   The connection string needs to follow this basic syntax:

           Provider=Microsoft.Jet.OLEDB.4.0;
   Data Source=path_to_database;
   User Id=user_name;Password=password;

   If you are not using a secured database, as in this example, you can
   leave the User Id and Password portions of the connection string out.

Figure 15-5. Enter a Jet connection string into the Edit Connection String dialog box

More than likely, you will need to modify the path to the database shown in Figure 15-5 to reflect the location of the sample database on your computer.

8. At the next page of the dialog, select the name of a tablefor
   this example, select the tblRunners tableand click Finish.

9. FrontPage 2003 returns you to the Data Source Properties dialog box.
   Click on General tab of the dialog box and enter the following name
   into the Name textbox:

   RunnersTable

10. Click OK to dismiss the dialog box and create the connection.

To place a Data View Web Part on a web page that
connects to an Access database connection, follow these steps:

1. Add a new page to the site by selecting File New... On the
   New task pane, click on "More page
   templates..." under New page.

2. Click on the Web Part Pages tab of the Page Templates dialog box,
   select one of the Web Part page templates, and click OK.

3. FrontPage adds a new page to the site containing one or more Web Part
   zones. Click one of the Web Part zones, and select Data
   Insert Data View... to open the Data Source Catalog.

4. Click on the RunnersTable database connection and select Insert Data
   View from the popup menu.

5. FrontPage adds a Data View Web Part to the page.

6. Select File Save to save the new page and select File
   Preview in Browser to display the page in your browser.
   The page should look similar to the one shown in Figure 15-6.

Figure 15-6. This page displays data from an Access database using a Data View Web Part

15.2.3 Discussion

If you look at the HTML behind the Data
View Web Part you will see that the Data View performs its magic
using XML and XSLT. In fact, if you look closely, you will see that
the Data View doesn't copy the data into the page.
Instead, it sets up a link back to the original Access database. This
way, the web page is never out of sync with the data in the database.

While Access works well in small workgroup scenarios, it is not a
good choice if you expect a moderate number (over a dozen or so) of
simultaneous users. In these cases, you'd be better
off moving the data into SQL Server or MSDE.

15.2.4 See Also

See Building XML Data-Driven Web Sites with
FrontPage 2003. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_fp2003_ta/html/odc_fpbldgxmlwebs.asp