In the Real WorldAlternatives to Action Queries

In the Real World Alternatives to Action Queries

Microsoft calls any Jet query that alters table data an action query; the more common name is update query, as in updating the database. With Jet, however, there's a good reason to distinguish graphical action queries from the update queries used in online transaction processing (OLTP). Access's graphical action queries are intended primarily for bulk operations adding, altering, or deleting large numbers of records in a single operation. OLTP usually deals with a single record or a few related records per operation. Creating a new Access action query each time you must update a single record clearly is an inefficient process, even if you add a parameter to designate the record you want to update or delete.

Note

The sections that follow deal with advanced Access topics, which are covered in detail by chapters later in this book. In the real world, production databases reside on a file or application server and multiple users connect their client PC's front-end applications to networked client/server (MSDE or SQL Server) databases. Chapter 19, "Linking Access Front-Ends to Jet and Client/Server Tables," and Chapter 20, "Exploring Access Data Projects and SQL Server 2000," deal with client/server databases. The purpose of this "In the Real World" episode is to demonstrate the many options that Jet and SQL Server offer for executing action queries and their equivalents.

Browse-Mode Updating

Browse-mode table editing in Datasheet view is the obvious alternative to action queries when you need to add, alter, or delete only one or a few records. In the real world of networked multiuser databases, however, database administrators (DBAs) discourage or prohibit browse-mode editing because browsing usually requires multiple database connections for a single client PC and generates a substantial amount of network traffic.

Further, multiuser browse-mode editing often results in contention problems when two users attempt to edit the same record. Jet's optimistic record-locking approach minimizes such conflicts, but resolving which user's edit of a record is correct often requires manual intervention by the DBA or a supervisor. DBAs, especially, don't like to get involved with table-level contention issues. DBAs suffer perpetual contention with information technology managers and chief financial officers.

Form-Based Updating

Form-based updates are the most common approach for production Access applications using Jet databases. Designing Access forms for OLTP applications is the subject of the next two chapters. Typically, the main form displays field values of a single record, for example an invoice, in text boxes. Data from related tables, such as invoice line items, appear in a multi-row subform. Conventional Access form-based updating, however, is a variation on the browse-mode datasheet updating process. The client PC maintains at least one connection to the database tables while the editing application is open and generates a significant amount of network traffic during the editing process.

The primary advantage of form-based over datasheet updating is that you can add to the form VBA code that resolves contention issues with error-handling procedures. An even better approach to contention problems is to write VBA code that takes advantage of the data-related events of ActiveX Data Objects (ADO). You must be a fluent VBA programmer, however, to write effective event-handling subprocedures for Access's form Recordsets.

Updating with SQL Statements

Sending SQL INSERT (append), UPDATE, or DELETE queries over the network to the database server is a much more efficient process than browse-mode editing with datasheets or forms. You send an SQL SELECT query to the database to retrieve only the record(s) you need, disconnect from the database, edit the records, open a connection, send one of the three types of SQL update queries, and then close the connection. Following is a typical T-SQL statement to add a new order with three line items to the Orders and Order Details tables of the NorthwindCS SQL Server database:

    INSERT INTO Orders       VALUES(11093, 'KOENE', 1,         '5/15/1998', '6/1/1998', NULL, 3, NULL,         'Königlich Essen',         'Maubelstr. 90',         'Brandenburg', '', '14776',         'Germany')     INSERT INTO [Order Details]         VALUES(11093, 24, 4.5, 24, 0)     INSERT INTO [Order Details]        VALUES(11093, 36, 19, 36, 0)     INSERT INTO [Order Details]        VALUES(11093, 42, 9.8, 12, 0) 

The preceding SQL INSERT statement contains a substantial amount of text overhead, but executes very quickly over a network connection. You can quickly convert the statement into an SQL Server transaction by adding a BEGIN TRANS[ACTION] prefix and a COMMIT [TRANS[ACTION]] suffix. Wrapping the statement in a transaction assures that either all INSERT operations succeed or the entire operation fails and no change occurs to either of the tables. Adding TRANS[ACTION] statements qualifies the operation for OLTP. Chapter 21, "Moving from Jet Queries to Transact-SQL," describes T-SQL TRANSACTION syntax.

Another update alternative is to use an ADO 2.7 disconnected Recordset object to retrieve and update Jet or SQL Server 2000 records. The advantage of disconnected Recordset objects is that ADO handles most of the disconnecting and reconnecting chores for you. Disconnected Recordsets also let you edit multiple groups of records, and then send only the changes to the database with the UpdateBatch method. Sending only the changes is especially efficient for UPDATE operations.

For an example of the VBA code for batch update operations, see "Taking Advantage of Disconnected Recordsets," p. 1312.

Updating with SQL Server Stored Procedures

The fastest and by far the most efficient method of updating is by using a parameterized stored procedure with a client/server database, such as SQL Server 2000. A stored procedure is a precompiled query that's similar to a stored Jet query (called a QueryDef object). Chapter 21 shows you how to write T-SQL parameterized stored procedures. SQL Server 2000 and thus MSDE2000 execute stored procedures faster than prior SQL Server versions.

You send the new values to add or change as stored procedure parameters. It's a common practice to write separate stored procedures for INSERT, UPDATE, and DELETE operations. DBAs greatly appreciate developers who take full advantage of stored procedures. Well-written stored procedures let DBAs spend more of their time contending with management, instead of putting out fires started by contentious OLTP users.