OakLeaf Systems: Client-Server Application Development Using Microsoft Access 2.0

Tuesday, April 25, 2006

Client-Server Application Development Using Microsoft Access 2.0

The oldest article that I've been able to find that's related to my early Access client/server development projects is the "Client-Server Application Development Using Microsoft Access 2.0" presentation from Tech•Ed 95 by Microsoft's Christopher Payne. (Chris Payne is now Corporate Vice President, MSN Search.)

The article—last modified on February 26, 1995—had been available as a Web page from http://www.microsoft.com/Accessdev/ARTICLES/AC302.HTM. Details are preserved here because the Web version is no longer available from the Microsoft site, and it appears to have disappeared from the Google cache. Figures were missing from the cached source copy. Microsoft Knowledge Base article Q129613 has a link to a downloadable Microsoft Word version of the article.

My Access 2.0 applications that inspired this article were NSC_ASK.mdb and MSA_OLTP.mdb. NSC_ASK.mdb was a client/server decision-support application for National Semiconductor Corp., which is described in my "An (Almost) Lifetime of BASIC" archive post. I wrote MSA_OLTP.mdb as a contract project for Greg Nelson, an early Access marketer.

National Semiconductor's Margaret Lam and Greg Nelson demonstrated the two applications at Tech•Ed 1995's AC302 session—about a year and a half after Microsoft released Access 1.0 in November 1993. Special Edition Using Access 95 and subsequent editions have used MSA_OLTP.mdb to test backward compatibility and compare client/server data retrieval and update performance with unbound controls.

Remarkably, much of the content of this article still applies to client/server projects with current Access versions. MSA_OLTP.mdb was designed for heads-down telephone order entry by customer service representatives. All order lookup, new order entry, and new customer addition operations use keyboard shortcuts; a mouse isn't required.

The following screen captures of MSA_OLTP.mdb—running in Access 2007 Beta 2—illustrate basic customer and order search, and new order-entry operations. Click the images to display full-size versions in a new window.

Customer lookup (above) starts by typing the first character or two of the company name into the textbox and pressing Enter to open a list box of matching company names if more than one customer meets the search criterion.

Selecting the customer in the listbox and pressing Enter opens another listbox of the customer's orders in reverse date sequence.

Selecting the order to review and pressing enter displays the line items for the order in another Access list box.

Pressing Alt+N or clicking the New Order button activates the order entry phase. Marking the Same as Bill To check box populates the shipping address fields with data from billing address. The capability to select products by numeric ID or the first one or two characters of the product name expedites line item entries.

The following description of the AC302 presentation is excerpted from the introduction to "Client-Server Application Development Using Microsoft Access 2.0."

Client-Server Application Development Using Microsoft Access 2.0 Presented by: Christopher Payne Christopher Payne has been with Microsoft for 4 1/2 years and has been focusing on research and planning future versions of Microsoft Access for the past 2 1/2 years. In addition to working on Microsoft Access, Chris was the project lead of the Microsoft Access Upsizing Tools released in October of 1994.

This presentation was originally created by Greg Nelson and Roger Jennings. Greg Nelson is a Senior Access Product Marketing Manager for Microsoft. Before coming to Access Marketing, Greg spent eight years as a developer and technical manager in Microsoft’s MIS department. He has 12 years’ experience in the software industry. Roger Jennings is the author of two books about Microsoft Access and is a consultant in the field of Windows database applications.

Session Goal The goal of this session is to provide Microsoft® Access developers with an understanding of the issues involved in developing efficient, optimized Microsoft Access version 2.0 applications for the client/server environment.

What You Will Learn

1. How Microsoft Access architecture operates and how it implements client/server computing.

2. How to optimize Microsoft Access to take advantage of its client/server capabilities.

3. How to use transaction processing system design issues as references for structural ideas and actual code segments optimizing client/server applications.

Overview

This course breaks down into two distinct sections:

1. The first section is an architectural overview of client/server application development technologies using Microsoft Access. This section provides essential background on each component, explains how the components are used, and how to optimize your application for your environment.

2. The next section is an overview of a transaction processing system. This application is the Northwind Traders sample database that ships with Access version 2.0.

For the purpose of this class, it has been migrated to a client/server environment giving us an opportunity to discuss the issues and solutions involved in migrating a transaction-processing file server application to client/server with a database many of us are familiar with. The applications will be available on your Tech*Ed compact disk. The application is for your personal use to learn from and reuse code segments. They cannot be redistributed for production use.

The reader is assumed to be familiar with the design of Microsoft Access version 2.0 applications and conversant with Access Basic programming techniques. No prior experience with using the Structured Query Language (SQL) database data sources is assumed for this session. This session is about optimizing database server usage in Microsoft Access.