Chapter 19

Exploring Client/Server Issues


No doubt you've seen a client/server demo or read an article that provided information to this effect: "Run the Access Upsizing Wizard to move your tables from Jet to SQL Server, link your application to the server tables, and Wham-you've got a client/server-based solution." This kind of presentation is like a plate piled high with whipped potatoes-lots of air, a little starch, and no meat.

Creating an expert solution that uses the teamwork between Access and the server-based data effectively involves a bit more work than simply running a wizard. This chapter describes the required effort; it is crammed with as much client/server connectivity "meat" as I could provide in the allotted space. I introduce you here to the following techniques:

Making Connections

If you are an old Visual Basic veteran like myself, you probably spent hundreds of hours over the years writing complex library routines that called the ODBC API directly to provide server data to your applications. Then, suddenly, along came the Jet database engine.

What a godsend! Jet suddenly provided a connectivity tool that made gatewaying out from VB applications to server data much easier to code. Installing the Jet Compatibility Layer into Visual Basic 3 provided what seemed like "instant" access to server data.

As an Access developer, close your eyes and contrast the difference in metaphors between life with Jet's connectivity abilities and the alternative-a life without them. Because Jet invisibly manages connections, ODBC compatibility, and cursors, an Access developer can literally link to server tables, run the Report Wizard, and print a report from legacy data, all in about five minutes. Without Jet's connectivity options, however, we'd all be using the API routines from our Visual Basic applications to login to a server, establish connections, create SQL statements, step through cursors, and fetch data into temporary tables simply to print the same report -a difference of twenty to fifty times more effort. Whew!

Makes you feel like sending a thank-you card to the Jet Team, doesn't it?

Those of you who have worked with other database products prior to Access probably were quite impressed when you saw the Jet Database Engine-it's a remarkable tool. However, the true genius of Jet doesn't really become apparent until you incorporate a database server into your Access application. Doing so reveals in stark detail two important facets of Jet:

In spite of Jet's powerful abilities, if you could simply link a server table to your Access application and point a form to the link, there would be no need for this chapter. Unfortunately, there are many nuances in making Access rely on an external server for its data. Ponder just a few of the questions that arise:

In this chapter, I will help you close in on the answers to questions like these. If you recall from Chapter 7, "Understanding Application Architecture," that there is no single way to combine application components, you will understand the fact that adding a server into your application's object mix introduces rewards and risks, advantages and obstacles. Thus, I say "close in on the answers" because often absolutes are hard to come by in a client/server model. I'll provide you here with many techniques and options, but you'll need to employ and test the techniques in your specific environment and applications before their value can be measured with certitude.

The first part of the chapter discusses the basic workings of the Jet Database Engine and the new ODBCDirect component of the Data Access Objects in a client/server application, with Access on the front-end and a server (SQL Server 6.5 in these examples) on the back-end. Then the chapter discusses some practical techniques for crafting Access forms and reports in client/server applications. This chapter is not a tutorial on upsizing. Instead, I assume that you have run the Access Upsizing Wizard already or manually created a server data structure for your application.

In spite of its lack of robust data and good naming conventions, the database Pubs that ships with SQL Server is used for the examples in this chapter and on the CD-ROM. Because it was not feasible for me to ship you a server database to run with my code samples, I opted to use a sample database that every SQL Server owner already has.

In my examples, the tables in the Pubs database are linked to an Access database using the tag tsql in front of each table name.

Manipulating Server Data

The relational data model that permeates the current database climate is based on-what else?-"relations," which are datasets of records and fields. SQL, the language of the relational model, thinks in terms of such datasets. That is why SQL places a higher emphasis on statements like SELECT, which return multiple records, than on a robust model designed to go to a particular record or to navigate between records.

Jet, however, provides the best of both worlds. Its use of an SQL dialect based on ANSI SQL (plus custom extensions) implements the positive aspects of the relational model. In addition, Jet provides recordset operations that exceed the capabilities of SQL cursors and allow very granular access to individual records and fields.

Likewise, when working with server datasets, Jet again breaks free of the SQL paradigm's restrictions and provides added value. The concept of returning sets of multiple records means that the obvious way for Jet to work with server data would be to fetch the entire results of an SQL statement from the server into its local buffer. Because this would not be time or resource-efficient, Jet "cuts corners" for speed by pulling keys (indexes) down from the server and requesting specific records or blocks of records by their keys, as shown in figure 19.1. Thus, Jet can get (and give) data in keyset-driven chunks rather than in entire dataset-driven superchunks. This optimizes the retrieval of server data when using dynaset-type recordsets (snapshots are retrieved in aggregate rather than chunks).

Fig. 19.1

Jet retrieves server data in chunks by keys rather than by entire result set.

Mechanically, much of Jet's interaction with a server is handled through two components:

In contrast to Jet's local workings, the new ODBCDirect engine attempts to offload as much work to the server as possible. It does this by employing ODBC's ability to use cursors (server-based recordsets). When the DAO passes a direct record request through to the server, it tells the server to create a cursor using server resources. DAO then fetches records from the cursor as required. In essence, ODBCDirect moves steps 2 and 4 in Figure 19.1 from the client side to the ODBC driver if the driver is connected to a server that supports the required cursor capabilities. This increases performance.

Linking to ODBC Data Sources

When running setup for Access or Office, choose the advanced (custom) installation option, which allows you to install the ODBC driver for SQL Server (select the Data Access option). The SQL Server driver SQLSRV32.DLL ships with Office and Access; other ODBC drivers are available from third-party vendors.

To configure your ODBC driver, you must first create a data source, which is a named configuration that includes a driver and a pointer to a data server. For example, to create a data source for the sample database included with this chapter, perform the following steps:

  1. Select the ODBC icon from Control Panel. The ODBC Data Source Administrator dialog box appears.
  2. Click Add in the dialog.
  3. Select "SQL Server" from the Create New Source dialog box. Click Finish.
  4. In the ODBC SQL Server Setup dialog, enter a Data Source Name, for example Pubs. Next, enter the name of your Server containing the Pubs database. Finally, enter Pubs as the Database Name option, as shown in figure 19.2.

Fig. 19.2

Creating a new SQL Server data source.

You can create a disk file (DSN file) that saves data source information and can be passed from one user to another (for example, it can be distributed with your Access application). Notice the tabbed layout for the ODBC dialog box in Figure 19.2. To create a data source that is visible only to the user currently logged in to the current machine, create it from the User DSN tab. To create a data source that is available to any user logged in to the current workstation, use the System DSN tab. To create a DSN file saved on disk, use the File DSN tab.

You can also create a data source entry in the Windows Registry using program code. This allows an Access client/server application to create or update the data source for its back-end tables as the application initializes. See the Help topic on the RegisterDatabase method for more information.

Having created a data source, you can now use the data source to link server tables to your client database. From the Database window, choose File, Get External Data, Link Tables... from the menu. In the Link dialog, open the "Files of type" combo box and select "ODBC Databases ()" (hidden at the bottom of the list). The SQL Data Sources dialog displays, as shown in figure 19.3. Set the data source you require (notice that Pubs from our previous example is available in this dialog).

Fig. 19.3

Selecting the data source that will supply the table to be linked.

Next, a SQL Server Login dialog is displayed. Enter the appropriate Login ID (for Pubs, this is sa). Normally, an entry in the Password text box will also be required, but Pubs has none.

Pubs is unprotected because it is a sample database. In general, you will always be using security and passwords with server databases.

Finally, a Link Tables dialog displays the tables (and views, which are saved queries) in your server database, as shown in figure 19.4.

Fig. 19.4

The Link Tables dialog allows you to select the server tables to link.

Note the following issues in the figure:

A good naming convention like the one in this book makes it easier to distinguish server tables (which are usually updatable) from views (which usually are not). Appendix C provides naming conventions for use with SQL Server databases and Jet databases that work with them.

As you can imagine, saving the database password with the table link exposes it to anyone who can view or progammatically access the TableDef's Connect property. See the section "Setting Jet Options on the Server" later in this chapter for information on disabling local password storage.

Fig. 19.5

When linking a table or view without a unique index, you are prompted to select the fields for Jet to use as an index; otherwise, editing will be unavailable or unreliable.

This last point cannot be over-stressed. Consider the surprising situation in figure 19.6, which shows the same table (roysched) linked twice from Pubs to an Access database. In the first link (roysched_unique), I properly specified all three columns required to create a unique index for the table (title_id, lorange, and hirange, refer to figure 19.5). However, for the second link (roysched_nonunique), I specified only title_id as the key field, which is not a unique value. As a result, Jet displays the data in the datasheet incorrectly, (see fig. 19.6, which shows how the records for the title_id field value BU1032 are displayed accurately when a unique key is used but inaccurately when displayed via the non-unique index).

Fig. 19.6

Datasheets that reflect two table links pointing to the same server table, with different Jet indexes for the links.

Server databases, like Jet databases, should have a unique (primary) key on each table whenever possible. You should also make use of server time stamp columns in every table, if the server supports them (see the section "Updating External Recordsets" for more information on time stamps). Read/update problems such as those shown in the previous figure can be avoided with a good database structure.

When Jet links a table, it uses the name of the table as supplied by the server, and does any required name fixup. For example, table objects on SQL Server are always prefixed with the name of the object's owner (the default owner is dbo for "database owner") and a period. When Jet receives this name from the server, it is unable to create a table link named dbo.authors due to its naming conventions (Jet does not allow the period), so it fixes-up the link name to dbo_authors.

Once a table is linked from a server to an Access database, it can be viewed in a datasheet, edited (index issues permitting), and used as the basis for recordsets, queries, forms, and reports. For your application and its users, a linked server table has many of the same attributes and capabilities of a local (Jet) table. In addition, like other linked tables, Access displays the connect string for a server table in the Description property in the Table Properties window available in design view.

If a linked server table (or view) is not updatable from Jet by default, you may be able to force it to be updatable by telling Jet to build a unique (local) pseudo-index for the table or view. First, link the table or view to the local database. Then, execute a CREATE INDEX statement through a Data Definition query to tell Jet which fields will be used for the index. Remember that the combination of fields you specify must uniquely identify a record (in other words, qualify as a "pseudo primary key"), as in this example:

CREATE UNIQUE INDEX idxRoySched

ON tsqlRoySched (title_id, lorange, hirange);

Jet does not actually build an index for the data, because it would not be able to keep the index synchronized with changes made by other users to the server data. Instead, Jet saves the index information with the definition of the linked table and uses it when creating the recordset of bookmarks (index values) that it builds at the time you open an editable recordset against the server.

When you create a unique pseudo-index with a Data Definition statement, the index is reflected in the table design view for the link as a primary key. (A primary key for the link is also indicated if you select the key fields when creating the link from the File menu.) However, you cannot create or alter the displayed primary key information for a linked table when in design view. You must use the CREATE INDEX and DROP INDEX statements to do so.

Because pseudo-indexes are local, CREATE INDEX and DROP INDEX statements applied against them do not have any effect on the real indexes on the server.

Establishing Your ODBC Connection

When your application first communicates with an ODBC data source, it establishes a connection, which is a handle to a communication pipeline through which requests and results flow back and forth. Because it would be inefficient to establish a new connection each time your user sends a statement to the server, DAO saves the first connection you establish between the client and the server and reuses this channel for the remainder of the application's needs or until the connection times out.

DAO creates a connection the first time it actually needs to talk to the server. This is not when the Access application opens; instead, it occurs when the first table datasheet opens or query process runs that actually transfers a request to the server. Your applications can usually rely on the server's ODBC driver to provide a login dialog for this purpose, but coding an application this way sometimes feels just a little "sloppy."

Instead, recall my admonition in Chapter 17, "Bulletproofing Your Application Interface," that every application have an Access form that serves as a login dialog. The dialog collects login information that is useful to the application's code routines. In a client/server application, you can extend the capabilities of your login form to create your application's initial server connection.

Figure 19.7 shows the login dialog and the system defaults table we used in Chapter 17, modified to collect and supply the information required to establish an SQL Server connection.

Fig. 19.7

A login dialog and supporting table for making an SQL Server connection.

The following listing shows code that establishes an initial connection to the server from the OK button on the login form. The code actually establishes two preliminary connections: one for the default Jet workspace and one for the ODBCDirect workspace to use (see "Introducing ODBCDirect" later in this chapter to learn the difference). This strategy creates two buffers, one for each type of server access. Regardless of which type of connection (Jet or ODBCDirect) is utilized later by application code, a cached server connection should already exist after this login code has run.

Listing 19.1 AES_Srvr.Mdb-Logging in to an SQL Server and creating an
initial connection for Jet to use

' A routine attached to the login form's OK button

Private Sub cmdOK_Click()

' Purpose: Create a connection to the server database

  Dim blnRet     As Boolean

  Dim dbs        As Database

  Dim rst        As Recordset

  Dim varBackEnd As Variant

  Dim varDSN     As Variant

  Set dbs = CurrentDb

  varBackEnd = DFirst("BackEndData", "zstblDefault")

  varDSN = DFirst("BackEndDSN", "zstblDefault")

  blnRet = lci_ODBCValidate(varBackEnd, Me!txtUserName, Me!txtPW, varDSN)

  If blnRet Then

    Beep

    lci_gstrCurrentUser = Me!txtUserName  ' Set global user name

    Set rst = dbs.OpenRecordset("zstblDefault", dbOpenDynaset)

    rst.MoveFirst

    rst.Edit

    rst!LastUserName = lci_gstrCurrentUser

    rst.Update

    rst.Close

    MsgBox "You are logged-in and validated.", _

      vbInformation, lci_gcstrAppTitle & " Login"

    DoCmd.Close acForm, Me.Name

  Else

    Me!txtUserName.SetFocus

  End If

End Sub

' A library routine

Public Function lci_ODBCValidate(rvarDbs As Variant, rvarUser As Variant _

  , rvarPW As Variant, rvarDSN As Variant) As Boolean

' Purpose:   Create and validate an ODBC connect string

'            Also creates initial (cached) Jet connection as byproduct

' Arguments: rvarDbs:=database name

'            rvarUser:=user name

'            rvarPW:=user password

'            rvarDSN:=data source name

' Returns:   True/False, True=logged in

' Example:   lci_ODBCValidate(varBackEnd, Me!txtUserName, Me!txtPW, varDSN)

  Const cstrProc As String = "lci_ODBCValidate"

  On Error GoTo lci_ODBCValidate_Err

  Dim cnn    As Connection

  Dim dbs    As Database

  Dim strCnn As String

  Dim wsp    As Workspace

  ' Build a connect string

  "ODBC;DATABASE=Pubs;UID=" & rvarUser & ";PWD='';DSN=Pubs"

  ' Create a Database connection (Jet)

  Set dbs = OpenDatabase( _

      Name:="" _

    , Options:=False _

    , ReadOnly:=False _

    , Connect:=strCnn)

  

  ' Create a Connection connection (non-Jet)

  Set wsp = DBEngine.CreateWorkspace( _

      Name:="ODBC" _

    , UserName:="sa" _

    , Password:="" _

    , UseType:=dbUseODBC)

  Set cnn = wsp.OpenConnection( _

      Name:="" _

    , Options:=dbDriverCompleteRequired _

    , ReadOnly:=False _

    , Connect:=strCnn)

  

  ' Save the connection string for our own use later

  lci_gstrCurrentCnn = strCnn

  lci_ODBCValidate = True

lci_ODBCValidate_Exit:

  On Error Resume Next

  dbs.Close

  cnn.Close

  wsp.Close

  Exit Function

lci_ODBCValidate_Err:

  Call lci_ErrMsgStd(mcstrMod & "." & cstrProc, Err.Number, Err.Description _

    , True)

  Resume lci_ODBCValidate_Exit

End Function

Here are several important considerations about the routines in the listing:

