Recipe 15.2 Create a SharePoint Data View Web Part Based on Access Data
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?
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:
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.
Select Data Insert Data View... to display the FrontPage
Data Source Catalog task pane.
Under the Database Connections section of the Data Source Catalog,
click the Add to Catalog... hyperlink. FrontPage displays the Data
Source Properties dialog box.
From the Data Source Properties dialog box, click the Configure
Database Connection... button.
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
FrontPage presents the Edit Connection String dialog box. At this
point you are on your own—FrontPage offers absolutely no help
in building the OLEDB connection string. Fortunately,
it's not that difficult to create a connection
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:
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.
At the next page of the dialog, select the name of a table—for
this example, select the tblRunners table—and click Finish.
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:
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:
Add a new page to the site by selecting File New... On the
New task pane, click on "More page
templates..." under New page.
Click on the Web Part Pages tab of the Page Templates dialog box,
select one of the Web Part page templates, and click OK.
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.
Click on the RunnersTable database connection and select Insert Data
View from the popup menu.
FrontPage adds a Data View Web Part to the page.
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
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