Exploring Access 97

The assignment seemed simple: Write a column describing the key new features in Microsoft Access 97. I obtained a copy of the Office 97 preview set and began to dig in. As I started exploring in my typical hyperlinking style, I realized that there is a lot more happening here than simply an Access upgrade. In fact, the changes in Access itself are not extremely significant; for the most part Microsoft Corp. has simply extended features that it implemented in Access 7.0 for Windows 95.

I found the significance I was looking for in two places. First, with Office 97, Microsoft's desktop suite has evolved into an object-based data- and Internet-aware application development platform. Second, by virtue of a common object architecture and tools set, Microsoft's Office and BackOffice technologies are converging around the Internet Information Server to provide an effective platform for developers creating solutions for a wide variety of organizations. These aspects of Microsoft's strategy for controlling the future of application development architectures join a couple of ongoing initiatives that are coming into clearer focus. First of these is the extension of Visual Basic as a language that high-level application developers can use to integrate solutions at all levels of an application system. Second is the evolution of the JET database engine into a data store that can be deployed both on Web client and server platforms.

To create a perspective for analyzing Microsoft's continuing technology barrage, I must first recount the stratification of Visual Basic into versions optimized for different categories of solutions. Visual Basic, Enterprise edition is a complete client/server development suite. Visual Basic, Control Creation Edition provides a platform for application developers like me to create ActiveX controls that can be deployed within applications created by any development product. Visual Basic for Applications (VBA) provides a consistent development surface for building applications that extend the Office development suite. The Visual Basic Scripting edition is a lightweight version of the language that is optimized for executing applications that run within the context of Microsoft's Internet Explorer and other Web browsers. Next month, I'll delve into greater detail on VBA and VBScript, and my colleague David Linthicum will discuss the Enterprise and Control Creation versions.

The second technology foundation supporting client-oriented, object-based application development is the continuing fortification of the JET database engine. JET version 3.5 has been enhanced to support Web database deployment, department-scale client/server applications, and applications built around the office suite. Improvements implemented in JET 3.5 give developers more control over application behavior and promise to boost performance. The new ODBCDirect technology lets Office and Access applications obtain data from RDBMS servers without going through the JET engine. Enhancements to the JET replication engine and the provision of user-friendly tools for managing replication make this feature a feasible solution to multiuser distributed database scenarios. The JET engine is taking on an increasingly strategic role in products offered by Microsoft and other vendors such as Visio Corp. (Seattle, Wash.), Vision Software Tools Inc. (Oakland, Calif.), and The EC Company (Palo Alto, Calif. -- my new company), who are deploying the JET engine as the repository for structured metadata.

These enhancements come along just in time. I have been corresponding with increasing numbers of developers who are actually implementing client/server systems with Access as the client. Some of these folks are even developing commercial business management and accounting products with this architecture. (I describe the version 3.5 JET enhancements in more depth below, and I'll return to a discussion of the specific issues involved in implementing client/server solutions with Access front ends later in the year. I'll also defer a detailed discussion of VBA 5.0 to next month.)

The architectural evolution of the entire Office suite into a component-based data- and Internet-aware development platform is more significant news. This evolution leverages VBA as a common development environment. For VBA to be a viable development tool, the applications being extended must offer an object hierarchy that exposes their structure and functions in a meaningful way. Office 97's developers have significantly extended the architecture of objects exposed by products in the suite. This includes new suite members such as Team Manager, the Command Bars component, the File Search utility, the Binder, the Office Assistant, and the Outlook groupware application, in addition to the core Word, Excel, PowerPoint, and Access products. With this architecture, suite members can function as a collection of object servers that provide services to one another and to applications being deployed within the context of the Web browser.

Finally, the Active Platform, and particularly Active Server Pages, appears to be a fundamental architecture that Microsoft will support for some time. The Active Server is a script-driven Web page generation engine. With the active server component installed on your Internet Information Server, if a browser requests a file with an "asp" extension, the server will process the script (say, VBScript or JScript) in the file and generate an HTML result, which it returns to the requesting browser. How does the Active Platform relate to Access, you wonder? Well, one of Access 97's new features is its ability to save table, query, form, and report objects as Active Server page files. In essence, Access 97 is a very nice database development environment that just happens to generate VBScript-based Active Server Pages.

JET 3.5

Data Access Objects (DAO) has exposed a new option of the DBEngine. Idle method called dbRefreshCache that will cause Jet to refresh its cache. This option lets developers of multiuser applications refresh the cache on demand, without compromising performance. The DAO CommitTrans method will force the operating system to immediately write Jet's transaction to disk. JET 3.5 has a new caching algorithm that enables it to utilize the maximum amount of cache without withholding access to records from other users. This particularly improves the performance of bulk operations. In another move to improve concurrency in applications where users are simultaneously reading and updating data in memo fields, Jet 3.5 uses fewer locks when reading "long value pages" (OLE or memo data types).