The connect string in the example uses the DATABASE, DSN, PWD, and UID arguments. While all ODBC drivers must support the keywords DSN, PWD, and UID, DATABASE is a SQL Server driver-specific keyword. To make my routine into a generic routine for multiple servers, you would have to add special handling for such server-specific keywords.

For Each eerrJet In DBEngine.Errors

  With eerrJet

    Debug.Print .Number

    Debug.Print .Description

    Debug.Print .Source

  End With

Next

Fig. 19.8

Two messages are in the Jet Errors collection after a pass-through query failure.

If some parameters are missing or invalid, ODBC may display its own login dialog to collect the required information. Canceling this dialog produces error number 3059, "Operation canceled by user." If you desire, you can trap this specific error number in your code and abort the application session (close Access) during login.

Set dbs = OpenDatabase("", False, False, "ODBC;")

This syntax allows users to login to any database for which they know the location and password, even one that does not match the application they are running. Unless your application is a "universal client" of some sort that works with more than one back-end, I do not recommend this approach.

When DAO has successfully established and cached initial server connections, it can reuse the connections for each request your application makes to the server. For some servers, such as Oracle, one connection may be sufficient for your application because a single connection can handle multiple statements passed back and forth between client and server.

For other servers, with SQL Server as an example, a single connection can only process a single statement. However, a single DAO task may require more than one statement. Also, DAO may decide to perform multiple tasks asynchronously. In these cases, DAO clones the initial connection into additional connections as needed, without burdening the user with an additional login dialog for each new connection. In fact, all programmatic and pass-through requests from your application that go to the same server and database as was accessed by the initial connection are handled invisibly by DAO's creation of extra connections on-the-fly.

Despite SQL Server's limitation of one statement per connection, DAO is able to use a connection to the server for more than one purpose if the data source name and the database name are the same for the different requests. This clever connection management provides you with another good reason to store connection information in a variable as I've just shown, and reuse the connection information over and over in your code. When presented with a consistent connection string coming from different requests, DAO can do some optimization of ODBC traffic.

Linking to ODBC Tables

Frequently, you will code into your Access applications the ability to link tables dynamically from a back-end Jet database when the database's location changes. In contrast, it is rare for the name of a server or a server database to change. Nevertheless, there are occasions when it is useful to programmatically link server tables to Jet.

To link a table from an ODBC server to a local database programmatically, use code like that shown in Listing 19.2.

Listing 19.2 AES_Srvr.Mdb-Dynamically linking an ODBC
table

Public Function lci_ODBCTblLink(rstrTblSrc As String, rstrTblDest As String) _

  As Boolean

' Purpose:   Link a table from an ODBC server

' Arguments: rstrTblSrc:=table name on server

'            rstrTblDesc:=table name in database

' Returns:   True/False, True=linked

' Example:   lci_ODBCTblLink("tblCust", "tsqlCust")

  Dim dbs As Database

  Dim tdf As TableDef

  Set dbs = CurrentDb

  Set tdf = dbs.CreateTableDef(rstrTblDest)

  tdf.Connect = lci_gstrCurrentCnn

  tdf.SourceTableName = rstrTblSrc

  dbs.TableDefs.Append tdf

  lci_ODBCTblLink = True

End Function

Data in a linked table can only be updated by the user or a process if it has a unique (primary) key on the server. If not, the data is read-only in all views. To determine if a recordset created from a linked table can be updated, use the Updatable property on the recordset, as in this example:

If rst.Updatable Then

Be careful not to test the Updatable property of the linked table itself instead of testing a Recordset object built from the table. The Updatable property of a TableDef object describes whether the design can be changed, not the data, and is always False for a linked table.

Introducing ODBCDirect

In the past, if you were a power-hungry developer or had power-hungry users, you had to find some way to invoke ODBC without the overhead of Jet when working with server data from code. You solved this problem either by writing code directly against the ODBC API, or by using the Remote Data custom control provided with Visual Basic 4.

The Access 97/Jet 3.5/DAO 3.5 toolset gives you a new weapon in the battle for improved performance. And unlike the two weapons previously mentioned, this one is actually easy to use. The tool is called ODBCDirect. ODBCDirect is simply the ability of the Data Access Objects engine to communicate with ODBC without loading any of Jet. This new technology will provide a significant performance boost to some of your code routines.

All Access database activity has always taken place within a Workspace object. Prior to Access 97, DAO and Jet were unified, and as a result the only type of workspace available from DAO was a Jet-centric one. At its simplest level, ODBCDirect is nothing more than the addition of another type of workspace (an "ODBC workspace" or "ODBCDirect workspace") to the DAO.

To create an ODBCDirect workspace, explicitly create a Workspace object in your code with the appropriate Type flag:

Set wsp = DBEngine.CreateWorkspace( _

    Name:="ODBC" _

  , UserName:="sa" _

  , Password:="" _

  , UseType:=dbUseODBC)

The Name property of a workspace must be unique within the containing application and cannot exceed 20 characters.

The UseType argument flag has two values: dbUseODBC and dbUseJet. To create a Jet workspace using the previous code lines, simply change the UseType flag. Alternately, leave the optional UseType argument off altogether because the default workspace created within Access uses Jet.

If your application will use ODBCDirect more than Jet, you can change the default workspace type by changing the DefaultType property of the DBEngine object when your application starts up.

If your code needs a default Workspace object of the default type, you can still issue the syntax you are familiar with from previous Access versions:

Set wsp = DBEngine.Workspaces(0)

The key component of an ODBCDirect workspace is a Connection object. Think of this object as a surrogate for the Database object in an environment where the database is irrelevant (technically speaking). Whereas a Jet Database object provides a container for QueryDef and Recordset objects, in an ODBCDirect environment the Connection provides this parentage. The object's role is to establish a link with a server database and then manage the traffic for QueryDef and Recordset objects that ride along that link.

The next two sections continue the ODBCDirect tutorial, first by clarifying the differences between ODBCDirect and Jet, and then by providing examples of using ODBCDirect in your code.

Comparing Jet and ODBCDirect

The DAO object model has seventeen objects (sixteen collections plus the DBEngine). Table 19.1 summarizes the version 3.5 object model and shows which objects are available in a Jet workspace, which can be used with ODBCDirect, and which apply to both.

The methods and properties listed in the table include only those that apply to the Database object. Methods and properties subordinate to other objects inherit their capabilities from their parent object and are thus not shown. For example, the ConflictTable property applies to a TableDef object; because a TableDef is not available in ODBCDirect, neither is the ConflictTable property.

On the Access Help topic "Data Access Objects Overview," click the See Also link. The resulting topic list includes several useful comparisons of the properties and methods in Jet and ODBCDirect.

Table 19.1 DAO Object Model Comparison Between Jet and ODBCDirect

Object

Jet

ODBCDirect

Connections collection


x

Containers collection

x


Databases collection

x

x

Documents collection

x


Errors collection

x

x

Fields collection

x

x

Groups collection

x


Indexes collection

x


Parameters collection

x

x

Properties collection

x

x

QueryDefs collection

x

x

Recordsets collection

x

x

Relations collection

x


TableDefs collection

x


Users collection

x


Workspaces collection

x

x

Close method

x

x

CreateProperty method

x


CreateQueryDef method

x

x

CreateRelation method

x


CreateTableDef method

x


Execute method

x

x

MakeReplica method

x


NewPassword method

x


OpenRecordset method

x

x

PopulatePartial method

x


Synchronize method

x


DBEngine object

x

x

CollatingOrder property

x


Connect property

x

x

Connection property


x

DesignMasterID property

x


Name property

x

x

QueryTimeout property

x

x

RecordsAffected property

x

x

Replicable property

x


ReplicaID property

x


Updatable property

x

x

V1xNullBehavior property

x


Version property

x

x

When do you use ODBCDirect instead of Jet? When you need to write code to perform SELECT, INSERT, UPDATE, or DELETE operations from VBA against an ODBC back-end, try using ODBCDirect first. ODBCDirect is best suited to simple record-based operations against server data.

ODBCDirect does not fully replace Jet. Here are circumstances where you will still use a Jet workspace in your client/server coding:

While ODBCDirect cannot create a table by using the CreateTableDef method, it can be used to pass SQL CREATE TABLE, ALTER TABLE, and other data definition statements to a server database instead. This provides similar functionality.

The strongest similarities between Jet and ODBCDirect are their common use of QueryDef and Recordset objects. For both types of workspaces, these objects provide the engines for data manipulation work, and your code against these objects will be very similar.

The primary difference between a QueryDef in Jet and one in ODBCDirect is that the ODBCDirect object can execute asynchronously when requested, while the Jet object can never do so. The second difference is that ODBCDirect QueryDef objects are not saved to the database; they are always transitory.

A third difference between the two types of QueryDef objects involves ODBCDirect's ability to accept parameter values back from the server. These are placed in the Parameters collection you are already familiar with for the QueryDef object. Thus you can set the value of qdf.Parameters(0), have the value passed to the server, and accept a returned value back from the server into the parameter. See the example in the section "Checking Messages Returned by the Server" later in this chapter.

For Recordset objects, the most important difference in the two technologies are the defaults applied to the creation of a new recordset, and the variety of options available to override those defaults. In a nutshell, a Jet recordset tries to become a read/write dynaset by default, while the default ODBCDirect recordset is a read-only snapshot. The topic "Comparing Dynasets and Snapshots" provides complete details of the OpenRecordset syntax for both types of workspaces.

Another important distinction evidenced in recordsets is that ODBCDirect manages caching more tightly than a Jet Recordset object, where you can set cache properties through code. An ODBCDirect recordset does not support the CacheStart property and FillCache method, and its CacheSize property cannot be set directly (these properties and methods are described in "Taking Advantage of Caching" later in this chapter). Instead, the cache attributes of an ODBCDirect Recordset object are inherited from its QueryDef. You can now apply the CacheSize method to a QueryDef, and the caching model defined will be propagated into any recordsets created from the query:

Set qsql = cnn.CreateQueryDef("")

qsql.SQL = "SELECT * FROM roysched"

qsql.CacheSize = 20  ' Arbitrary number for example

Set rsql = qsql.OpenRecordset()  ' Recordset's cache size is also 20

Other, more subtle differences between Jet and ODBCDirect objects will become evident as you read the next section.

Putting ODBCDirect to Work

With ODBCDirect, you can now write generic DAO code that runs against two different type of workspaces, often with no changes at all to the code. Generic code like this can sometimes benefit from knowing what kind of workspace it lives in. Your code can check the Type property of its parent workspace for the values dbUseJet or dbUseODBC to discern the workspace's character:

Public Function BatchUpdate(rwsp As Workspace) As Boolean

  ...

  If rwsp.Type = dbUseJet Then

  ...

Once you've established a workspace, your code must determine whether to use the Database or Connection object for its transactions. With a Jet workspace, you'll continue to use the traditional Database object. With ODBCDirect, you'll usually elect to use the new Connection object. The Connection object was designed specifically as a pipeline to server data. For example, it can manage multiple queries running simultaneously (asynchronously).

The object structure of the DAO makes it easy to prototype applications in Jet and then migrate them to a server. The switch is facilitated by the presence of a Connection object as a member of the Database object, and the reverse. Assume you have written DAO code that uses a Database object, and the database is attached to a Jet workspace. Now, you change the workspace to use ODBCDirect. By checking the Connection property value, your code can discover that it is now running in an ODBCDirect workspace.

You test for the presence of ODBCDirect this way:

Dim dbs As Database

Dim cnn As Connection

' The next line will generate a trappable error if the workspace is Jet

' If Err.Number is still 0 after the line, ODBCDirect is present

Set cnn = dbs.Connection

On the other hand, if your code knows that it will be running under ODBCDirect, testing for it is unnecessary and a Connection object can be used from the start:

Dim cnn As Connection

Set cnn = wsp.OpenConnection( _

    Name:="wspWork" _

  , Options:=dbDriverCompleteRequired _

  , ReadOnly:=False _

  , Connect:="ODBC;DATABASE=Pubs;UID=sa;PWD=;DSN=Pubs")

Here are the arguments to this method:

Once a Connection object is created, your code can create one or more QueryDef and Recordset objects against the connection. To create an ODBCDirect QueryDef object, you will use the same syntax you are familiar with from working with Jet:

Set qdf = cnn.CreateQueryDef("qsqlJobs", "SELECT * FROM jobs")

Notice that the QueryDef object is created against an ODBCDirect Connection object; this gives the query a direct pipeline to ODBC's routines for executing prepared statements (SQL strings). Also, the name of the QueryDef object is only used to identify it within the QueryDefs collection; with ODBCDirect, the object is never actually saved to the host database and a name string is optional.

With an ODBCDirect QueryDef object, you will generally either apply an Execute method to run an action query or an OpenRecordset method to create a Recordset object from the QueryDef. In either case, the syntax matches the structures you are familiar with from previous Access releases; the most noteworthy addition is the ability to use the new dbRunAsync flag on either method to prescribe that the query run in parallel with other processes:

qsql.Execute dbRunAsync

Set rsql = qsql.OpenRecordset(, dbRunAsync)

You cannot use dbRunAsync with Execute on a query hosted by a Database object; the query must be attached to a connection. However, you can use dbRunAsync on an OpenRecordset method attached to a Database object in the same manner that you can when a Connection object is involved.

When a query is running asynchronously, you can generate errors by attempting to access its result set before it completes its work. Your code must always check the query's processing via the StillExecuting property and wait for completion before addressing the resultant recordset:

Set rsql = qsql.OpenRecordset(, dbRunAsync)

Do While rsql.StillExecuting

Loop

rsql.MoveLast

You cannot have multiple processes running on the same connection. Check StillExecuting on a running process before issuing another process on the same connection. To run asynchronously, use multiple Connection objects.

If a process is still executing and your code needs to abort it, send the Cancel method to the object, as in:

rsql.Cancel

Note, however, that Cancel does not perform a rollback of the transaction in progress, it simply cancels it at the current point. You will generally want to wrap processes in a transaction if they could be canceled, and issue a Rollback statement in conjunction the Cancel.

See "Comparing Dynasets and Snapshots" later in this chapter for a complete discussion of ODBCDirect-based recordsets.

Sending Queries to the Server

There are several ways to send queries to a back-end server:

Whenever possible, a Jet workspace will try to transform your query into a request that the server can execute by itself, because any query processing that takes place on the client workstation slows your application. For example, if a user or process is allowed to join a local table with a remote transaction table, much of the value of the server is discarded-Jet will need to fetch indexes or records from the server and then perform the join work on the user's machine. (Although Jet has optimized this behavior in its latest incarnation, and will fetch only the server records that are needed to join to the local records, presuming you've joined the tables based on indexed fields.)

In contrast, an ODBC workspace does not have the same logic abilities as Jet. ODBCDirect passes statements through to the server with little to no preparation or added value. Your syntax and your server must be tuned during construction.

You can optimize Jet query processing by ensuring that little or no work is done by Jet other than submitting a data request to the server and managing the result set. Techniques for achieving this result include:

Jet will do some cleanup for you in an attempt to convert a function to a statement that can be sent to the server. Specifically, if your function or expression evaluates to a constant that can be placed in the WHERE clause as a criteria value, Jet does the evaluation in advance and passes the enhanced statement through to the server. Functions applied to table columns, however, require evaluation on a per-record basis and must be evaluated locally.

Getting the best performance out of the DAO's connectivity to server data through ODBC involves carefully structuring your application to take advantage of the way the connectivity works.

Avoiding Common Query Pitfalls

In order to illustrate ODBC connectivity and the roles each of the three players in a Jet-based remote data request (Jet, the ODBC layer, and the data server) play, let's dissect the processing of two example queries. Each is based on a Jet table tsqlSales linked to the table sales in the Pubs sample database on SQL Server.

The first query, qsqlSelNet30Sales_SQL, selects all of the sales records where the terms include the string "30", using the standard SQL LIKE operator. The second query, qsqlSelNet30Sales_VBA, also selects sales records where the terms include the string "30", but uses the VBA InStr() function in its criteria.

