Big Data

Get IT Done: Building database Web solutions with Access 2000

Use Access 2000 to build database Web solutions


Microsoft Access 2000 introduces a new collection that dramatically increases the ease with which Webmasters and database administrators (DBAs) can provide databases as part of their Web solutions. Data access pages are similar to traditional Access forms and reports, but they also work over the Web. Furthermore, graphical design techniques make it easy to build solutions without code by simply dragging controls on a Web page. It’s also easy to enhance the built-in data binding of data access pages with Office Web components, which allow you to add spreadsheet, charting, and pivot table functionality to Web pages. Advanced and intermediate developers can use VBA, VBScript, JavaScript, and DHTML to enhance the basic features exposed through graphical design techniques.

In this article, we’ll offer a quick overview of what data access pages are, how you use them, and the specific purposes they serve.

Getting started with data access pages
A data access page is an .htm file that supports automatic data binding. Data access pages implement data binding through the Microsoft Office Data Source Control (one of the Office Web components). There are three types of data access pages:
  1. One that supports basic browsing, editing, adding, and deletion of records.
  2. Another that supports interactive reporting.
  3. By adding Office Web components, such as spreadsheets, charts, and pivot table lists, Webmasters and DBAs can create dynamic data analyses that are accessible Web pages. Users can filter and sort the data locally without affecting the original source data on these types of pages.

The Access 2000 Database window displays three tools for creating data access pages. You can view these options by selecting Pages. The first way to create a data access page is to use the Design view by clicking Create Data Access Page In Design View. This process is similar to creating a form or report in Design view. After specifying a data source for the page, drag controls onto a page. You can manually promote some fields above others to reflect one to many relationships. Switch to Page View to see how the page looks in a Web browser.

A second way to create a data access page is with a wizard. Open the Page Wizard by clicking Create Data Access Page By Using Wizard. Pick a table or query for the page, then choose fields from within that database object. You can choose multiple tables and queries for a single page. If there is a hierarchical relationship to the fields that you select, the wizard automatically reflects it by grouping the lower level fields within the higher one(s).

The third option for building data access pages utilizes existing Web pages. Clicking Edit Web Page That Already Exists adds a Microsoft Office Data Source Control to a standard Web page. This option allows a Webmaster or DBA to utilize a previously formatted page. The analyst can concentrate on arranging data and specifying computations rather than formatting issues.

Once you create a data access page, you can save it. Choosing to save a page for the first time creates two objects:
  1. A Web page is saved as a separate Web page file outside the database.
  2. A shortcut link is created in the Pages collection of the Database window. The shortcut points to the corresponding data access page.

Any of these simple routes enables you to create a page that’s viewable from Access or an Internet Explorer 5 browser. IE5 is the only browser capable of viewing data access pages, so you should reserve the pages for intranet situations where it’s possible to specify the browser type used to access the page. By restricting Access to IE5 browsers, Microsoft makes it easy to build and use Web pages that include databases.

When browsing data access pages from multiple computers, you will need to edit the page's data access connection properties for Microsoft Jet database engines. By default, Access connects the pages to data via mapped drive letters on whatever computer you used to build the tables. This won’t cause any problems as long as you always open the page from the computer used to build the page. However, it’s unlikely that another computer will have the same database in an identical location on its hard drive(s). So if a user on another computer attempts to open the page, the computer will be unable to complete a connection to the page's data source.

The workaround to this predicament is to respecify the OLE DB (Object Linking and Embedding Database) data source and OLE DB system database properties with the Universal Name Convention (UNC) format. For example, on a computer named Cabxli with drive shares named after their drive letters, change the data source from letter:\path\filename to \\cabxli\letter\path\filename. You must make the same type of change to the OLE DB system database properties, which point to the data source's workgroup information file. To reset the page properties, open the Field List dialog box by double-clicking Field List on the Design View toolbar. Right-click on the Connection object in the Field List dialog box to show the Data Link Properties dialog box. Click the All tab to simplify access to both properties. Select each of the properties and respecify their property value from a mapped drive designation to the UNC format.

If you are creating data access pages while disconnected from a server, you can store the pages temporarily in any location. Later, you can move them to a Web server or file server for easy access by multiple users. A reliable way to accomplish this is to remove the shortcut links to the pages from the Database window. Then, move the files to their new location. To re-create a link to a page, double-click Edit Web Page That Already Exists and navigate to the page’s new location. Re-save the page in Access. Repeat this process for all other moved pages to finish relinking them.

Sample pages
Although data access pages resemble Access forms and reports, they aren’t backward compatible. You can’t automatically convert your old Access forms and reports to data access pages. However, data access pages compensate for incompatibility by being exceptionally easy to construct and amazingly flexible.

The navigation bar at the bottom of a data access page offers multiple functions, and it includes a Help button. Help desk technicians can reduce the frequency and duration of low-level support calls by introducing end users to the Working With Data section in Help. This section details the capabilities of navigation toolbar command buttons. In addition to browsing records, users can add new records, as well as update or delete existing ones. Users can also sort and filter the records behind a page with the navigation bar.

Webmasters can control the visibility of each command button on the navigation toolbar. (Users can’t sort without a command button for sorting.) A browsing page is read/write by default. However, page designers can make a page read-only by altering its Recordset Type properties from dscUpdatableSnapshot to dscSnapshot. These settings can be changed in the page's property sheet.

