Access Cookbook - Ken Getz [300]
TIP
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.
TIP
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 string.
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
TIP
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:
RunnersTable
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
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