Figure 19.9 shows these two queries in design view.

Fig. 19.9

Two sample queries against a linked table, one of which uses standard SQL and one that uses a VBA function.

To understand Jet's interaction with the server, let's run these two queries and trace the interaction between Jet and the server as reflected in the ODBC trace files.

Tracing server statements involves telling Jet to write log records to a disk file, as described in the "Determining What the Server Received" section later in this chapter.

Running the query qsqlSelNet30Sales_SQL produces the following interaction with the server (as summarized from the trace logs):

  1. Jet executes an SQL statement (using ODBC's SQLExecDirect() function) to select the primary index values for the table that match the criteria. These values are copied into an ODBC result set, which essentially is an ODBC-managed recordset.
  2. Because the string sent to the server has the appropriate standard SQL WHERE criteria, the result set contains only the 8 requested bookmarks (index values). With this first request, Jet has not actually requested full data records yet, but instead has only asked for the three columns that make up the linked table's primary key:

    SELECT stor_id, ord_num, title_id
    
    FROM sales
    
    WHERE ("payterms" LIKE '%30%' )
    
    

  3. In Jet parlance, this bookmark-fetching query is called a keyset population query.
  4. Jet then fetches (using ODBC's SQLFetch() and SQLGetData() functions) the indexes from the ODBC result set into its own temporary table. Jet selects only key values in the first pass in order to build a list of index values that will be useful in future data requests. For example, in order to scroll forwards and backwards through a datasheet or Recordset variable, Jet must be able to retrieve specific records on demand.
  5. Next, Jet executes an additional SQL statement to select the actual table data you've requested in the QueryDef. In addition to the displayed fields, Jet again requests the key fields as part of this result set so that it can match its index recordset from the first two steps with its data recordset:

    SELECT stor_id, ord_num, qty, payterms, title_id
    
    FROM sales
    
    WHERE ("payterms" LIKE '%30%' )
    
    

  6. Finally, Jet fetches the data from the ODBC result set one record at a time into its own temporary table. This recordset is the data actually presented to your users or application through a datasheet, form, or DAO Recordset object.

In sum, Jet was able to grab exactly eight indexes and eight records from the server in two quick and simple operations, thanks to the query's use of standard SQL.

In contrast, the query qsqlSelNet30Sales_VBA's use of the VBA function InStr() produces a more wasteful interaction with the server, as follows:

  1. Jet executes an SQL statement (using ODBC's SQLExecDirect() function) to select the index values for the table. However, unlike the SQL-specific variation of this operation, Jet cannot pass an SQL WHERE criteria to the server, due to the presence of the VBA function. Consequently, Jet must ask the server for all indexes for the table.
  2. Further, Jet must request the value for the payterms field in addition to the three fields in the index, so that it will have the information necessary to do the InStr() evalution:

    SELECT payterms, stor_id, ord_num, title_id
    
    FROM sales
    
    

  3. Jet then issues 21 fetches to get the fields from the ODBC result set into its own (internal) recordset (using ODBC's SQLFetch() and SQLGetData() functions). Jet then performs the VBA InStr() function against each retrieved value of the payterms field in the recordset, and builds a list of indexes that match the records satisfying the function's criteria.
  4. Next, Jet creates another SQL SELECT statement, this one with a WHERE clause to request specific records from the server-those that met the VBA function's criteria. This operation uses the ODBC SQLPrepare() and SQLBindParameter() functions repeatedly to build a statement resembling the following:

    SELECT stor_id, ord_num, qty, payterms, title_id
    
    FROM sales
    
    WHERE stor_id = '7066' AND ord_num = 'A2976' AND title_id = 'PC8888'
    
       OR stor_id = '7067' AND ord_num = 'P2121' AND title_id = 'TC3218'
    
       OR <etc...>
    
    

  5. Jet then sends an ODBC SQLExecute() function call to execute the SQL string and build a result set containing only the desired records.
  6. Finally, Jet fetches the data from the ODBC result set one record at a time into its own recordset. This recordset is then displayed to the user or exposed to program code.

These two contrasting examples serve to illustrate the inner workings of Jet against an SQL Server host. More importantly, the examples clarify that Jet must request much more data and do much more local processing when asked to perform a query operation that it cannot simply submit to the server for remote resolution.

Creating Pass-Through Queries

Using an SQL pass-through query, you can forward any command or set of commands to the receiving server that are valid on the server. Valid commands are not necessarily limited to SQL queries against data tables. For example, with an ODBC connection to SQL Server, the DAO can pass through any of these strings:

In addition to providing your application with access to any features of the server exposed to ODBC (as in the previous list), the benefit of pass-through queries is that the Jet or ODBCDirect query parser ignores the query and simply sends it on to the server for evaluation. This moves the translation effort to the ODBC driver and the data host, which are the most capable devices for evaluating and complying with the request.

Stored procedures have a variety of uses on a database server. Because they are compiled, they provide a fast-executing method for performing repeated operations (in this respect, they have something in common with queries compiled and stored as Jet QueryDef objects).

Depending on the server's syntax capabilities, stored procedures can provide capabilities to accept arguments, create variables, return printed output, iterate through records, run SQL statements, and so on. Thus, any temptation you have to write VBA code against a recordset built from a server table should be moderated by first attempting to solve the same problem through a stored procedure on the server, and using your application to invoke the stored procedure via pass-through.

By nature, a pass-through SELECT query saved in a Jet database can only return a non-editable snapshot-type recordset. This makes it an effective object for read-only DAO operations, for row sources in ComboBox and ListBox controls, and for report sources, but not an effective query to supply forms with records that must be editable.

Editable recordsets can be created from QueryDef objects, but not from QueryDefs saved in Jet. Creating a Recordset based on a QueryDef based on a Database or Connection object linked directly to a server can produce an editable recordset.

You cannot create pass-through statements using the query design grid, you must use the SQL view of query design, as shown in figure 19.10. Jet does not evaluate the information you type into the SQL text box, so you are responsible for the validity of the statement.

Fig. 19.10

The SQL view of Access' query design view for a pass-through query, with the Properties window displayed.

Wherever possible when creating pass through queries that are SQL statements, link the server tables to an Access database first and use the query design grid to author the core of the request. When the request exceeds standard Jet join capabilities or syntax, change the query type from Select to SQL Pass-Through and finalize the query development in the SQL view text box. Delete the table links after you save the query.

Alternately, develop and test the pass-through statements in the Query Analyzer dialog in the SQL Enterprise Manager, then copy and paste the string into Access' query design view.

Executing a pass-through query from code without the use of a QueryDef object involves the use of the Execute method against a Database or Connection object. When performed against a Jet Database object, include the pass-through constant dbSQLPassThrough as shown in the example in Listing 19.3.

Listing 19.3 AES_Srvr.Mdb-Using the Execute method against a Database and
a Connection object to send a pass-through query

Dim cnn    As Connection

Dim dsql   As Database

Dim strSQL As String

Dim wsp    As Workspace

  

strSQL = "UPDATE publishers SET country = 'United States'" _

  & " WHERE country = 'USA'"

' Jet example

Set dsql = CurrentDb

dsql.Connect = lci_gstrCurrentCnn

dsql.Execute strSQL, dbSQLPassThrough

' RecordsAffected does not work against a Database object

' ODBCDirect example

Set wsp = DBEngine.CreateWorkspace("", "", "", dbUseODBC)

Set cnn = wsp.OpenConnection("cnn", , , lci_gstrCurrentCnn)

cnn.Execute strSQL

' RecordsAffected works against a Connection object

Beep

MsgBox CStr(cnn.RecordsAffected) & " records updated.", vbInformation, cstrProc

The Execute method flag dbSeeChanges has no effect on the Execute method with pass-through queries against a Database object. dbSeeChanges is useful for updatable recordsets, but not for pass-through queries that create snapshots or perform action queries. However, you must use dbSeeChanges when performing an SQL action against a linked table if the server table has an Identity-type column.

The dbFailOnError flag tells Jet to fail an entire bulk operation, as opposed to a partial failure that may arise under the default Jet behavior. Use this flag if your SQL statement is wrapped in a Jet transaction or you want Jet to create an implicit transaction that allows the operation to be fully rolled-back in the event of failure.

You can also use pass-through with a QueryDef object. This option is valuable to you if you want to save the pass-through query before or after it is executed, or use the query to create a recordset. Here are the steps:

  1. Create a QueryDef using the CreateQueryDef method on a Database object. Do not specify an SQL statement for the query during its creation:

    Set qspt = dbs.CreateQueryDef("qsptSelPublisher")
    
    

  2. Because a named QueryDef object is appended to the database immediately upon creation, an SQL string included at the time of creation as the second argument to CreateQueryDef() is evaluated by Jet and therefore must be a valid Jet SQL statement. By contrast, an SQL string added through the SQL property after the query has been assigned a connect string is not evaluated, and thus can be in a foreign server's syntax. For example, the name of a stored procedure is a valid command to pass to SQL Server, but would not survive Jet's scrutiny if parsed and compiled as a non pass-through query.
  3. Alternately, to create a temporary pass-through query that is not saved in the database, leave the query name argument blank:

    Set qspt = dbs.CreateQueryDef("")
    
    

A temporary (unnamed) QueryDef object like this produces the least possible impact on local (Jet) resources, because no QueryDef object is appended to the local database. Temporary query objects are ideal when your primary objective is to create a recordset from a pass-through SQL statement, like this:

Set qspt = dbs.CreateQueryDef("")

' Set query properties here

...

Set rsql = qspt.OpenRecordset()

  1. With ODBCDirect, all QueryDef objects are temporary. When you create a QueryDef against a Connection object, the name is transitory and not required, and no object is saved to the database:

    Set qspt = cnn.CreateQueryDef("")
    
    

  2. For a Jet QueryDef, set the Connect property for the query to address the ODBC server:

    qspt.Connect = lci_gstrCurrentCnn  ' Connect string saved from login
    
    

  3. You do not need to set this property for an ODBCDirect QueryDef, which is attached to a Connection object and inherits that object's connection information.
  4. Set the query's SQL property:

    strSQL = "UPDATE publishers SET country = 'United States'" _
    
     & " WHERE country = 'USA'"
    
    qspt.SQL = strSQL
    
    

  5. Decide whether or not the query will return records, then set the ReturnsRecords property accordingly. A SELECT query will return records while DELETE, INSERT, and UPDATE queries will not:

    qspt.ReturnsRecords = False
    
    

The default for the ReturnsRecords property is False, and Jet will not change the setting for you by checking the string assigned to the SQL property of the query. You must set the property to True yourself if the pass-through statement will return records.

  1. If the query returns records (the UPDATE query in this example does not), it can be assigned to a snapshot-type Recordset object, as in this syntax:

    Set rsnp = qspt.OpenRecordset(dbOpenSnapshot)
    
    

The example in Listing 19.4 pulls these steps together. The first block of code creates an action pass-through query, the second block assigns a row-returning query to a recordset.

Listing 19.4 AES_Srvr.Mdb-Three pass-through QueryDef
objects created from code- one is an action query and two return records

Dim cnn    As Connection

Dim dbs    As Database

Dim qspt   As QueryDef

Dim qsql   As QueryDef

Dim rsql   As Recordset

Dim strSQL As String

Dim wsp    As Workspace

' Jet example

Set dbs = CurrentDb

dbs.QueryDefs.Delete "qsptUpdPublishers_USA"

' Create and execute a pass-through action query

Set qspt = dbs.CreateQueryDef("qsptUpdPublishers_USA")

qspt.Connect = lci_gstrCurrentCnn

strSQL = "UPDATE publishers SET country = 'United States'" _

  & " WHERE country = 'USA'"

qspt.SQL = strSQL

qspt.ReturnsRecords = False

qspt.Execute dbFailOnError

dbs.QueryDefs.Delete "qsptSelPublisher"

' Create and execute a pass-through SELECT query

Set qspt = dbs.CreateQueryDef("qsptSelPublisher")

qspt.Connect = lci_gstrCurrentCnn

strSQL = "SELECT * FROM publishers WHERE country = 'United States'"

qspt.SQL = strSQL

qspt.ReturnsRecords = True

Set rsql = qspt.OpenRecordset(dbOpenSnapshot)

rsql.MoveLast

Beep

MsgBox CStr(rsql.RecordCount) & " records in Jet record set." _

  , vbInformation, cstrProc

' ODBCDirect example

Set wsp = DBEngine.CreateWorkspace("", "", "", dbUseODBC)

Set cnn = wsp.OpenConnection("cnn", , , lci_gstrCurrentCnn)

Set qsql = cnn.CreateQueryDef("", strSQL)

Set rsql = qsql.OpenRecordset(dbOpenSnapshot)

rsql.MoveLast

Beep

MsgBox CStr(rsql.RecordCount) & " records in ODBC record set." _

  , vbInformation, cstrProc

Access Help infers that, after a pass-through operation against a Database or QueryDef object, you can check the RecordsAffected property of the object to see how many records were altered by the most recent process, like this:

qspt.Execute

MsgBox CStr(qspt.RecordsAffected) & " records updated."

In fact, this property is only partially implemented, so use it wisely. If your Execute action runs SQL against a Jet table that is linked to a server table, this property is correctly set by Execute to reflect the number of records inserted, updated, or deleted. In contrast, if your Execute action operates as a pure pass-through statement directly against a named server table, not only is RecordsAffected not set, it is not reset. Thus, whatever value existed in this property from the last action against a Jet table or table link is still there. Do not query this property after a pure pass-through operation using Jet. The property is accurate when used with ODBCDirect Execute operations using a connection.

Once you assign a Connect property to the QueryDef, Jet determines that it is an SQL pass-through query. Oddly enough, Jet will then generate an error if you try to use the pass-through flag on the Execute or OpenRecordset statements, like this:

qspt.Execute dbSQLPassThrough  ' This generates an error

Set rsql = qspt.OpenRecordset(dbSQLPassThrough)  ' This generates an error

Using a SELECT pass-through QueryDef object, you can copy records into a table in your local database for more extensive processing using a Jet SELECT INTO query. As an example, replacing the OpenRecordset logic in the prior listing with the following statement would copy the selected records from the remote publishers table into a newly created local table zttblPub:

dbs.Execute "SELECT * INTO zttblPub FROM qsptSelPublisher"

To check the success of an insert operation like this, you will need to check for the existence of records in the target table. If the table is used for temporary processing, remember to delete the table when its usefulness expires.

Consider including a table name prefix on each field references in SQL statements that you write in code. For example, the following code is very clear in its intent:

SELECT stores.stor_name, stores.city, sales.qty

FROM stores, sales 

WHERE stores.stor_id=sales.stor_id;

Code like this is reliable even if the database structure expands. In contrast, if a qty field were added to the stores table in the future, and your query was saved with a non-qualified derivative of the syntax shown, the query would fail with an ambiguous reference because both tables would have a qty field.

Using Complex Pass-Through Queries

If your pass-through query returns multiple recordsets, the situation is slightly more complex than with a simple SELECT or UPDATE statement. Multiple result sets can be returned by calling a server's stored procedure or by sending the server a batch of commands in one pass-through statement.

DAO can handle multiple return sets, but not within Jet recordsets. Instead, you must tell Jet how to direct the returned records to tables, and the data returned will then be placed in your database in the designated tables. When using ODBCDirect, DAO supports multiple recordsets within one returned data set.

For the purpose of the following example, we'll use the stored procedure byroyalty in the Pubs SQL Server database. We'll modify the stored procedure to take two parameters (percentage and percentage2) instead of one, and to consequently return two result sets, as shown in Listing 19.5.

Listing 19.5 AES_Srvr.Mdb-Modifying the byroyalty stored procedure in
the Pubs database to return two result sets

CREATE PROCEDURE byroyalty @percentage int, @percentage2 int

AS

SELECT au_id FROM titleauthor

WHERE titleauthor.royaltyper = @percentage

SELECT au_id FROM titleauthor

WHERE titleauthor.royaltyper = @percentage2

Assume you want to return two result sets from the server using this stored procedure, one with author ID values for authors where the royalty percentage is 50, and one for 75. The mechanism using Jet is as follows:

  1. Create and save a pass-through query that executes the statement which will return multiple results. In the current example, we want to run a single pass-through query that will return two recordsets.
  2. The following code creates a query that runs the stored procedure:

    Set qspt = dbs.CreateQueryDef("qsptMakByRoyalty_2Result")
    
    qspt.Connect = lci_gstrCurrentCnn
    
    strSQL = "byroyalty @percentage=50, @percentage2=75"
    
    qspt.SQL = strSQL
    
    qspt.ReturnsRecords = True
    
    

  3. Execute the pass-through query by using a make-table Jet statement to direct the returned results to a table:

    dbs.Execute "SELECT * INTO zttblRoyalty FROM qsptMakByRoyalty_2Result"
    
    

  4. Obviously, Jet cannot return the two result sets from the single stored procedure into the single local table named in the statement, because there is no guarantee that the two result sets will have the same field structure (even though they do in my simple example). To solve the problem, Jet appends table version numbers to the name you prescribe for each table returned. Thus, in the previous example, the first dataset returned from the server is placed in a table named zttblRoyalty, but the second result set is directed to zttblRoyalty1.
  5. Utilize the returned data as required, then delete the returned tables when they are no longer needed. Note that the tables created by this process are native Jet tables, and are divorced from any relationship with the server (thus my use of the zt prefix to specify that these are temporary, work tables).

Because Jet is actually assigning table names for you in a process like this, you need to take care to allow Jet to use the names you expect, or you need to write a routine that checks the DAO for the most recently added tables so you can discern what Jet has done.

The cleanest scenario is to simply reserve specific table names for a specific process. Using the previous code as an example, only your routine to retrieve customer data from the server should make use of the table name zttblRoyalty; this table name should not be used by any other process. Consequently, your routine can safely delete zttblRoyalty and zttblRoyalty1 before executing the procedure that creates new versions of them, and thus the result sets created by your pass-through operation will be predictable in advance.

The process of handling multiple result sets in ODBCDirect is different than with Jet. ODBCDirect is able retrieve multiple Recordset objects from a single statement (however, none of the result sets will be updatable). The technique involves telling the server not to use cursors to handle the data; this creates a fetch engine returning one record at a time. Under this model, SQL Server can return multiple result sets.

Here are the steps to get ODBCDirect working against a multiple Recordset result set:

  1. Create a QueryDef and set its CacheSize property to 1. This causes DAO to request one record at a time from the server.
  2. Open a Recordset against the QueryDef. Use the flage dbOpenForwardOnly and dbReadOnly to force the server to use no cursors.
  3. Alternately, set the Workspace object to use no cursors by setting dbUseNoCursor on the Workspace's DefaultCursorDriver property. This achieves the same result steps 1 and 2.
  4. Walk through each recordset. First, traverse the initial recordset until the end is reached. Then, issue the NextRecordset method to determine if another result set is queued up. If it is, traverse that recordset, and so on.

Listing 19.6 shows the code for this process. It uses the byroyalty stored procedure that was created in Listing 19.5.

Listing 19.6 AES_Srvr.Mdb-ODBCDirect code that returns and reviews two
recordsets within a single result set

Set wsp = DBEngine.CreateWorkspace("", "", "", dbUseODBC)

wsp.DefaultCursorDriver = dbUseNoCursor  ' Disable cursors

Set cnn = wsp.OpenConnection("cnn", , , lci_gstrCurrentCnn)

' Run the stored procedure; it returns 2 result sets

' OpenRecordset("sqlstatement1; sqlstatement2") would work too

Set rsql = cnn.OpenRecordset("byroyalty @percentage=50, @percentage2=75")

Do  ' Loop through the recordsets

  Debug.Print "Recordset:"

  While Not rsql.EOF

    Debug.Print rsql.Fields(0)  ' Dump the first field

    rsql.MoveNext

  Wend

  ' Get the next Recordset in the result set

Loop Until (rsql.NextRecordset = False)

In addition to calling a stored procedure with multiple result sets, your advanced pass-through queries can send any legitimate server syntax. One example is multiple SELECT statements, as in the following lines:

' Jet example

strSQL = "SELECT * FROM authors; SELECT * FROM discounts"

qspt.SQL = strSQL

' ODBCDirect example

Set rsql = cnn.OpenRecordset( _

  "SELECT * FROM authors; SELECT * FROM discounts")

These compound statements sent as a pass-through query will return two result sets back to your application.

Another example of compound statements involves multiple stored procedures. When sending more than one stored procedure name in a pass-through statement, use the EXEC keyword:

EXEC byroyalty 50

EXEC byroyalty 75

Inserting Records on the Server

When creating pass-through queries to insert records into a server table, you have more than one option for approaching the task. Which approach you select will be based upon the client/server coding model you adopt for Access applications, as well as performance and security considerations.

Here are the options:

dbs.Execute "INSERT INTO jobs VALUES ('Boss',50,100)", dbFailOnError

Rather than rely on implicit transactions, it is a cleaner coding model to create an explicit transaction with BeginTrans in your code whenever you are performing an operation against a server. DAO will use your transaction for its SQL operations, and the transaction provides your code with a way to rollback the process for any reason it deems important.

SELECT * FROM tsqlJobs WHERE False

SELECT * FROM tsqlJobs WHERE job_id = -1  ' Non-existent job

INSERT INTO qsqlSelJobs (job_desc, min_lvl, max_lvl)

VALUES ('Boss',50,100)

Did you observe that the previous INSERT statement specifically identified the target fields in the destination table by name? This syntax is not always required, but I selected the jobs table for my example specifically to make this point: the jobs table has a field defined as type Identity on the server (akin to Jet's AutoNumber type). This field is the first field (job_id) in the table. As such, a generic INSERT statement like the following:

INSERT INTO jobs VALUES ('Boss', 50, 100)  ' This works

will run against the server directly, because no attempt is made to insert data into the Identity field. However, this similar syntax will not run against the server table linked to Jet, or any query wrapped around such a table link:

INSERT INTO qsqlSelJobs VALUES ('Boss', 50, 100)  ' This doesn't work

While this Jet behavior is odd, specifying the target fields for the insertion by name (and not including the Identity field in the list) will allow you to work around it. In general, you should specify column names in an INSERT statement if you are not inserting into all columns.

Unlike the security of working with Jet data, where you can create a recordset with LockEdits set to True to enable pessimistic locking and guarantee your updates, bulk operations on a server that doesn't support pessimistic locking are more risky. Therefore, always use a transaction wrapper around bulk server operations and roll back the transaction at the first sign of trouble.

In some applications, clever record inserts can be done from the server to the server, bypassing Jet altogether. Audit routines are a good example: when server data is manipulated, it causes a server process to be triggered that spawns another process (such as writing an audit log to a log table).

SQL server provides such capabilities for purely remote processing through stored procedures and triggers. A trigger is an event that fires when table data is modified. Triggers can contain some simple programming commands. You can create them on the server or using DAO pass-through statements.

For example, assume you need a trigger on the Pubs sample database that creates a new record in the discounts table whenever a new store record is inserted into the stores table. Under no circumstances will your VBA code do this job faster than the server can, so it's a natural choice for embedding in a trigger. You would use a pass-through query to create an insert trigger to handle this task; the query would look like this:

CREATE TRIGGER trgCreateDiscount ON dbo.stores 

FOR INSERT

AS

INSERT INTO dbo.discounts (discounttype, stor_id, discount)

SELECT ('<new>'), inserted.stor_id, (0) FROM inserted

In the previous example, the new record's ID is fetched from the table named inserted, which is a buffer in SQL Server triggers that contains a copy of an inserted record until the insertion is committed.

Using Parameter Queries

Parameter queries are handy devices. Without them, the only option available for setting parameters in a saved query would be to open the QueryDef and somehow rewrite the WHERE clause.

Because server databases do not understand the Jet PARAMETERS keyword, your first intuition regarding parameter queries may be that Jet or DAO must send a broad request and then process the results locally. In fact, while DAO indeed must take such a query and prepare a statement for the server from it, DAO does so quickly and efficiently, and makes the server do the work.

It is a good idea to include the PARAMETERS keyword in your saved parameter queries destined for a server. If you do not explicitly assign a data type to each parameter in a query, Jet will make its best guess based on the context of the query's fields. (In earlier versions of Jet, queries without typed parameters were resolved locally. This behavior has been replaced by the "best guess.")

For example, review the code in Listing 19.7, which creates a parameter query programmatically. (The query could also be created through the Access interface; query parameters are assigned from the Query, Parameters... menu option). The query is based on a table tsqlJobs linked to the Pubs database on the server, and it is a parameter query expecting a single parameter value.

Listing 19.7 AES_Srvr.Mdb-Creating and executing a parameter query against
a linked ODBC table

Dim dbs    As Database

Dim qsql   As QueryDef

Dim rsql   As Recordset

Dim strSQL As String

Set dbs = CurrentDb

' Create a parameter query pointing to the linked table

strSQL = "PARAMETERS MinLevel Byte;" _

  & " SELECT * FROM tsqlJobs WHERE min_lvl > [MinLevel];"

Set qsql = dbs.CreateQueryDef("qsqlSelJobLevel", strSQL)

qsql.Close

' Reuse the query over and over with this code

Set qsql = dbs.QueryDefs("qsqlSelJobLevel")

qsql.Parameters("MinLevel") = 150

' The following syntax is also valid in place of the previous line

' qsql!MinLevel = 150

Set rsql = qsql.OpenRecordset(dbOpenDynaset)

Tracing the interaction between Jet and the server when executing this parameterized query shows that Jet does not fetch all the records from the target table and evaluate them locally against the parameter value, which would be the slowest possible resolution method. Instead, Jet rebuilds the query's SQL statement with the parameter before sending it to the server, returning only the three desired records.

You might think that a parameter query using a linked table may not run as fast as creating the corresponding SQL statement in your program code and executing the statement as a pass-through query. And some of the time, you would be correct. However, in many cases the saved parameter query will provide better performance than either Jet or ODBCDirect pass-through. Why?

Take great care to make certain that parameter values passed to the server are of the expected type, avoiding the temptation to grab a value from a form control or InputBox() function and pass it to a QueryDef object's parameter untested. In order to properly test values, your code should know the data type of the server field to which the parameter will be sent, and how to test the parameter value supplied by a user to see if it's valid for the target field.

In the previous code listing (19.7), the query passed a parameter value to the SQL Server field min_lvl, which is defined as the TinyInt type on the server. As such, the allowable range of values for the field is from 0 to 255. Jet's analogous data type to TinyInt is Byte, so I used this as the query parameter type.

In other cases, Jet may not have an identical analogous value to map to a server data type. Consequently, it is possible to pass a value to a parameter query that survives Jet's inspection but generates an error on the server.

Do not rely on Access to provide you with valid data type information that maps to the server's data type for linked tables, because the table design view does not have a way to represent non-Access data types. For example,, if you were to look at the table link to the roysched table in design view, you would see the field type for hirange represented as a Long Integer in the Access property list, not a SQL Server Integer (its true data type). This disparity is shown in Figure 19.11.

Thus, if you had the SQL Server data structure in front of you and were building a parameter query in the Access design grid for the roysched table, you might be inclined to set the type of the parameter for the hirange field to Integer (the SQL data type). Unfortunately, this would restrict users, developers, and Jet from passing larger values to the server field, values that are quite acceptable. The corollary can also be true-sometimes Access will represent the closest field data type match in the table design grid and the match actually overstates the values accepted by the field. In this scenario, users or developers may attempt to pass a value to the server that is valid for Jet overflows the field type linked from the server, which will generate ODBC errors like these:

3146  ODBC--call failed.

 220  Arithmetic overflow error for type tinyint.

Fig. 19.11

Access does not represent the true data type of a server field in a linked table, it represents the nearest matching Jet data type.

For a table that compares Jet data types to SQL Server data types, see the Help topic "Equivalent ANSI SQL Data Types."

Working with parameters in an ODBCDirect environment is somewhat different than with Jet. ODBCDirect supports SQL parameters but does not support them as named parameters. The syntax for a parameter in a statement that it sends to ODBC is the question mark (?) character in the location where Jet would use a parameter name. If you want to create a statement to send repeatedly to the server and change only the parameters, use one or more of the placeholder ? characters in the statement:

SELECT * FROM jobs WHERE min_lvl > ?

In the Parameters collection for the SQL statement, the placeholders become parameters with names Parameter1, Parameter2, and so forth. Using parameters, you can create reusable ODBCDirect statements like the one shown in Listing 19.8.

Listing 19.8 AES_Srvr.Mdb-Creating and executing a parameter query using
ODBCDirect

Dim cnn    As Connection

Dim qsql   As QueryDef

Dim rsql   As Recordset

Dim strSQL As String

Dim wsp    As Workspace

Set wsp = DBEngine.CreateWorkspace("", "", "", dbUseODBC)

Set cnn = wsp.OpenConnection("cnn", , , lci_gstrCurrentCnn)

strSQL = "SELECT * FROM jobs WHERE min_lvl > ?;"

Set qsql = cnn.CreateQueryDef("", strSQL)

' Set the parameter

qsql.Parameters(0) = 150

' The following syntax is also valid

' qsql!Parameter1 = 150

Set rsql = qsql.OpenRecordset(dbOpenSnapshot)

The value of parameterized SQL statements can be inferred from the listing. ODBC keeps a copy of the SQL string in its buffer as long as the QueryDef qsql exists; neither ODBC nor DAO need to re-evaluate the statement's structure when a parameter value is changed. Thus, the code above could create many recordsets in quick succession by simply setting the parameter value, creating a recordset from qsql, changing the parameter, creating a different recordset, and so on.Checking Messages Returned by the Server

Some ODBC database servers are capable of passing status messages back to the client application after the execution of a pass-through query. To trap or ignore such status messages returned by a server, set the LogMessages property of a saved Jet query.

This property does not exist in Jet unless you create it from code when you build or modify a pass-through QueryDef object, or if you set the property in the Properties dialog in query design view. The following lines show an example of creating the property on a new QueryDef object:

Set prp = qspt.CreateProperty("LogMessages", dbBoolean, True)

qspt.Properties.Append prp

Jet queries with the LogMessages property set to True will create a table to hold message strings returned from the server's execution of a pass-through query. In this context, a "message" is not an error string-errors are returned to the Jet Errors collection. Instead, messages are non-recordset information passed back from a server process. Different servers implement this functionality differently, but the simplest SQL Server example is afforded by the PRINT statement. This command can be placed in stored procedures to return a string back to the client application.

A message logging table will contain one or more server messages resulting from an action. The name of the message table is the name of the currently logged user, plus a space, a dash, a space, and a sequential number from 00 through 99. For example, if the current user is Admin, the message table names will be Admin-00, Admin-01, and so on.

To clarify this situation, let's once again modify the stored procedure byroyalty in the Pubs sample database, as shown in Listing 19.9. This time, we'll add a PRINT statement to the end of the procedure to echo back to the client application the parameter value that was passed in to the procedure, as follows:

Listing 19.9 AES_Srvr.Mdb 7ÄModifying the byroyalty stored procedure in the Pubs database to return a message with PRINT

CREATE PROCEDURE byroyalty @percentage int

AS

DECLARE @pct CHAR(5)

SELECT au_id FROM titleauthor

WHERE titleauthor.royaltyper = @percentage

SELECT @pct = '>>' + CONVERT(CHAR(3), @percentage)

PRINT @pct

From Access' perspective, the stored procedure returns a result set (table records) for the SQL SELECT statement, but a message string for the PRINT statement. Listing 19.10 shows an example of code that creates a query to run the revised stored procedure byroyalty, which generates a results table and a message table.

Listing 19.10 AES_Srvr.Mdb-Creating a pass-through query
that logs server messages in a local table

Const cintPct As Integer = 50

Dim dbs       As Database

Dim prp       As Property

Dim qspt      As QueryDef

Dim strSQL    As String

Set dbs = CurrentDb

' Create a pass-through query on the stored procedure

Set qspt = dbs.CreateQueryDef("qsptSelPrcByRoyalty_Msg")

qspt.Connect = lci_gstrCurrentCnn

' cintPct is the stored procedure argument

strSQL = "byroyalty @percentage = " & CStr(cintPct)

qspt.SQL = strSQL

qspt.ReturnsRecords = True

' Create the LogMessages property 

Set prp = qspt.CreateProperty("LogMessages", dbBoolean, True)

qspt.Properties.Append prp

' Create the results table and messages table

dbs.Execute "SELECT * INTO zttblRoyalty FROM qsptSelPrcByRoyalty_Msg"

Figure 19.12 shows the contents of the message table generated when the code in the listing is run with a percentage argument value (cintPct) of 50.

Fig. 19.12

A table and record generated by the LogMessages property on an ODBC pass-through query to a stored procedure.

When dissecting server messages like the one shown in the figure, your code will need to parse off the driver information prefix characters in order to retrieve the actual value returned. Consider adding special characters to the message string created by the server to facilitate this parsing, as demonstrated by the ">>" characters in my example.

The method for returning server information to an ODBCDirect operation is quite different from the table-based metaphor just described. The basis for passing values to and from a server operation in ODBCDirect is QueryDef parameters, as described in the previous section "Using Parameter Queries." In that section, I described how parameters in ODBC statements are represented with placeholder characters (?). This is ODBC syntax, not DAO or server syntax. The following syntax creates a statement with a replaceable parameter and sets the parameter to 150:

strSQL = "SELECT * FROM jobs WHERE min_lvl > ?;"

Set qsql = cnn.CreateQueryDef("", strSQL)

qsql.Parameters(0) = 150

This syntax construction can also be carried over to stored procedures, which have two different types of parameters: arguments and a return value. Consider the stored procedure created in Listing 19.11, which accepts a passed-in value and returns a different value (similar to the way a VBA Function procedure returns a value).

Listing 19.11 AES_Srvr.Mdb-Creating a stored procedure that accepts
an argument and also returns one

CREATE PROCEDURE byroyalty @percentage int

  AS UPDATE titleauthor

  SET titleauthor.royaltyper = (@percentage + 1)

  WHERE titleauthor.royaltyper = @percentage

  RETURN @@rowcount

Return values from stored procedures provide the flexibility of Jet's message logging table as described earlier in this section, but provide a more graceful approach. The procedure in the previous listing does the following:

To call this stored procedure from an ODBCDirect statement, you must use proper ODBC grammar:

Set qsql = cnn.CreateQueryDef("", "{? = CALL byroyalty(?)}")

The grammar rules evidenced by this statement are quite strict:

In summary, the syntax to call a stored procedure with and without a return value looks like this:

Set qsql = cnn.CreateQueryDef("", "{? = CALL byroyalty(?)}")

Set qsql = cnn.CreateQueryDef("", "{CALL byroyalty(?,?,'Bob')}")

The placeholder arguments must be set somehow so that they become constants sent to the server, and the value returned by the server procedure also must have a destination. Both of these circumstances are facilitated by Parameter object values. The following code uses parameters to pass values to the stored procedure:

Set qsql = cnn.CreateQueryDef("", "{CALL byroyalty(?,?,'Bob')}")

qsql.Parameters(0).Value = 50  ' Replaces the first ?

qsql.Parameters(1).Value = 60  ' Replaces the second ?

qsql.Execute

The next code passes one value to a stored procedure and gets one back:

Set qsql = cnn.CreateQueryDef("", "{? = CALL byroyalty(?)}")

qsql.Parameters(1).Value = 60  ' Replaces the second ?

qsql.Execute

varRet = qsql.Parameters(0).Value  ' Gets value from first ?

The following syntax for a CALL statement with a return value is also valid according to the ODBC standard but generates an error when used from ODBCDirect:

{CALL ?=byroyalty(?)}

When using Parameter objects to set and get stored procedure values, you must provide a type constant to the parameter's Direction property so that it can establish the proper communication with its ODBC channel. Here are the values:

Listing 19.12 demonstrates code that calls the procedure created in Listing 19.11, setting and retrieving the procedure's argument values.

Listing 19.12 AES_Srvr.Mdb-Calling a stored procedure that accepts
and returns a value with ODBCDirect

Dim cnn    As Connection

Dim qsql   As QueryDef

Dim strSQL As String

Dim wsp    As Workspace

Set wsp = DBEngine.CreateWorkspace("", "", "", dbUseODBC)

Set cnn = wsp.OpenConnection("cnn", , , lci_gstrCurrentCnn)

Set qsql = cnn.CreateQueryDef("", "{? = call byroyalty(?)}")

  

' Set the parameters

qsql.Parameters(0).Direction = dbParamReturnValue  ' Value from procedure

qsql.Parameters(1).Direction = dbParamInput  ' Value to procedure

qsql.Parameters(1).Value = 50

qsql.Execute

' At this point qsql.Parameters(0).Value contains the return value

Working With Recordsets

By default, when Access creates a recordset object to fetch server data, it asks Jet to try to make the recordset an updatable dynaset. When creating the dynaset, Jet does not actually fetch records until Access needs to display them. Instead, Jet fetches all the index values of the requested records and uses these keys to retrieve selected records as Access needs them.

Jet is actually quite efficient in optimizing this type of fetch operation. For example, when it retrieves a record from SQL Server that contains Text (memo) or Image (OLE object) fields, Jet does not request these fields from the server unless Access actually needs them, because fetching these field types is fairly inefficient.

Beginning with Access 97, your code can also bypass Jet completely and use the ODBCDirect capabilities of the Data Access Objects to communicate with server data directly. Using this new feature will provide a performance boost to your client/server code, and also give you almost unlimited control of the communication processes between client and server and the manipulation of data by the server, as the next three sections will make apparent.

Comparing Dynasets and Snapshots

As described in the section "Manipulating Server Data" earlier in this chapter, Jet's dynaset processing is based on keysets, which fetch data from the server in chunks. With ODBCDirect, you can also use keysets, but you can utilize any other type of cursor processing available on the server as well. You can configure the behavior of ODBCDirect recordsets at a more detailed level than with Jet recordsets.

You can also tell DAO to retrieve records into a snapshot-type recordset, and all of the requested data for the records is returned back to the DAO for processing. This would seem to be less efficient than building a dynaset-type recordset, which initially fetches only keys and not data, and in fact it usually is. In general, creating a dynaset from a server will be faster than creating a snapshot (the larger the recordset, the more true this is).

Regardless of the type of recordset that you are using to fetch data from the server, always request only the fields your application actually needs.

The exception to the "dynaset is faster" rule-of-thumb occurs when the snapshot is small, in which case DAO can build the read-only snapshot faster than the dynaset because a snapshot executes a single query on the server and then returns the results. Dynaset creation executes multiple queries-one to populate the keyset and another to fetch the data corresponding to the records in that keyset (see the section "Manipulating Server Data" earlier in this chapter for specifics). A small snapshot in this context is usually around 500 to 1000 records, depending on the number of fields and other factors. You will need to experiment to determine where the performance threshold is in your specific application.

If your snapshot contains memo fields or OLE object fields, it will usually always be slower than a dynaset, due to the overhead of these specific field types. Use a dynaset when either of these field types must be retrieved.

Snapshots are primarily a Jet concept. ODBCDirect thinks primarily in terms of different types of dynasets, as will be discussed as this section unfolds. If you are using Jet and have determined that a snapshot is more appropriate than a dynaset in a specific context in your application, consider whether or not the snapshot needs to scroll backwards though the data with MovePrevious. If not, you can pass the dbForwardOnly flag when calling the OpenRecordset method and create a forward-only snapshot. Creating and navigating the snapshot will be faster if Jet does not need to account for moving backwards.

This is also true of ODBCDirect recordsets, which can be set to move in a forward direction only, and can be configured for a variety of data access behavior. The following list describes the different types of ODBCDirect recordsets that you can create with OpenRecordset, including the related Type arguments. Because record navigation in ODBCDirect is performed by the server, a recordset's behavior is a function of the type of cursor built by the server to feed it:

While Jet attempts to build an updatable dynaset when it can, ODBCDirect attempts by default to build the fastest Recordset, which is based on the dbOpenForwardOnly and dbReadOnly arguments and is non-editable. If you want an editable recordset you have to supply values that override this behavior

When determining what type of recordset to use and whether to use ODBCDirect or Jet, your decision will be based on each type of data operation and its unique needs. ODBCDirect is superior to Jet in these areas: asynchronous queries, returning multiple Recordset objects, batch cursor processing, and record throughput.

However, ODBCDirect is also more restrictive than Jet. It does not allow you to select the index to use on your Recordset object, does not support Seek, does not allow for modification of server table objects via the DAO object model, and cannot perform FindFirst, FindNext, FindPrevious, or FindLast. Clearly, then, there will be times where you still want to employ Jet recordsets to solve specific problems.

ODBCDirect has five values that can be compounded in the LockEdits argument to tailor locking behavior:

dbReadOnly. The recordset is not editable.Finding the best mix of a recordset's type, options, and locking behavior will be a trail-and-error proposition given the newness of ODBCDirect. Nevertheless, its flexibility and power are a major step forward in client/server computing.

Updating External Recordsets

A recordset built on a linked table pointing to an ODBC data server will be updatable if the remote table has a unique key. When using such a recordset, the Edit method may be applied to the recordset to update the record via code, and the user can also edit data via a datasheet or form based on the table link.

Note, however, that Jet cannot utilize pessimistic locking on server datasets, so all ODBC-related locking will be optimistic and the LockEdits property of the recordset cannot be changed from False. Because optimistic locking is the default behavior for Access datasheets and forms, the locking behavior of these objects against linked server tables is similar to the default behavior you are familiar with against native Jet tables.Opening a Recordset object against a linked server table or a query pointing to it is no different than opening one against a Jet-based table or query:

Set rsql = dbs.OpenRecordset("qsqlSelJobLevel")

Set rsql = dbs.OpenRecordset("tsqlAuthors", dbOpenDynaset)

Jet will do its best to create an editable dynaset-type recordset where possible. To force the creation of a non-editable snapshot, use the dbOpenSnapshot flag instead of dbOpenDynaset.

Because Jet creates a dynaset by default when it can, the dbOpenDynaset flag is optional when working with linked tables.

Here are other OpenRecordset flags of interest when working with linked recordsets:

This flag does not create pessimistic multi-user locking, although you might assume so from its name. Regardless of whether you include this flag, Jet still traps concurrency conflicts between different users editing the same linked table record, and produces the same "Data has changed; operation stopped" message that you are familiar with from working with native Jet tables.

If you open a recordset against a linked table that is already in use by another process on your machine (recordset, datasheet, or form) and do not use this flag, an error will be generated.

Jet's concurrency model for recordsets is based on key fields. Jet can be the most successful when updating records if comparing the key fields in its recordset with the key fields in the source record on the server produces an accurate comparison. You can do two things to help insure this accuracy:

The presence of floating-point data columns in a server table can cause concurrency check failures if a time stamp is not present. If you have floating-point data anywhere in the recordset, you should include a time stamp column in the source table.

The section "Linking to ODBC Data Sources" earlier in this chapter discusses the importance of unique keys in creating updatable recordsets, and the creation of local key information for linked tables.

Taking Advantage of Caching

I've Got a Boat to Cache

When I was wandering through Europe years ago, I came to an Italian port city to catch a boat to Greece. There was a line of hundreds of passengers waiting to be crammed into the small boat, and only a small gangplank going down to the dock.

The Italian officials wisely let twenty or thirty people down the gangplank to the dock, then wandered through the group checking passports and tickets. When they had inspected the entire group, it was hustled onto the boat and another thirty people were moved to the dock. In this manner, officials were processing people as fast as they could move through the small group, instead of waiting on each individual person to navigate the small gangplank down to the dock.

This batch processing is, effectively, what "caching" is all about.

When working with recordsets that are built from attached server tables, Jet allows you to configure its use of cache resources to optimize the effort of "loading the boat" (see the sidebar). Specifically, you can establish a cache (a memory buffer area on the workstation) and then fill it with a block of server records. Any time the recordset addresses a record that is in the cache, no server activity takes place. To a smaller degree, you can also tune the caching behavior of ODBCDirect recordsets.

Jet provides the following properties and methods for use from code that addresses a DAO Recordset object built from server data and a Database object:

FillCache Method. Executing this method fills the cache with server data starting at the row specified by the CacheStart bookmark and fetching the number of records specified by the CacheSize property.Figure 19.13 shows a diagram of this process.

Fig. 19.13

Jet can be told to retrieve a specific number of records into a cache.

Listing 19.13 shows a routine that fills the cache and fetches records from it. The listing has these attributes:

Issuing a MoveNext that positions the record pointer to a record outside of the cache will not generate an error. Jet merely fetches a record that is not in the cache from the server individually, circumventing your caching scheme and its benefits.

You probably use the Option Compare Database or Option Compare Text statement in the Declarations section of your code modules. When using the CacheStart property in a procedure, the Declarations section of the containing module must have an Option Compare Binary statement instead. The bookmark of an ODBC recordset using the CacheStart property is unlike a standard Jet recordset bookmark, which is a string, and must be treated as binary data. (A remote recordset's bookmarks are actually created by compounding a record's primary key field values together.)

Listing 19.13 AES_Srvr.Mdb-Directing Jet to fetch data
into a recordsetcache by blocks

' In the Declarations section

Option Compare Binary

' A procedure to demonstrate caching

Sub CacheExample(rlngCacheSize As Long)

' Purpose:   Use the FillCache method to cache records from server

' Arguments: rlngCacheSize:=Cache size from 5 to 1200

  Dim dbs    As Database

  Dim intRec As Integer

  Dim rsql   As Recordset

  

  Set dbs = CurrentDb

  Set rsql = dbs.OpenRecordset("tsqlRoySched", dbOpenDynaset)

  rsql.CacheSize = rlngCacheSize

  Do Until rsql.EOF

    If intRec = rlngCacheSize Then

      Debug.Print "Fill cache"

      rsql.CacheStart = rsql.Bookmark

      rsql.FillCache

      intRec = 0

    End If

    Debug.Print rsql!title_id

    rsql.MoveNext

    intRec = intRec + 1

  Loop

End Sub

Data in the cache will no longer be current when another user has edited a record stored in a cache buffer. The Requery method of the recordset is not the appropriate way to refresh the data in the cache, because it will clear the cache and reset bookmarks. Instead, issue these commands (using the example in the previous listing) to see the latest data:

rsql.CacheSize = 0

rsql.CacheSize = rlngCacheSize

rsql.FillCache

ODBCDirect does not allow you to control caching to the same degree as Jet. An ODBCDirect recordset does not support the CacheStart property and FillCache method, and its CacheSize property is inherited from the QueryDef on which it was based. For example, you will apply the CacheSize method to a QueryDef object, and the cache configuration propagates into recordsets created from the query:

Set qsql = cnn.CreateQueryDef("")

qsql.SQL = "SELECT * FROM roysched"

qsql.CacheSize = 20  ' Arbitrary number for example

Set rsql = qsql.OpenRecordset()  ' Recordset's cache size is also 20

By default, Jet cache's one record at a time, while ODBCDirect's default cache size is 100 records.

Squeezing Out the Last Drop of Performance

There are a variety of ways to increase performance when working with server data. For example, Jet will establish separate connections for retrieving keys and data when creating recordsets against SQL Server. However, if a recordset has 100 records or fewer, Jet can fetch both its keys and its data on a single connection. As a result, if you can tune your application to fetch data in small "bites" of under 100 records, it will run faster.

The next few topics provide additional tidbits on boosting the performance of your applications against a server. The collection of techniques is by no means comprehensive, because different servers have different personalities, and different applications have different performance logjams. However, I'll touch on the most important areas for optimization.

Retrieving the Smallest Possible Dataset

When retrieving records from a server, the conventional wisdom that you apply to native Jet SQL requests also applies: "Request the smallest possible number of rows and fields that will answer the stated question." Obviously, a request for a large dataset takes longer to process than one for a smaller set of data, due to these three factors:

The concept of requesting a small dataset may mean doing some experimentation. For example, a recordset operation may require data from a Text or Image field (let's call them "large fields") on an SQL Server. We've already discussed in this chapter that pulling the data from large fields into a recordset may be a poor performance choice. Instead, you could create a separate recordset pointing only to the large fields and fill the recordset on demand with only a few records as required, using the caching technique described previously in this chapter. You would want to try timing tests on this approach and then contrast its performance against the alternative approach of sending a new SQL request to the server for each large field and creating single-record result sets as required.

Selecting Specific Records

If you are creating a recordset from a server for the purpose of sequentially reviewing the returned data, DAO can move forward and backwards through the recordset (cursor) with ease. However, DAO cannot use the Seek method on a recordset created from ODBC data, and only a Jet-based recordset can use the Find methods. Thus if your intent is to jump around the recordset to specific records, you will be relying on either the Find methods and a Jet recordset, or creating a very small recordset and moving sequentially through it in search of specific records.

You have two options when trying to give Jet a fighting chance to perform well in such a scenario. The example I'll use to describe both of these options assumes that you want to locate the records for Carson, Greene, and Hunter within the authors SQL Server table.

While this table provides the most convenient example within Pubs, it does not contain enough records to be a good example against which to run timing tests. You will have to create a much larger table in order to fully evaluate the worth of these techniques.

The options to create the fastest Find methods are:

Set rsql = dbs.OpenRecordset("qsptSelAuthors")

rsql.FindFirst "au_lname = 'Carson'"

rsql.FindFirst "au_lname = 'Greene'"

rsql.FindFirst "au_lname = 'Hunter'"

Set qspt = dbs.CreateQueryDef("")

qspt.Connect = lci_gstrCurrentCnn

qspt.SQL = "SELECT * FROM authors WHERE" _

  & " au_id IN ('238-95-7766', '527-72-3246', '846-92-7186')"

qspt.ReturnsRecords = True

Set rsql = qspt.OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)

rsql.FindFirst "au_id = '238-95-7766'"

rsql.FindFirst "au_lname Like 'Car*'"

In the previous line of code, notice that the Jet asterisk wildcard (*) was used rather than the ANSI SQL wildcard (%), even though the recordset was created from a pass-through query. This is because the FindFirst operation is applied to the recordset at Jet's level, not at the server's, and thus must use Jet syntax. Jet will translate the * to a % before sending the query to the server so that the server can still evaluate the LIKE predicate.

The previous examples of FindFirst operations assumed that the recordset created from the server was a dynaset. If you are working with snapshots instead, very different logic applies. Because a snapshot is copied to your local drive, a FindFirst on a small snapshot will be quite speedy, even though it does not use indexes. Conversely, a find operation on a larger snapshot will not have the benefit of the server's indexes and will be slower than the dynaset examples I've just given.

The discussion in this section has used FindFirst in all examples, but is equally relevant to its siblings FindLast, FindNext, and FindPrevious.

Relocating Lookup Table Values

Lookup table values are used frequently within an application, so they are an immediate candidate for tuning when optimizing a client/server application. Master copies of lookup tables must be located on the server with the remainder of the data, for three reasons:

Despite these considerations, you will usually find that local lookup tables load combo and list boxes in forms faster than tables on the server. (For our purposes, a local table is one that is located in the application database or in another Jet database on the same machine as the application database and linked to it. A lookup table that is linked to its application database from a Jet back-end database on a file server is not considered local.)

Taking advantage of local lookup tables requires that you "clone" lookup tables from the server database to the client database, effectively creating your own (one-way) replication mechanism. See the section "Loading Lists from Local Tables" later in this chapter for a discussion of the issues involved in this strategy.

Wrapping Processes in Transactions

DAO is quite an intelligent tool when sending batch processes to a server. However, the way it defines a "batch" of work may not be as efficient or useful to your application as if you were to define the batch yourself. Thus, you will want to train DAO to manage server transaction commits and rollbacks for the maximum benefit of your application.

By default, each bulk query statement (DELETE, INSERT, and UPDATE) that you send to a server is wrapped by DAO in an ODBC transaction. Thus, DAO defines a single one of these statements as its default batch size for action queries. DAO will roll back the transaction if it detects from the server that the operation failed.

As opposed to action queries, Data Definition queries and other types of special statements sent to the server are not automatically wrapped in a transaction, and in many cases will not be wrapped in a transaction even if your code specifies one. This behavior varies by server. For example, SQL Server does not support a Data Definition statement inside a transaction.

When you use the DAO to perform recordset operations on a linked server table, Jet creates a larger batch than simply a single action-it places a transaction around all the operations for a given recordset. This is quite efficient and requires no work on your part. However, this implicit transaction cannot be rolled back.

Thus, the greatest degree of control of a data process is achieved when you manually build your own transaction, and place the events in it that you want to manage as a batch. When you create an explicit transaction on a Jet workspace, Jet uses your transaction model rather than its defaults (in as much as they don't openly conflict).

DAO translates your BeginTrans and CommitTrans method statements into the appropriate ODBC syntax and passes it to the server, so there is no need to include server-specific transaction statements in a pass-through query that is already nested in a DAO transaction. (In fact, this situation may actually prove dangerous by attempting to get the server to nest transactions when it cannot.)

In VBA, you can nest transactions inside each other when coding against the DAO. However, ODBC does not support nested transactions, so any levels of transactions other than the first one are ignored when DAO packages up the batch for ODBC. Thus, coding nested transactions in an ODBC client/server application is a waste of your time, as well as a small amount of VBA/Jet processing time.

As an example, assume that your system "checks out" or borrows records from the server into a local work table. The user edits these records, then runs a code process to replace the records on the server with the newly edited ones. Your code could create a transaction around the code containing all DELETE and INSERT statements that execute this plan.

The lines of code in Listing 19.14 show that a transaction targeted for the server is structured much like a standard transaction based on Jet tables. The code uses a simple Pubs example that mirrors the scenario of deleting and inserting a record. The example contains both Jet and ODBCDirect syntax.

Listing 19.14 AES_Srvr.Mdb-Wrapping workspace transactions around a
server process

' Jet example

Set wsp = DBEngine(0)

Set dbs = wsp(0)

 wsp.BeginTrans

  Set qspt = dbs.CreateQueryDef("")

  qspt.Connect = lci_gstrCurrentCnn

  qspt.ReturnsRecords = False

  qspt.SQL = "DELETE FROM discounts" _

    & " WHERE discounttype = 'Customer Discount'"

  qspt.Execute

  qspt.SQL = "INSERT INTO discounts" _

    & " VALUES ('customer discount', '8042', NULL, NULL, 5)"

  qspt.Execute dbFailOnError

wsp.CommitTrans

' ODBCDirect example

Set wsp = DBEngine.CreateWorkspace("", "", "", dbUseODBC)

Set cnn = wsp.OpenConnection("cnn", , , lci_gstrCurrentCnn)

wsp.BeginTrans

  Set qspt = cnn.CreateQueryDef("")

  qspt.SQL = "DELETE FROM discounts" _

    & " WHERE discounttype = 'Customer Discount'"

  qspt.Execute  ' Can't use dbFailOnError with ODBCDirect

  qspt.SQL = "INSERT INTO discounts" _

    & " VALUES ('customer discount', '8042', NULL, NULL, 5)"

  qspt.Execute

wsp.CommitTrans

Set the IsolateODBCTrans property on each individual Jet Workspace object if you are going to attempt to run parallel transactions against ODBC. By default, all workspaces share a single transaction space, so a CommitTrans method on one workspace may actually end a transaction on a different workspace. Setting the IsolateODBCTrans property to True tells Jet to keep a transaction buffer for each Workspace object.

You will want to be familiar with the record locking model of your server. Many servers lock records affected by a transaction and do not release the lock until the transaction ends. Thus one large transaction can effectively prevent other users from working with the affected data.

Jet does not commit an open transaction unless you explicitly tell it to do so. If there is no matching CommitTrans in your code to pair with an open BeginTrans, Jet initiates a roll back when the procedure ends. ODBCDirect transaction behavior is less predictable, because a transaction is initiated on and controlled by the server.

Determining What the Server Received

What happens at your birthday or Christmas after you make your gift list?

This series of events is similar to what happens when your application sends a data request to a server-what you get back, and when you will get it, may not be predictable. But worse, if you don't get what you asked for, you have no idea why.

On your birthday or at Christmas, you can solve this problem by going shopping with your loved ones, knowing that an involvement in the process brings a higher probability that you'll get what you requested. The same is true of working with servers. You can ask Jet to take you on its "data shopping" errands by asking it to expose to you the actual requests it makes to the server. This is called tracing.

Trace mode is enabled by setting values in the system Registry. These values are stored in the tree \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\ODBC. Two trace mode values can be placed in this key:

Figure 19.14 shows the Windows Registry structure where you will find or create these keys.

Fig. 19.14

If the ODBC section of the Jet tree in the Windows Registry does not already contain the two keys for ODBC tracing, you can add them yourself.

When tracing ODBC calls, you may find the process easier if your requests go to the server synchronously. To enable this, create or find the key called DisableAsync (of type DWORD) in the same ODBC folder of the Registry as shown previously. Set this key's value to 1 to disable asynchronous queries. Remember to set this key back to 0 when you are done tracing to get the best performance from future ODBC calls.

Let's send a simple request to the server and review the information in the log files. The SQL statement we'll send as a pass-through query from Jet is:

SELECT emp_id, fname, lname

FROM employee

WHERE job_lvl >200;

This SQL statement generates the following information in the SQLOUT.TXT log file:

SQLExecDirect:

  SELECT emp_id, fname, lname

  FROM employee

  WHERE job_lvl > 200;

Notice that the pass-through operation from Jet did exactly what was expected-it passed the SQL string unmodified directly to the ODBC driver. Jet sent the SQL request to the SQLExecDirect() ODBC function. This function passes the received string directly to the server with no parsing or pre-processing.

The abridged contents of the ODBCAPI.TXT trace file for the example SQL statement are shown in Listing 19.15, with my annotations.

I have removed trace details (memory handles and so forth) and duplicate statements that are not relevant to this discussion from the listing. I have also not dissected the individual attributes of each ODBC function in the listing, but instead have commented the file at a broader level. I've used apostrophe-marked comments and VBA-style line continuation markers to make the listing more readable; these characters are not in the actual log file.

Listing 19.15 Contents of the ODBCAPI.TXT trace file after running a simple
pass-through SELECT statement

' Establish a connection

SQLAllocEnv

  RETURN:   0 (SQL_SUCCESS)

SQLAllocConnect

  RETURN:   0 (SQL_SUCCESS)

SQLSetConnectOption

  RETURN:   0 (SQL_SUCCESS)

SQLDriverConnectW

  ARGUMENT: "DSN=Pubs;UID=sa;PWD=;DATABASE=pubs"

  ARGUMENT: <SQL_DRIVER_NOPROMPT>

  RETURN:   1 (SQL_SUCCESS_WITH_INFO)

' Check the attributes of the connection

SQLGetInfo

  ARGUMENT: <SQL_ODBC_API_CONFORMANCE>

  RETURN:   0 (SQL_SUCCESS)

SQLGetInfo

  ARGUMENT: 6 <SQL_DRIVER_NAME>

  RETURN:   [12] "SQLSRV32.DLL"

  ' The number in brackets above is the byte length of the return string

SQLGetInfo

  ARGUMENT: <SQL_TXN_CAPABLE>

  RETURN:   0 (SQL_SUCCESS)

SQLGetInfo

  ARGUMENT: <SQL_CURSOR_COMMIT_BEHAVIOR>

  RETURN:   0 (SQL_SUCCESS)

SQLGetInfo

  ARGUMENT: <SQL_CURSOR_ROLLBACK_BEHAVIOR>

  RETURN:   0 (SQL_SUCCESS)

SQLGetInfo

  ARGUMENT: <SQL_ACTIVE_STATEMENTS>

  RETURN:   0 (SQL_SUCCESS)

' Create a statement handle for passing statements

SQLSetConnectOption

  RETURN:   0 (SQL_SUCCESS)

SQLAllocStmt

  RETURN:   0 (SQL_SUCCESS)

  RETURN:   HSTMT = 0x030c2920

' Once a statement handle is returned (a pointer to a connection),

'   the pointer is passed by Jet to each ODBC request from here onward

'   as in the following argument

SQLGetStmtOption

  ARGUMENT: 0x030c2920

  RETURN:   0 (SQL_SUCCESS)

SQLSetStmtOption 

  RETURN:   0 (SQL_SUCCESS)

' Check for the configuration table

' See the section "Setting Jet Options on the Server" later in this chapter

SQLExecDirect

  ARGUMENT: "SELECT Config, nValue FROM MSysConf"

  RETURN:   -1 (SQL_ERROR)

SQLError

  RETURN:   0 (SQL_SUCCESS)  ' Informational-not fatal-error

  RETURN:   [78] "[Microsoft][ODBC SQL Server Driver][SQL Server] _

              Invalid object name 'MSysConf'."

SQLError

  RETURN:   100 (SQL_NO_DATA_FOUND)

' Drop the statement handle to the invalid table

SQLFreeStmt

  ARGUMENT: 0x030c2920

  ARGUMENT: <SQL_DROP>

  RETURN:   0 (SQL_SUCCESS)

' Create a new statement handle

SQLAllocStmt

  RETURN:   0 (SQL_SUCCESS)

  RETURN:   0x030c2920

  ' Note that ODBC reuses the previous handle for efficiency

SQLGetStmtOption

  RETURN:   0 (SQL_SUCCESS)

SQLSetStmtOption

  RETURN:   0 (SQL_SUCCESS)

SQLGetStmtOption

  RETURN:   0 (SQL_SUCCESS)

SQLSetStmtOption 

  RETURN:   0 (SQL_SUCCESS)

' Create a cursor to the desired table data

SQLExecDirect

  ARGUMENT: "SELECT emp_id, fname, lname FROM EmployeeWHERE job_lvl > 200;"

  RETURN:   0 (SQL_SUCCESS)

' Get information about the query result set

SQLNumResultCols

  RETURN:   0 (SQL_SUCCESS)

  RETURN:   3  ' Number of fields in result set

SQLGetInfo

  ARGUMENT: <SQL_DBMS_NAME>

  RETURN:   0 (SQL_SUCCESS)

  RETURN:   [20] "Microsoft SQL Server"

SQLDescribeCol

  ARGUMENT: 1  ' Column number

  RETURN:   0 (SQL_SUCCESS)

  RETURN:   [6] "emp_id"  ' Name of column 1

  ' The number in brackets above is the byte length of the return string

SQLDescribeCol

  ARGUMENT: 2

  RETURN:   0 (SQL_SUCCESS)

  RETURN:   [5] "fname"

SQLDescribeCol

  ARGUMENT: 3

  RETURN:   0 (SQL_SUCCESS)

  RETURN:   [5] "lname"

' Retrieve one record and its fields

SQLFetch

  RETURN:   0 (SQL_SUCCESS)

SQLGetData

  ARGUMENT: 1  ' Column number

  RETURN:   0 (SQL_SUCCESS)

  RETURN:   [9] "F-C16315M"  ' Row 1, column 1 data value

SQLGetData

  ARGUMENT: 2

  RETURN:   0 (SQL_SUCCESS)

  RETURN:   [9] "Francisco"

SQLGetData

  ARGUMENT: 3

  RETURN:   0 (SQL_SUCCESS)

  RETURN:   [5] "Chang"

' Retrieve the next record

SQLFetch

  RETURN:   0 (SQL_SUCCESS)

SQLGetData

  ARGUMENT: 1

  RETURN:   0 (SQL_SUCCESS)

  RETURN:   [9] "PTC11962M"  ' Row 2, column 1 data value

...

' The fetch/read process continues for all records

' The final fetch goes to the end of the cursor

SQLFetch

  RETURN:   100 (SQL_NO_DATA_FOUND)

' Release the statement handle and connection

SQLFreeStmt

  ARGUMENT: 0x030c2920

  ARGUMENT: 1 <SQL_DROP>

  RETURN:   0 (SQL_SUCCESS)

SQLDisconnect

  RETURN:   0 (SQL_SUCCESS)

SQLFreeConnect

  RETURN:   0 (SQL_SUCCESS)

The first few lines of the trace file contain the initial "handshaking" between Jet and the remote database. Jet first creates a connection, then a statement handle within the connection, which is a buffer for sending the SQL string to the server and for retrieving the records back from the server. Jet asks the buffer for information about what the server has sent (columns and rows), then the records are fetched individually as Jet needs them to populate its own record set.

Notice how much work Jet is doing for you "behind the curtain." Jet handles all the communication of your Access request to the ODBC driver, and all the retrieval of data through the driver from the server.

Let's look at a more complex example now, where Jet is sending a request to the server but is basing the request on a linked table tsqlJobs and not using pass-through. Here is the SQL statement:

SELECT job_id, job_desc, min_lvl, max_lvl

FROM tsqlJobs

WHERE tsqlJobs.min_lvl > 100;

For the purposes of this example, I executed the following steps:

The information in the SQLOUT.TXT proves insightful. Listing 19.16 shows a summary of the relevant SQL submissions logged in the trace file.

Listing 19.16 Contents of the SQLOUT.TXT trace file after running a query
on a linked table and editing the data

SQLExecDirect:

  SELECT "dbo"."jobs"."job_id"

  FROM "dbo"."jobs"

  WHERE ("min_lvl" > 100 ) 

SQLPrepare:

  SELECT "job_id","job_desc","min_lvl","max_lvl"

  FROM "dbo"."jobs"

  WHERE "job_id" = ? OR "job_id" = ? OR "job_id" = ? OR "job_id" = ?

     OR "job_id" = ? OR "job_id" = ? OR "job_id" = ? OR "job_id" = ?

     OR "job_id" = ? OR "job_id" = ?

SQLExecute:

  (MULTI-ROW FETCH)

SQLPrepare:

  SELECT "job_id","job_desc","min_lvl","max_lvl"

  FROM "dbo"."jobs"

  WHERE "job_id" = ?

SQLExecute:

  (GOTO BOOKMARK)

SQLExecDirect:

  UPDATE "dbo"."jobs"

  SET "max_lvl"=?

  WHERE "job_id" = ? AND "job_desc" = ? AND "min_lvl" = ?

    AND "max_lvl" = ?

Compare this listing to the one from the prior example and you will notice these significant differences:

Listing 19.17 summarizes the contents of the ODBC trace file for the current data request. I've included only the core statements from the trace file. All server processes include events to establish a connection, retrieve the connection attributes, create statement handles, and release resources. Each of these tasks was shown in Listing 19.16 so I have removed them from the following listing to keep it uncomplicated. I have also removed many of the duplicate statements from the trace file for brevity and included comments in their places.

Listing 19.17 Contents of the ODBCAPI.TXT trace file after running a query
on a linked table and editing the data

' Fetch the initial key values into a keyset

SQLExecDirect

  ARGUMENT: "SELECT "dbo"."jobs"."job_id"

             FROM "dbo"."jobs"

             WHERE ("min_lvl" > 100 )"

' Get a record from the cursor

SQLFetch

' Get the index field from the record

SQLGetData

...

' Fetch/GetData repeat six times, once per index record,

'   until all index values are in the cache

' Create a prepared statement for filling the buffer

SQLPrepare

ARGUMENT: "SELECT "job_id", "job_desc", "min_lvl", "max_lvl"

             FROM "dbo"."jobs"

             WHERE "job_id" = ? OR "job_id" = ? OR "job_id" = ? _

                OR "job_id" = ? OR "job_id" = ? OR "job_id" = ? _

                OR "job_id" = ? OR "job_id" = ? OR "job_id" = ? _

                OR "job_id" = ?"

' Replace the ? parameters in the prepared statement above

'   with the actual values for the SELECT

SQLBindParameter

  ARGUMENT: 1  ' Bind a job_id from the keyset to the first argument

SQLBindParameter

  ARGUMENT: 2  ' Bind a job_id from the keyset to the second argument

...

' BindParameter repeats ten times, once for each job_id key value in 

'   the keyset plus four dummies to fill out all ten

' Execute the prepared statement

SQLExecute

' Get a record from the cursor

SQLFetch

' Get the data values

SQLGetData

  ARGUMENT: 1  ' Retrieve first column

SQLGetData

  ARGUMENT: 2  ' Retrieve secon column 

SQLGetData

  ARGUMENT: 3

SQLGetData

  ARGUMENT: 4

...

' Fetch/GetData repeats six times, once for each record; this 

'   populates the datasheet

' At this point, I edit the first record in the datasheet

' Check the record for currency

' Before doing an UPDATE, Jet checks the record to see if has

'   has been edited by someone else or still matches the record

'   in the buffer 

SQLPrepare

  ARGUMENT: "SELECT "job_id","job_desc","min_lvl","max_lvl"

             FROM "dbo"."jobs"

             WHERE "job_id" = ?"

' Replace the ? parameter in the previous statement with the 

    key value of the edited record

SQLBindParameter

  ARGUMENT: 1 ' Bind job_id for edited record to the argument

' Retrieve the record

SQLExecute

' Get a record from the cursor

SQLFetch

' Get the data values

SQLGetData

  ARGUMENT: 1

...

' GetData repeats four times, once for each field

' Comparison complete, okay to UPDATE

' Replace the ? parameters in the upcoming statement with the 

'   appropriate values

SQLBindParameter

  ARGUMENT: 1  ' Bind first parameter to new max_lvl

SQLBindParameter

  ARGUMENT: 2  ' Bind second parameter to job_id

SQLBindParameter

  ARGUMENT: 3

SQLBindParameter

  ARGUMENT: 4

SQLBindParameter

  ARGUMENT: 5

' Send the edited record to the server

SQLExecDirect

  ARGUMENT: "UPDATE "dbo"."jobs"

             SET "max_lvl"=?

             WHERE "job_id" = ? AND "job_desc" = ?

               AND "min_lvl" = ? AND "max_lvl" = ?"

' See if one row was affected

SQLRowCount

' Commit the transaction

SQLTransact

Why is it important to understand this communication process from client to server and back? Because once you comprehend it, you can review how Jet communicates your more complex statements to the server and gain insight into how to optimize the process. Even if you don't understand the ODBC function statements themselves, you can see the fetches of data and the SQL statements in these trace files and gain a greater understanding of how your application requests records.

For example, if you send a complex join for a report to the server and the processing is slow, reading the trace files will help you gain insight as to whether or not Jet sent the complete query to the server, or instead fetched data in "bite-sized chunks" and processed the query locally.

After a long day of debugging, remember to delete your trace log files ODBCAPI.TXT and SQLOUT.TXT and to reset the tracing Registry keys before you go home. You'll forget about them by the next day and the log files will get large very quickly if tracing is left on unintentionally.

Using Access Forms Against a Server

In the past two years, we've created for one of our larger clients about a half-dozen Access applications that were each intended to be migrated to SQL Server within a year of birth. Consequently, we designed the applications with a client/server model in mind. In the end, the client's IT group decided that they were not ready to support our SQL Server databases yet, and these projects all stayed in Access. Because they were designed to move to a server, they are performing more slowly in Jet than we would like.

Mind you, we always try to do our best job on every project, but an application written to one model is not always optimized for another model. In the case of client/server applications, we usually "over-engineer" the system in several ways, expecting that the speed and capabilities of the server will compensate for the extra baggage. Here are four examples:

These engineering decisions often add code or complexity that de-optimizes the application when run under Jet, but enhance performance when working against a server.

Additionally, an application is usually selected as a candidate for a client/server model by virtue of a large amount of users or records. Leaving the data in Jet long after the record or user volume exceed a comfortable level is one sure way to run slowly.

The moral of this story is that it's always nice to have an application ready to migrate to a client/server model, but constructing an application for a database server and then running it indefinitely on a file-server is not a positive scenario.

When creating Access applications for use against a server, there are several general rules-of-thumb that can be applied to make the technology partnership run smoothly. The simple techniques include:

Following the naming conventions in Chapter 6, "Leszynski Naming Conventions for Access," and Appendix C of this book will ensure that your database structure is "server-compatible" when migrated.

In addition to these general guidelines, there are plenty of nuances when working with server data, many of which are detailed earlier in this chapter. The next few topics discuss additional concepts specific to the architecture of forms in Access client/server applications.

Providing Records to Forms

Access forms are the cornerstone of your application, and also the biggest performance bottleneck. In order to work with server data, some minor paradigm shifts must take place. You must weigh each of these considerations as you design your forms:

When tying your forms to server data, your options are different than those when working with Jet data. The most significant difference is the decision as to whether or not server tables will be linked to your database and forms built on the links. If you select this strategy, your architectural decisions are basically behind you and pulling your application together becomes quite straightforward.

If, on the other hand, you intend to take into account the various techniques discussed in this chapter, your decision-making and development processes become more complex. For example, you must consider all of the following options for providing data to forms. Each option has strong and weak points.

As mentioned earlier in this chapter, Jet relies on the server's locking methodology, not its own. Because most servers utilize optimistic locking, the setting for the RecordLocks property on your server-based forms should always be No Locks (the All Records setting is not allowed for remote data and Edited Record is ignored).

The various coding techniques required to create form record sources are covered earlier in this chapter.

Shifting the Data Entry Work Locally

Forms designed specifically for data entry do not necessarily need to point to server tables. Because an entry form does not fetch records, it can be tied to a local table that provides the appropriate validation, then an entire data entry batch can be moved from the local database to the server when complete.

Note the key word in the previous sentence: validation. Do not proceed with the following technique unless you have a good strategy in place for keeping your Access form and table validation in synch with the server's validation.

To enable such a local data entry strategy, follow these steps:

  1. In the interface (client) database, create tables that match the exact structure of the target tables on the server.
  2. Create validation rules in the tables that mirror the rules in the server.
  3. Design data entry forms that tie to the local work tables.
  4. Create any non-linear validation that exceeds Jet's capabilities for business rules as form code (in other words, reproduce rules in VBA code that are enforced as stored procedures or triggers on the server).

When the user makes a menu selection to create a new batch of records, your application can either:

At the end of the batch entry process, the user closes the primary entry form or clicks a button that signifies that it is time to "post" the batch to the server. Your application must save any unsaved batch records, complete any pending validation, and then bundle up the batch records into a package and send it to the server. The entire batch of records should be sent to the server inside a transaction that either succeeds or fails as a unit.

The discussion in the "Inserting Records on the Server" section earlier in this chapter will clarify the options you have for posting the local records to the server.

The gaping hole in this strategy as described is exposed by the jobs table in Pubs, the source of many examples in this chapter. The table has an auto-increment field (of SQL Server type Identity) which provides each job with a new number automatically. This presents your application with two problems if your forms or application routines send records to the table with INSERT statements:

The solution to these problems is actually quite complex. Essentially, you need to pre-assign server record numbers to records that you are creating locally, or you need to teach the server to handle your incoming batch and to process it for you. These approaches are described in the next two topics.

Pre-Assigning Record Numbers for Data Entry

Pre-assigning a record number from the server to the client is not always an appropriate approach, because to pre-assign a record ID number you must create a placeholder record on the server. Once the dummy record is created, it will show up in queries, reports, datasheets, and so on. Thus your bogus record essentially "pollutes" the database until plugged with the final values.

However, because pre-assigning is easier to manage then post-assigning, it is a worthwhile technique in the case of transaction records that feed to a transaction table. In other words, creating placeholder records is an acceptable strategy for server tables whose only purpose is to queue transactions for processing, and not to display records to users. During processing batches, any bogus records (unused placeholders) on the server are simply not carried into master data tables.

When creating placeholder records, you will need to create dummy field values that satisfy the validation requirements of the table on the server. In doing so, your data may look "live" when it really is not. To prevent the misinterpretation of a placeholder record, always include a "flag" field in your server record that says whether it is a placeholder or live data. Any server processes that run against the table must be taught to ignore the records currently flagged as placeholders.

As an example, assume that you have 100 order entry clerks taking orders in an Access application that is connected to server data. In order to minimize connections, lock contentions, and network traffic, you've architected the application to send a record or batch of records to the server via bulk inserts. This application would benefit from the ability to reserve one or more record numbers on the server, do local data entry, and then post the entry batch into the placeholders when complete. Overnight, the server should run routines that check the transaction tables for unused placeholder records (these indicate records that the user did not successfully post). It should also take records that were successfully posted and move them into production tables.

Reserving ID values is especially important in data entry situations where a "paper trail" is kept for audit purposes. In the current example, if the clerks were keying in orders from hard copy, having the record number in advance would allow them to write the table record number on the hardcopy when keying a record into the form.

Although the technique of pre-assigning record numbers before or during data entry can be used in a variety of ways, it works best under this combination of circumstances:

  1. A Single-record Batch. In this scenario, the user will be adding one record at a time, then the record will be sent to the server. Thus, the placeholder record does not exist on the server for very long, and you do not often confront the problems of creating or deleting multiple placeholders.
  2. No Dependencies. If the entered record is not going to be the parent to multiple child records, it is easy to make use of a single pre-assigned ID number. However, if the form will allow the entry of child records, and the child table has an Identity field on the server as well, you will not know how many record numbers to pre-assign for the children before data entry begins. You will have to pre-assign child record IDs as well, as each child is created. This unfortunately creates complex groups of placeholder records sitting on the server.

To pre-assign a record number, run a stored procedure on the server that creates a dummy record in the target table and returns to you the ID of the added record as a message. You can pre-assign the record number in one of two locations in the application flow:

If you pre-seed a work table with only a single record, you must make certain that the user cannot add additional records through the form. Set the form's AllowAdditions property to False.

Of course, once the user has completed entry of a record using the assigned ID value, your code must post the data to the server. Because you've already created a dummy record on the server, you will be using an UPDATE statement to send the user's values to the dummy record rather than appending a record with INSERT.

Remember that your form must allow the user to undo the record entry process by selecting a "Delete" or "Discard" option of some sort on the form. In this situation, you must delete the record in progress in the local work table, and you must also delete the placeholder record that was created on the server, or cleverly re-use the placeholder for the next record entered.

The following example illustrates these concepts using the Pubs database.

First, a stored procedure is created on the server to insert the placeholder record:

CREATE PROCEDURE prcNextJobID

AS

DECLARE @ID char(6)

INSERT INTO jobs (job_desc, min_lvl, max_lvl)

  VALUES ('Dummy', 10, 10)

SELECT @ID =  '>>' + CONVERT(char(6), @@identity)

PRINT @ID

There are three important features to note about this server procedure:

See the section "Checking Messages Returned by the Server" earlier in this chapter for information on how the DAO can be taught to handle returned server messages.

Next, create a simple pass-through query qsptAppNextJobID to call the stored procedure, with the single line:

prcNextJobID

Saving this call in a query allows you to use the stored procedure from code more easily than with the alternative method of creating a temporary QueryDef each time the procedure is to be run. However, a temporary QueryDef built on an ODBCDirect workspace my provide enough performance improvement over Jet to warrant bypassing the saved query.

Next, create a work table that mirrors the structure of jobs on the server. This table (tblJobs) has the same field names and validation rules as the server data, and will be used as the record source for an entry form. Finally, create a form based on the work table tblJobs and place code like that shown in Listing 19.18 in the form's module.

Listing 19.18 AES_Srvr.Mdb-Routines that create a
placeholder record on the server and assign the record ID to the form record

' This routine is initiated when the user begins typing a new record

 Private Sub Form_BeforeInsert(Cancel As Integer)

' Purpose: Assign a job number to the record

  Dim lngID As Long

  DoCmd.Hourglass True

  lngID = cbfNextJobID()  ' Assign the ID

  If lngID <> 0 Then

    Me!txtjob_id = lngID

  Else

    Cancel = True

  End If

  DoCmd.Hourglass False

End Sub

' This routine fetches the next ID number

Function cbfNextJobID() As Long

' Purpose: Add placeholder record to server table and return ID value

' Returns: Next ID number or 0 on failure

  Const cstrProc As String = "cbfNextJobID"

  On Error GoTo cbfNextJobID_Err

  Dim dbs  As Database

  Dim qspt As QueryDef

  Dim rsql As Recordset

  Dim str  As String

  Set dbs = CurrentDb

  ' Delete the messages table if it exists

  On Error Resume Next

  dbs.TableDefs.Delete CurrentUser & " - 00"

  On Error GoTo cbfNextJobID_Err

  ' Run the stored procedure to create the dummy record

  Set qspt = dbs.QueryDefs("qsptAppNextJobID")

  qspt.Execute dbFailOnError

  ' Get the ID number from the message table

  ' In your application, add code here to trap if the table doesn't exist

  '  or is empty

  Set rsql = dbs.OpenRecordset(CurrentUser & " - 00")

  rsql.MoveFirst

  str = Trim(Mid(rsql!ODBC, InStr(rsql!ODBC, ">>") + 2))

  ' In your application, add code here to make sure a Long was returned

  cbfNextJobID = CLng(str)

  Exit Function

cbfNextJobID_Err:

  Call lci_ErrMsgStd(Me.Name & "." & cstrProc, Err.Number, Err.Description, _

    , True)

  Exit Function

End Function

When the user begins keying a new record, the Form_BeforeInsert() event fires, a placeholder record is created on the server, and its ID number is displayed on the form. Figure 19.15 shows the results of this process.

Fig. 19.15

A form that is bound to a local entry table but whose record ID is assigned by creating a server placeholder record.

When the user has completed data entry for the form, he or she clicks the Post button on the form and the form's data is sent to the dummy record on the server. Fields in the placeholder record shown in the figure are modified with their new values by an UPDATE statement. The code for this process is shown in Listing 19.19. Notice that the code first saves the edits in the form to the local work table. This validates the record before any attempt is made to send it to the server. Validation could also be provided by form code and the work table would not be necessary. Alternately, each record could be saved to the work table after entry and all new records could be queued up and updated on the server by a bulk process.

Listing 19.19 AES_Srvr.Mdb-Updating a placeholder record on the server
with form values

Private Sub cmdPost_Click()

' Purpose: Send the edits to the server

'          This routine could alternately be coded against ODBCDirect

  Dim dbs  As Database

  Dim qspt As QueryDef

  Dim wsp  As Workspace

  RunCommand acCmdSaveRecord  ' Save the record to validate it

  Set wsp = DBEngine(0)

  Set dbs = wsp(0)

  wsp.IsolateODBCTrans = True  ' Prevent overlapping transactions

  wsp.BeginTrans

    Set qspt = dbs.CreateQueryDef("")

    qspt.Connect = lci_gstrCurrentCnn

    qspt.ReturnsRecords = False

    ' The example here sends one record to the server

    ' It can pull the record's values from either the form or its table

    ' In this example, the data is pulled from the form

    ' In the case of a form that allows multiple batch transactions,

    '   the batch records would be appended from the work table to

    '   the server as the form closes

    qspt.SQL = "UPDATE jobs SET job_desc = '" & Me!txtjob_desc _

      & "', min_lvl = " & Me!txtmin_lvl _

      & ", max_lvl = " & Me!txtmax_lvl & " WHERE job_id = " _

      & Me!txtjob_id

    qspt.Execute dbFailOnError

  wsp.CommitTrans

  RunCommand acCmdRecordsGoToNew  ' Ready for another one

End Sub

Assigning Record Numbers After Data Entry

Assigning record numbers after data entry is the best solution when the data entry process is complicated, involving multiple tables and record dependencies. The downside to this strategy is that the user does not see the final record ID value on the form while data entry is occurring, because the number is not yet assigned. On the positive side of the coin, the application can discard an abandoned entry process by simply clearing the local work tables. This does not encumber the server with the addition or deletion of placeholder records.

When you implement this scenario, you can send all of the records to the server in a batch, or send records individually. The former option is easier to code, because you need to assign less ID numbers in advance, and because it is easier to "plug" the ID values into your work tables when there is only a single record.

Here is a suggested approach toward implementing this process:

  1. Decide how many record numbers you need. You must determine how many ID numbers are required for each of the primary/foreign key combination fields in the entry tables. You will need to create dummy records for each table in your batch that is on the parent side of a join.

You do not need to assign record numbers for primary key values of tables that have no children in the entry batch. These records' ID numbers can be assigned by the server during record insertion, and do not need to be returned to your program because they do not propagate into other records.

  1. Create server placeholders. When you know how many records to create on the server and in what table(s), insert the dummy records to the server using a process that returns the list of ID numbers that were allotted during the insertion. (See the example of this concept in the previous topic.)
  2. Change the temporary record numbers to the assigned values. With cascading referential integrity enabled on your local work tables, you can change the temporary key values to their server-assigned values on the parent side, and the change will cascade into related child records. Using the list of ID values returned by the server, update the temporary ID field values in your work tables with the actual record numbers. Because your data entry tables should mimic their targets on the server, each table will have a temporary ID field that links it to its child table(s).

You cannot assign temporary record ID values in the work tables using Access' AutoNumber field type, because you will not be able to change this read-only ID number to reflect the actual number when assigned by the server.

The "easy" way to beat this dilemma is to assign temporary record keys by having the user or program code enter them into the form. For example, the user or a procedure can enter the first job record with an ID of 1, the second with 2, and so forth. This allows your work table to accrue temporary ID numbers and to link parent/child record pairs with no programmatic effort.

Your form code can mimic the functionality of the AutoNumber data type using VBA, so that each form's BeforeInsert procedure creates a new, "next available" number to assign to the record. One common approach to this is to grab the maximum record number from the work table (the DMax operator can do this) and increment it.

  1. Send the records onward. Once the records have been "fixed-up" with the proper ID values to match their server placeholders, you can forward the records to their placeholders on the server using UPDATE statements within a transaction.

If this technique sounds complicated, that's because it is. One of the benefits of using a server is to shift as much work as possible from the client side to the server side. The process described does not do so. I've detailed the process anyway in order to provide you with food for thought. However, the actual approach I prefer to use is to post records to the server through stored procedures. We accomplish two objectives with this arrangement:

I'll summarize here one flow that your application could employ to use stored procedures to perform batch insertions. This flow requires no placeholder records and thus can feed data directly to "live" tables:

  1. Send the local records to the server. Use INSERT statements to send the local work table records to holding tables on the server with the same structure. You will need to include a user or process ID in each record sent to server holding tables in order to identify the owner of the batch.

If you are a fan of my auditing strategies described in this book (see Chapter 17, "Bulletproofing Your Application Interface," for examples), you can create a unique batch number and use that number in the posted records instead of a user ID. The batch number can be assigned by calling a stored procedure that creates a new batch log table record and returns the number assigned to the record (this is similar to the technique described in the preceding section for creating placeholder ID values).

Once the related batch process is complete or has failed, you can update the batch table record to log information about the success or failure.

  1. Call the stored procedure. Invoke the stored procedure from your application, and pass to the procedure the unique user ID or batch ID that identifies the holding table records to be processed.
  2. Move records into production. As the stored procedure executes, it must work through the records you submitted to the holding tables one record at a time, by processing a parent record first and then all of its children. This enables the stored procedure to create an Identity field value for the parent record and then propagate that value into the child records in the holding table before posting them onward, thus maintaining referential integrity. Listing 19.20 provides a pseudo code outline for such an operation.

Listing 19.20 AES_Srvr.Mdb-Logic for a stored procedure-based batch
addition records to data tables

Start the transaction

Declare a cursor for the records in the parent holding table

Open the cursor

Fetch the first record in the cursor

Loop until the cursor is depleted

  Insert the parent record into the production table

  Capture the ID of the new parent record

  Update child records, placing the ID of the parent in their foreign keys

  Insert the child records into the production tables 

  Fetch the next record in the cursor

End loop 

Close the cursor

Delete all holding table records

Commit the transaction

Report the status to the calling program

  1. Check the status. You must have your server process return a success or failure message to your application so that you can inform the user of its outcome, and clear the local tables or retry the process based on that outcome.

While writing DAO code is quicker and less frustrating than writing server routines like this one, shifting the batch insert load to the server is the appropriate use of the client/server paradigm.

Filling Combo and List Boxes

Form combo boxes and list boxes may sometimes load more slowly from the server than from a Jet database, increasing the load time for their forms. There are several different techniques at your disposal for providing records to these controls. For each application, you should review and potentially benchmark each of the following options in order to determine the best approach for the specific application.

For purposes of the following topics, I'll refer to combo and list box controls in aggregate as "list controls."

Regardless of the techniques you employ to load list controls, these two rules apply whether your list data is on the client machine or the server:

Loading Lists From the Server

The key to successfully loading form list controls is to allow Jet or ODBC every opportunity to optimize the process. When you want to pull list items from server data, you can use any of these mechanisms as your list's row source:

Raw SQL statements as row sources used to skirt the positive benefits of Jet's pre-compilation applied to saved queries. As a result, you previously favored saved queries over SQL statements in the RowSource of list controls. Access now saves a compiled query for list control row sources, so the performance benefit of a saved query as a RowSource has diminished.

SELECT * FROM jobs IN "" [ODBC;DATABASE=Pubs;UID=sa;PWD=;DSN=Pubs];

When using program code to provide list values, you have two additional opportunities for fetching data: you can call the ODBC API directly from code, or you can use the new ODBCDirect techniques to fetch data using ODBC but skirting Jet.

Loading Lists From Local Tables

List controls are usually populated from lookup tables, which normally contain data that is fairly static. From a performance standpoint, your list controls will invariably load faster from a local table than a server table.

In order to populate lists from local data, you will need to "clone" the server tables onto the client workstation at regular intervals. Notice that I said "client workstation" and not necessarily "client database," because server lookup tables may be difficult to clone into the application database. The reason is that data must be "pulled" from the source databases by a client application's process, rather than being "pushed" to the client workstations by a central administrative process (I will clarify this concept in a moment).

The challenge provided by local lookup tables is to decide how often to update the values on the client workstation. You have these options:

Keeping lookup table values in a separate Jet database on the workstation, with the lookup tables linked to the client application, will allow the download process to overwrite lookup table values without destroying any saved personal objects (usually queries or reports) saved in the client application database.

As network cards and server machines continue to get faster, the need to clone lookup values to user workstations will diminish and large numbers of concurrent users should be able to pull lookup values directly from the server tables.

Protecting Server Data

The ease with which users can create table links in their local databases and then edit server data directly should cause you much concern. To prevent problems introduced by this feature, you will need to implement a security layer on your server data tables that disallows direct edits from Access.

How then can an application update tables that are no longer updatable? The simplest solution is to create different security levels for users and processes. The password that users login to the server with does not allow them to edit data. However, the password that does allow tables to be updated is passed to the server from code before running any process that alters server data. Access' security or Make MDE features can then be used to lock the modules that contain such code in order to prevent users from discovering the write password.

This scenario has two shortcomings:

I'll propose two other solutions to this security dilemma in the next two topics, in order to provide you with alternatives to explore.

Applying Security Via the Three-Tier Model

One solution to the data security problem brings into sharp focus the value of the three-tier architecture model as described in Chapter 7, "Understanding Application Architecture." Imagine creating a business object as an Access MDE file or Visual Basic Automation server whose job it is to post order records to the server. Your client application could perform data entry directly into tables managed by the business objects (if they are in a Jet database), or could set class properties in an Automation server application via VBA code.

In either case, the business objects would then take care of posting records to the server. The objects can have embedded password information that allows server updates but is compiled or otherwise secured from users.

Using common business objects allows for sharing of record operations between Access and non-Access users. In other words, a set of common business objects could also be used by Visual Basic and Excel client applications.

Applying Security with Stored Procedures

A second security model to use in client/server applications is to employ stored procedures to enforce a security layer. With stored procedures, all data operations are concentrated onto the server, where they can benefit from the server machine's higher performance. In addition, developers benefit from the centralization of all data functions in one place, where they can be updated together, backed-up together, replicated to another site together, and so forth.

For each data process (DELETE, INSERT, and UPDATE), you would create a stored procedure on the server to accept information from the client application and process it against the server. Set the stored procedure's rights to a level that allows it to update tables on the server. Only the procedure has these rights to base tables; user's linked to the database from Access or wandering in it without the Administrator password would not have rights to alter data, and would need to know the syntax of a stored procedure to do any actual damage to data.

Data can be passed in to the stored procedure as arguments when calling it, or can be written to a non-secured table on the server from which the stored procedure retrieves the records and moves them into production. Refer to the previous topic "Assigning Record Numbers After Data Entry", which provides a simple example of how stored procedures can be used to process data passed in from a client application.

Preserving Data Integrity

Another important issue in the protection of server data is preserving referential integrity (RI). Access provides such easy visual mechanisms for creating Jet table relationships and enforcing referential integrity that Access developers get just a little "spoiled". Most servers, however, do not provide such luxuries as graphical relationship builders. Some, in fact, do not even provide for stored relationships at all.

When you move your Access database to a server, you will need to establish referential integrity constraints there to ensure data integrity. In general, offloading this work to the server is the best approach from both a performance standpoint and a philosophical one.

SQL Server added some referential integrity capabilities starting with version 6.0. If you are using an older version you are still enforcing RI with triggers, in which case you may want to upgrade.

Your Access application should not have to enforce the RI of a remote database. In fact, it cannot do so because Jet cannot build referential integrity for linked ODBC tables. Nevertheless, any local batch data entry tables used for creating records that are posted to the server should enforce the same RI model as the server, in order to ensure a lower probability of posting failures.

When working against servers that do not have a robust RI model, client application code or stored procedures may be needed to "fake" referential integrity. For example, where cascading updates and deletes are not provided by the server, programmatic routines must be created to fill the void.

Setting Jet Options on the Server

When Jet establishes its first connection to a server database, it looks for a proprietary settings table called MSysConf that gives Jet/Access some connectivity behavior parameters. No Jet error occurs if the table is not found. You can create the table in your server databases using the following fields:

You set the flags that Jet uses to determine how to interact with the server by adding records to this table. Currently, Jet only recognizes three Config field values:

Jet does not perform background population by default. All background record fetching is done by Jet at the request of the client (the Access user interface).

From Here…

Creating client/server applications is a very complex task. In this chapter, I've provided discussions of the most important issues and techniques involved when using Access against remote data. I've done much more than scratch the surface here, yet there are many application-specific issues that will crop up in your development work that I have not dealt with. To solve them, use the code in this chapter and the associated sample database on the CD-ROM to create test cases in your application. Run and refine the test cases to achieve the best combination of data protection, usability, performance, and load-balancing between Access and its server partner.


© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.