Partial replication is a major enhancement to the replication feature introduced in JET 3.0; it now allows row subsets to be replicated. You can use either Boolean or relationship filters to create a partial replica. Boolean filters limit the replicated rows to only those that meet specified criteria. DAO represents this filter with the ReplicaFilter property on a TableDef. relationship filters limit the set of rows being replicated by enforcing a relationship between partially replicated tables. With DAO, you can set the PartialReplica property on a relation. This allows filters on the "one" side of a relation to be used to filter the records being replicated from the "many" table.

Replicated database subsets and updates to replicated databases can now be transferred via FTP connections on TCP/IP networks. The entire replication process can be managed with the Replication Manager Application included in the Office Developer edition. I viewed the Briefcase replication functionality included with Access 95 as a solution for single-user, two-computer scenarios. The combination of partial replication, FTP capability, and the replication manager makes JET replication a feasible solution for multiuser, multisite applications where delays in recording updates from other sites are acceptable.

Microsoft calls the second significant data access improvement "ODBCDirect." Using ODBCDirect, applications written in VBA using DAO to work with database servers can bypass the JET engine entirely, thereby avoiding a significant amount of client overhead. The selection of data access mechanism is specified as a type argument to the CreateWorkspace method of the DBEngine object. Each "workspace" defines a database session that encompasses database connections, user rights, and transaction management. A workspace that uses ODBCDirect can specify whether to locate cursors locally or on the server, whether they are dynamic or static, and what level of concurrency control they provide. Readers who have read my discussions of cursor control in Microsoft Visual FoxPro are familiar with its sophisticated cursor management controls. These controls are now available throughout the Visual development suite via ODBCDirect. (Documents available on Microsoft's Web site at www.microsoft.com/accessdev/accwhite/odbcdir.htm and www.microsoft.com/accessdev/accwhite/accwhite.htm offer detailed descriptions.)

ODBCDirect offers other important features and benefits. It provides an easy way for developers to pass input parameters and receive output parameters from SQL Server. It lets Access and VBA developers load dynamic queries onto a server as prepared statements and execute them with different parameters throughout the course of a session. It enables asynchronous processing of both application functions and multiple server requests. With optimistic locking in effect, it permits applications that need to update multiple rows to work entirely locally and to update server tables in a batch; this approach can significantly improve the performance of batch operations where contention is unlikely. Using DAO, you can mix and match JET and ODBCDirect operations, using each to perform tasks for which it is best suited. For instance, using JET lets you use DAO's data definition syntax to create database structures rather than SQL pass-through. It also supports heterogeneous joins between tables in two or more ODBC data sources.

Access 97 Developer Enhancements

This ability to execute objects within the current database has significant positive implications. One of the key performance enhancements in Access 97 is that it won't load the code module associated with a form or report if there is none. Access 7.0 loaded the code module associated with each form whether or not code was associated with it. Microsoft refers to forms without code as "lightweight" forms and claims that they load twice as fast as those with code. This feature was driven by user feedback that form load-time in Access 7.0 was excessive, as well as indications that a large percentage of forms have no logic behind them. In the common case where the only logic is the ability to open an associated form, query, or report, this logic can be replaced by a hyperlink, making the object lightweight.

Another performance-enhancing feature is the ability to remove the source code from an Access application prior to distribution. An option on the Tools menu performs this task. The resulting file, with a default .mde extension, prevents users from seeing design objects and results in smaller distributable applications. It improves performance by eliminating the need for Access to check whether the source code is more recent than the current compiled image. Access requires manual recompilation, so it's particularly slow when it determines that there have been changes; eliminating the possibility of users trying to modify code is therefore especially attractive.

Access 97 now participates in Microsoft's SourceSafe source code control paradigm. With SourceSafe and the Office Developer edition installed, developers can check objects in and out of the version-control system from inside Access. Each developer on a multideveloper project works on his or her own .mdb file. Access queries, forms, reports, macros, and modules are stored as text files in the version-control system. When a developer wants to check one of these application components into version control, Access exports it to a temporary text file and copies that text file into the SourceSafe project. When you want to check out an application component to work on it, SourceSafe copies the text file from the version-control system to a temporary holding location on the developer's machine. Access then imports the file into the working .mdb file. These transformations occur behind the scenes as the developer works through the process of creating, updating, and saving forms, reports, queries, and modules. All of the other Access objects are stored in a single .mdb that SourceSafe treats as a binary file.

Access 97 Support for Internet Applications

The next most explicit Internet connectivity feature is the Save to HTML menu command, which is exposed to developers within VBA through the OutputTo method of the DoCmd object. Choosing the Save to HTML command from the Access file menu starts the Publish to the Web wizard. Using the OutputTo method programmatically enables developers to bypass the wizard but preselect from the options that the wizard would make available. The most significant choice controls whether the currently selected table, query, or form is converted to a static HTML document, a dynamic Web application that uses Microsoft's Internet Database Connector mechanism, or a dynamic Web application built with Active Server Pages. Secondarily, you can choose an HTML template, whether to automatically generate a home page, and where to store the generated files.

Publishing information from a database to static pages has fallen somewhat out of style, but in certain circumstances it is still expeditious to do so. This is particularly true if immediate data currency is not required. An HTTP server will return static pages faster than those that need to be generated dynamically. Moreover, avoiding HTTP access to a database reduces the administrative burden of setting up and maintaining ODBC connections on the Web server and eases multiuser contention for the database. Access's ability to save reports and forms as static HTML documents is especially useful, because it converts all formatting properties recorded in the database -- such as font information, color, and size -- into the equivalent HTML code and results in pages that are a close map to the real thing.

If your application requires dynamic page generation, Access generates both Internet Database Connector and Active Server Pages files. Internet Database Connector was the mechanism that Microsoft initially introduced for dynamic access to databases from the Internet Information Server. If you choose this approach, Access generates two files, a request file with an .idc extension and a response template with an .htx extension. The request file lists the ODBC datasource name, a SQL statement representing the table or query to use for retrieving the data, the name of an HTML template file, and any user name or password that the database requires. Access lets Web page designers create templates with directives to its generators that are processed when the response page is created. The response file is an HTML document with tokens indicating the names of data elements that will be replaced by actual data items when the page is returned to the requesting browser.

Access 97 and the Active Server Architecture

At the top of the program, the code shown in Listing 1 established a connection to the JET engine and read the contents of the "product" table into a recordset. It then established a persistent server side session for the current user.

I then discovered ActiveX object definitions for each of the labels, entry boxes, list boxes, and command buttons defined on the Web form. These used the HTML 3.x <OBJECT> element syntax and are shown in Listing 2.

The CLASSID property pointed to an object class defined in the Windows NT registry. By inspecting the registry, I learned that the classes being used here were the UI objects exposed by the Microsoft Forms package. Microsoft Forms is a complete subsystem for developing ActiveX control-based forms. It is included with Office 97 and is planned for inclusion in future versions of Visual Basic. It already serves as the basis of the ActiveX Control Pad and the HTML layout control. The one application glaringly missing from this list is Microsoft Access; although Microsoft Access 97 got the updated Visual Basic for Applications language engine, it will have to make do with its older Microsoft Access Forms at least until the next version. So while we wait for a future version of Access to get the new Forms designer, it's already possible to use Access to create its output.

Another interesting piece of this object definition is the assignment of values to property parameters (see Listing 3). All of the properties of this control are assigned via Name/Value pairs. In the example, the Value property takes the value of the VBScript variable "selectedVarSupplierID." "<%" and "%>" serve as .asp delimiters. The variable selectedVarSupplierID records the value of the user's selection from the supplier combo box, which is loaded into another ADODB recordset. VBScript code executing on the client retrieves data being managed by cursors running on the Active Server.

Interesting things are happening in Listing 3. The script has created a server-side cursor, which it scoped to the user session. VBScript executing on the client browser is creating an array that will populate a list of suppliers. It sizes the array by referring to a variable created and maintained by the server process. The server fills the client-based array by traversing the record set and assigning values from the supplier ID and name columns to array elements referenced by the server-side iterator variable. This is an extremely tightly coupled interaction between client and server; it is presented here as an example of the new Active Server approach to running database applications as Web applications. It's certainly useful to have Access 97 generating complete applications that I can use as they stand or as training wheels!

A Surfeit of Application Development Technology

With the increasing componentization of Microsoft Office, Access becomes less interesting as a standalone product than as a source of database management functionality that can be used in conjunction with the applications in the Office suite.

Figure 1.

LISTING 1

<%
' Specify SQL statement that reads supplier list for lookups

sql = "SELECT DISTINCTROW [Suppliers].[SupplierID], 
[Suppliers].[CompanyName] FROM [Suppliers] ORDER BY
 [Suppliers].[CompanyName] "
' Create an ADODB recordset object
Set tempRS = Server.CreateObject("ADODB.Recordset")
' Map the query to the recordset
tempRS.Open sql, conn, 3, 3
' Assign the record set to the session
Set Session("RS_Products_SupplierID") = tempRS
%>
' Create array of suppliers
Dim SupplierID_tempList(<% =tempRS.RecordCount %>, 2)
<%
tempRS.MoveFirst
I = 0
' Scan record set looking for matching value
do while Not tempRS.eof
If tempRS.Fields(0).value = rs.Fields("SupplierID").Value Then
selectedVarSupplierID = cstr(tempRS.Fields(0).value)
End If
%>
SupplierID_tempList( <% =I %> , 0)= "<% =tempRS.Fields(0).value %>"
SupplierID_tempList( <% =I %> , 1)= "<% =tempRS.Fields(1).value %>"
<%
tempRS.MoveNext
I = I+1
loop