Bound images
There is no way to add bound images directly to a data access page, but the Microsoft Office 2000 Developer Edition (MOD) includes a sample data access page with a solution. The solution requires the creation of a separate table with the paths to the images (consider transforming the images to a popular Web format, such as a .gif file). Use the table as the source for an Image control. The Microsoft solution buries much of the plumbing in a data source control that is rich in XML code. Advanced developers can study this as a model for customized extensions of the initial design. The sample is available in the Solutions9.mdb file on the MOD CD-ROM.

Reporting capabilities
Another data access page type features reporting capabilities as opposed to browsing and editing functionality. Webmasters can create a kind of interactive reporting data access page by creating the page in a way that users can selectively expand sections of the report.

Supporting technologies and issues
It’s easy to create and design data access pages manually, but you can also develop them programmatically. Several situations call for programmatic approaches. This section explores programming data access pages with VBA and the Microsoft Script Editor.

As you manually create pages, it’s easy to forget to make design changes that will significantly impact the use of the pages. For example, you might designate a data source with mapped drive letters instead of the UNC format. When you test the page, it may work correctly from one computer on an intranet, but another computer will fail to properly connect to the page's data source. You may also have to consolidate an application with many data access pages distributed over multiple folders and servers. In either of these situations, creating an inventory of the data access pages associated with an Access database file or project is useful.

The VBA procedure in Listing A illustrates one way of creating a list of all the data access pages in the current project—or any project with a slight modification. For inventory purposes, it relies primarily on the AllDataAccessPages and DataAccessPages collections. The AllDataAccessPages collection is a set of AccessObject objects. Each member object contains information about a data access page in a project. The Name property of an AllDataAccessPages member is the shortcut link name for the page in the Database window. The FullName property is the data source specification (either the mapped drive letter or UNC format). The IsLoaded property indicates whether a page is currently open. The DataAccessPages collection refers to all open data access pages in an Access database file or project. This includes a set of properties and one method for data access pages. An interesting property for our data access page inventory task is the ConnectionString property. It contains useful information that can help with consolidation efforts, such as the password and workgroup information file for the data source associated with a page.
Sub inventoryDAPs()
Dim myAObject As AccessObject
Dim dap1 As DataAccessPage
Dim rst1 As ADODB.Recordset
Dim cmd1 As ADODB.Command

'Open recordset for data access page inventory
Set rst1 = New ADODB.Recordset
rst1.Open "dapInventory", CurrentProject.Connection,
 adOpenKeyset, adLockOptimistic, adCmdTable

'Empty old inventory before repopulating table
Set cmd1 = New ADODB.Command
With cmd1

 .ActiveConnection = CurrentProject.Connection
 .CommandText = "DELETE dapInventory.* From dapInventory"
 .CommandType = adCmdText
 .Execute
End With

'Start loop through pages
For Each myAObject In _
 Application.CurrentProject.AllDataAccessPages
 With rst1
'Start to add a record to the inventory
 .AddNew
 rst1.Fields("dapLinkName") = myAObject.Name
 rst1.Fields("dapFileName") = myAObject.FullName
'Collect connect string info
'Open (and re-close) any pages that are closed
 If myAObject.IsLoaded = True Then
 Set dap1 = _
  Application.DataAccessPages(myAObject.Name)
 rst1.Fields("dapConnectionString") = _
  dap1.ConnectionString
 Else
 DoCmd.Echo False
 DoCmd.OpenDataAccessPage myAObject.Name
 Set dap1 = _
  Application.DataAccessPages(myAObject.Name)
 rst1.Fields("dapConnectionString") = _
  dap1.ConnectionString
 DoCmd.Close acDataAccessPage, _
  myAObject.Name, acSaveNo
 DoCmd.Echo True
 End If
 .Update
 .MoveNext
 End With
Next myAObject

End Sub

If you have substantial experience with HTML, DHTML, and scripting for Web applications, you’ll probably find the Microsoft Script Editor friendly. It will enable you to enter HTML and code directly into a data access page. It’s also a great place to review samples that Microsoft provides or that you build yourself through the manual interface techniques described above.

Conclusion
With Access 2000, Microsoft introduces a simple, flexible, and powerful new tool for designing databases that include Web applications. Before building solutions with this technology, you must answer the question, “Is it possible to restrict viewing pages to IE5 browsers?” If the answer is yes, you’ll be able to utilize data access pages.

The goal of this article is to expose you to the core concepts of data access pages, so you can decide if they’re right for your organization. Many excellent sources are available if you wish to drill deeper. Besides the standard Access 2000 Help system, you should explore the separate Help system available through the Microsoft Script Editor. Sample data access pages are available from the Northwind Access database file. The NorthwindCS Access project orients you to applying these to SQL Server and MSDE data sources. Additional sample pages and techniques are available from Microsoft Office 2000 Developer Edition (MOD) in its VB Programmer's Guide and on the companion CD. Four MOD white papers also present advanced programming and deployment issues, such as how to use data access pages over the Internet as opposed to an intranet.

Rick Dobson, Ph.D., and his wife operate a development and training consultancy. He is the author of the best-selling book Programming Microsoft Access 2000 for Microsoft Press. Rick is a regular contributor to TechRepublic and numerous computer periodicals. In addition, he has presented training sessions and seminars on Access and Web development topics in Australia, Canada, the United Kingdom, and throughout the United States. Rick is a Microsoft Certified Professional and a Microsoft Certified Trainer. You can reach Rick at either of the two Web sites that his practice maintains (www.programmingmsaccess.com and www.cabinc.net ).

The authors and editors have taken care in preparation of the content contained herein, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.

Editor's Picks

Free Newsletters, In your Inbox