The three primary motives for adopting client/server architecture for database applications are:
Enterprise-wide access to corporate information: As computers become smaller, more powerful, and more usable, information is more widely distributed across the enterprise in an increasing number of formats. Client/server technology provides the links to access and manipulate data regardless of its location or storage format.
Upsizing multi-user file server database applications: Client/server applications provide improved performance, security, and reliability. Database application solutions inherently grow and become more complex over time. Developers need the assurance that they have a path to scale up as applications become larger and more complex and must support more users. Using the Northwind Traders database, you will be able to see some of the decision processing involved in upsizing an application.
Downsizing mainframe and minicomputer database applications: Most vertical application vendors are aggressively moving their applications to client/server to take advantage of Graphical User Interface (GUI) processing. Computer technology is quickly moving away from systems-driven users and toward user-driven systems. The power inherent in the event model is the basis for enabling this corporate re-engineering movement. Re-engineering is happening now because it can. Also motivating this change is the reduced cost of new back-end servers. Downsizing, however, is a more difficult and a much slower migration process. As such, the seamless integration from the mainframe world to the client/server world is a priority. Given this level of integration, client/server applications enhance and add value to existing mainframe-based process.
Microsoft Access uses several significant components to access and manage data. At the highest level is the Access user interface enabling the developer to create and manage tables, queries, forms, reports, macros, and code modules.
The next level is the database engine called Jet. The Jet engine is comprised of many modules including:
Data Access Objects: A new, high-level, object-oriented, data access language that is a complete Data Definition Language (DDL) and Data Manipulation Language (DML) for Access Basic.
Query manager: A sophisticated query processor that builds SQL statements based on a cost-based optimizer, manages query objects and their result sets, and enables heterogeneous updateable joins using a very flexible query-on-query model. The query manager also decides when to send queries to the Indexed Sequential Access Methods (ISAM) manager or the remote manager.
ISAM Manager: This manager provides access to various foreign ISAMs and has a native (local-to-Jet) ISAM called the Jet ISAM.
Remote Manager: The remote manager receives queries from the query manager and makes the appropriate Open Database Connectivity (ODBC) Application Programming Interface (API) calls to retrieve and pass data back to the query manager.
The final level is the ODBC software. This enables Microsoft Access to connect to many SQL-based data sources without the need to write a lot of server-specific SQL statements. This enhances portability and enterprise-wide connectivity.
The following two topics explain the enhancements found in Access version 2.0 that facilitate client/server development.
Data access objects can be thought of as an extension to the Access programming model. Data access objects provide a hierarchical structure for a complete, high-level DDL and DML language. All objects in the structure have properties and methods, so it is a more intuitive and robust programming model. You will find excellent documentation and on-line help to quickly explain how to use and manipulate these objects. There are also client/server specific commands and capabilities in data access objects that will be reviewed in the course of analyzing the sample applications. Figure 1 illustrates how data access objects are structured. Each object in the figure represents a layer of commands appropriate to that level.
Figure 1: Data Access Objects Hierarchy
The Jet Database Engine is the key component that enables robust data access and data management in Microsoft Access. The engine provides seamless connectivity to a myriad of data sources
Figure 2 illustrates the architectural components of the Jet engine outlined in the Microsoft Access Architecture section above.
Jet engine version 2.0 is used by Microsoft Access version 2.0. Jet has its own version numbering system since it is a separate module also used by Microsoft Visual Basic®. You will find in the Appendix a high-level overview of all the major enhancements made to the Jet engine. This list is segmented by new features when using Jet as a file server versus using Jet to connect to client/server databases.
In December 1994, an updated version of the Jet database engine became available from Microsoft -- Jet 2.5. Jet 2.5 has been modified to enhance database repair functionality and to correct the erroneous setting of an internal database flag that, in some cases, resulted in the message "Couldn't open SYSTEM.MDA." The Object Linking and Embedding (OLE) dynamic-link libraries (DLLs) have been modified to allow the use of OLE custom controls created by third-party vendors and to correct problems with diminished system resources when printing reports containing embedded Microsoft graph objects. The installable ISAM drivers have been modified to correct various minor problems.
The Service Pack is available as a self-extracting file called ACCSVC.EXE in the Microsoft Software Library (MSL).You can obtain the Service Pack by downloading the files from one of the services listed below.
CompuServe
1. GO MSL
2. Search for ACCSVC.EXE
3. Display results and download
Microsoft Download Service (MSDL)
1. Dial (425) 936-6735 to connect to MSDL
2. Download ACCSVC.EXE
Internet (anonymous FTP)
1. ftp ftp.microsoft.com
2. Change to the \SOFTLIB\MSLFILES directory
3. Get ACCSVC.EXE
The Microsoft ODBC API provides the ability to connect to a variety of client/server and mainframe databases. Connecting to a client/server or mainframe database requires a product-specific ODBC driver written to ODBC specifications. A wide variety of ODBC drivers are available from many vendors and will enable you to connect to your specific database. Additionally, an appropriate network library is necessary since there is no networking built into ODBC drivers. These libraries are provided by your ODBC vendor. Figure 3 illustrates the components involved in providing heterogeneous access to enterprise data.
Figure 3: Sample Open Database Connectivity Solution.
ODBC's goal is to provide seamless access to enterprise data using a single API. This capability is especially important for decision-support type applications where data may reside in many heterogeneous databases. It is also important in facilitating the upsizing of applications from a local file server to the client/server database of your choice.
When working in the very heterogeneous environment that ODBC enables, it is impossible, and even undesirable, to hide all server-specific functionality. Each relational database server has different capabilities and limitations. In addition, ODBC drivers can have different characteristics and capabilities since they are provided by many software vendors. The developer must make trade-offs between heavy use of server-specific features and the application portability to other servers.
Basic Design Principles
Basic design principles apply to file server or client/server development. The significance of the difference between them depends on your environment. Keep these principles in mind when experimenting with them to find out your response time. The primary design guidelines are:
Design forms so that the form opening process does not require the retrieval of data from the server . Add a command button to the form to fetch data to populate the form. In many cases, you may want to save the last set of form data in a local table, and then load the form from the saved data the first time the user opens the form.
Use Recordset objects of the Snapshot type if the result set contains relatively few columns and doesn't contain OLE object or large memo fields, and you don't need to update the server tables. Set the value of the AllowUdating property of the form to No Tables to create a Snapshot instead of a Dynaset. Pass-through queries always return Snapshot Recordset objects.
Minimize the number of items in server-populated pick list combo boxes. Use Snapshot Recordset objects to populate the combo boxes. It's important to remember that as database sizes increase, certain solutions may become unworkable. For instance, a combo box of 30 records is reasonable for a user to browse and pick from. Although the list of possibilities is in the hundreds, a list this large is unworkable.
If pick list data changes infrequently, maintain a local copy of the server table that populates the combo boxes. Include only the field(s) you need for the pick list in the table. Create an index on the local table to speed pick list population. Provide the user a simple means of replacing the local tables.
If decision-support application users need to compare multiple sets of data, consider storing the data returned by the server in temporary local tables. Provide a form in which the user can elect to use the previously stored data, or execute a new query. Adding the ability to quickly compare results of successive queries is especially important in applications that process financial information.
Background Population
When you open an attached ODBC table in datasheet view, Microsoft Access creates an updateable dynaset, if possible. Access fetches a chunk of rows to populate the visible datasheet grid and then continues to fetch the primary keys of the query result set into its keyset in the background; 100 rows every 10 seconds are the default values for fetched chunks. The chunks are stored in memory and when available memory space is exhausted, the chunks spill into a temporary (.TMP) file whose location is specified by the SET TEMP= environment variable in your AUTOEXEC.BAT file. Background chunking operations do not apply to Recordset objects created with Access Basic code.
Avoid operations resulting in substantial changes to the position of the record pointer, such as moving to the last record, with large tables attached by ODBC. Access and ODBC must process all the chunks between the prior and the new position of the record pointer to update the datasheet.
Table Updateability: Snapshots and Dynasets
Attached tables and queries against attached tables are updateable only if each table being updated has a unique index. Jet creates a Recordset object of the Dynaset type over the indexed table. Jet chooses an index to serve as the primary index (in effect, the primary key) by searching for clustered, hashed, and other indexes in sequence. If no clustered index exists, Jet picks the first unique non-clustered index in the alphabetical order of the index names.
Rather than transferring the data in each column of the row during the fetch , Jet builds a Dynaset by fetching the key values that uniquely identify each of the rows. The data from the bookmarked rows is fetched only when needed to populate the table or query datasheet view. Using key values speeds the chunking operation and minimizes network traffic especially when browsing large tables. Data retrieval is also optimized in that memo and OLE objects are not retrieved unless they need to be displayed.
When a table has no unique index, Jet creates a non-updateable Recordset object of the Snapshot type over the table. A Snapshot Recordset does not use bookmarks; instead, the data contained in each of the rows is fetched. Creating Snapshot Recordset objects can be faster with small tables, but can be quite time-consuming when large tables with many columns are involved, and network traffic increases drastically. This is especially true if the table or query includes memo fields and OLE objects.
You can minimize performance with Snapshot objects by ensuring you are returning only the columns you need. (Avoid using the TableName.* identifier.)
The Access version 2.0 Jet lets you create updateable Dynaset objects from views attached from the server data source. Ordinarily, a server view is not updateable because the view does not have its own unique index. If the table that underlies the view has a column or multiple columns that uniquely identify a row, however, you can create a local index specification on the attached view with Microsoft Access version 2.0's new DDL capability. The generic SQL to create a local index on a server view is:
CREATE UNIQUE INDEX IndexName ON tblTableName(ColumnName1, ColumnName2, ...)
The preceding statement does not create an index on the view or its underlying table(s); instead, it marks which column or columns uniquely identify each row of the view. You cannot use DDL to create local indexes on Recordset objects returned by SQL pass-through queries, nor can you attach result sets of server stored procedures. SQL pass-through queries are the subject of a following section.
Microsoft Access version 2.0 has the ability to execute pre-compiled stored procedures on the database server. There are several advantages to the use of stored procedures:
Stored procedures eliminate the overhead of the server's query planning (optimization) process. The optimized version of the stored procedure is immediately available to process the query.
Stored procedures are called by name. Jet doesn't need to send long, involved SQL statements through ODBC and over the network to the server. Sending a name, instead of an SQL statement, saves time and reduces network traffic.
Stored procedures can employ constructs that are not recognized by Jet or by ODBC's SQL grammar. As an example, Microsoft SQL Server™ Transact-SQL® provides for conditional execution and loops within Transact-SQL.
SQL pass-through is also required for processing server-specific SQL statements not translatable by the ODBC driver in use. SQL statements processed by Jet must conform to Jet SQL syntax. Jet translates Jet SQL syntax to ODBC SQL grammar when necessary. The non-standard SQL syntax of IBM's DB2™ database is discussed in a later section.
Microsoft Access Version 2.0's SQL Pass-Through Methods
You can elect to use Microsoft Access version 2.0's SQL pass-through option when you create a new QueryDef object in query design view. Choose the type of query represented by the stored procedure from the Query menu, then choose SQL Specific from the Query menu and choose Pass-Through from the submenu to display the SQL Pass-Through Query window. Type the name of the stored procedure and the value of its arguments, if any. Set the properties of the query (ReturnsRecords, ConnectStr, LogMessages, ODBCTimeout) in the Query Properties window.
The following SQL Server-stored procedure, qryTMAPL accepts two text parameters, territory (@terr) and account code (@acct), and returns rows that correspond to the parameter values.
CREATE PROCEDURE qryTMAPL @terr varchar(1), @acct varchar(2)
AS SELECT * FROM TMAPLBBB WHERE
C_TERR_C = @terr AND C_MAJACCT_C = @acct
Figure 4 illustrates the SQL statement and Query Properties entries to execute the qryTMAPL stored procedure with territory = A and account code = CC. Clicking the ellipsis button starts the Connect String Builder that translates the entries in ODBC.INI for the data source (SCMVSPRV), your user name, and (optionally) your password into a completed ODBC connect string.
Figure 4: Creating a Pass-through QueryDef Object.
The following Access Basic code creates a simple pass-through query calling SQL Server's SP_WHO stored procedure:
Function Callspt () As Integer
Callspt = False
Dim MyWorkspace As WorkSpace, MyDB As Database, Myquery As QueryDef, MySet As Recordset, temprecordset As Recordset
Set MyWorkspace = DBEngine.Workspaces(0)
Set MyDB = MyWorkspace.Databases(0)
Set Myquery = MyDB.CreateQueryDef("SP_WHO SQL Passthrough")
Dim SourceConnectStr As String
SourceConnectStr = "ODBC;DSN=dabusql;UID=sa;PWD=;DATABASE=nwind"
Myquery.Connect = SourceConnectStr
Myquery.ReturnsRecords = True
' Create the user property for logmessages if your sp returns messages
Dim MyProperty As Property
Set MyProperty = Myquery.CreateProperty("logmessages", db_boolean, False)
Myquery.Properties.Append MyProperty
Myquery.Properties("logmessages") = True
Myquery.SQL = "sp_who"
'Now create the snapshot
Set MySet = MyDB.OpenRecordset("SP_WHO SQL Passthrough")
'Print out the returned data:
Dim I As Integer, J As Integer
Debug.Print
' Enumerate all Recordset objects.
For J = 0 To MyDB.Recordsets.Count - 1
Set temprecordset = MyDB.Recordsets(J)
Debug.Print
Debug.Print "Enumeration of Recordset objects("; J; "): "; temprecordset.Name
Debug.Print
' Enumerate fields.
Debug.Print "Fields: Name, Type, Value"
For I = 0 To temprecordset.Fields.Count - 1
Debug.Print " "; temprecordset.Fields(I).Name
Debug.Print ", "; temprecordset.Fields(I).Type
Debug.Print ", "; temprecordset.Fields(I).Value
Next I
Next J
' Delete the query def from the collection
MyDB.querydefs.Delete ("SP_WHO SQL Passthrough")
Debug.Print ;
Callspt = True
End Function
When executed, a QueryDef object for pass-through queries returns a Recordset object of the Snapshot type in your current database while MSASP110.DLL had to return data to a table. You can use this Recordset as a data source for all normal processing. The preceding code creates a new table from the QueryDef object, duplicating the operation of MSASP110.DLL.
If you set the LogMessages property of an SQL pass-through query to True, Jet creates a table named UserID - ##, where UserID is your user ID (default = "admin") and ## is a sequential number from 0 to 99. A new table is created for each query executed. The informational and error messages in these tables is often useful in debugging problems with SQL pass-through queries.
The optimizing steps described in the following sections require changes to your Access Basic code, alterations of entries in sections of MSACC20.INI or APPNAME.INI, and/or creating additional stored procedures in your server database.
Adding a TimeStamp Field to Client/Server Tables
If your server supports the timestamp data type (a server-controlled version stamp), it is good practice to always add the timestamp field to all updateable server tables. When updating or deleting a record in a table with a version column, Jet checks the value of the version column to verify that the value hasn't changed since the record was initially accessed. If Jet is checking a table with the added timestamp field and the column values differ, then the update/delete would overwrite someone else's changes. If the table doesn't have a version column, Jet does a column-by-column comparison of all the old data column values with the present values, to determine if data has changed in the interim. This process is less efficient and generates more network traffic than only checking the version column. If the table has floating-point columns, Jet may think data has changed when it hasn't because of inherent inaccuracies in comparing floating-point values. Furthermore, when comparing all data column values, memo and OLE object columns are skipped; their size making the comparison prohibitively expensive.
As an example of how to add a version column to a table, the syntax on Microsoft SQL Server or SYBASE™ Server would be:
ALTER TABLE RemoteTable ADD VersionCol TIMESTAMP
You can run this DDL statement as a pass-through query in Microsoft Access. Remember to re-attach the remote table (or use the Attachment Manager Add-in), to refresh the table specification stored with the attachment data.
Microsoft and SYBASE SQL Server support version columns, but not all server support the concept of a version column. Check your server's documentation to see if you have this option.
Ensuring Transaction Integrity
When developing transaction processing applications, transaction integrity is a prime concern. Version 2.0 of the Jet engine supports transactions global to a single Workspace object. This means you can include Recordset objects based on more than one Database object within a single transaction, if all of the Database objects are opened within the same Workspace object.
You can now include multiple Set RecordsetName = dbName.CreateRecordset(Arguments) statements within the BeginTrans...CommitTrans...Rollback structure, even if the connection to the server needed to create the Recordset object is not open when the BeginTrans statement is encountered.
Applying transaction processing to bulk update operations with Microsoft Access version 2.0 is practical in almost any situation. It is not recommended to embed a BeginTrans statement within a SQL pass-through call, since the two statements would conflict. Even if your server handles nested transactions, you cannot eliminate this restriction.
Using SQL Pass-through and Stored Procedures for Bulk or Grouped Updates
When you use bulk transactions with attached tables, Jet engine creates a set of bookmarks (called a keyset) for the rows to be affected, then performs the operation for each record represented by a bookmark. The advantage of Jet's approach is that you can successfully execute partial bulk operations and types of bulk operations not supported directly by the server. In most cases, however, bulk operations are executed more quickly by SQL pass-through queries that are executed directly by the server.
You also can use stored procedures to execute bulk or grouped operations, within the limitation of the number of arguments you can pass to the server via the stored procedure. One of the advantages of using stored procedures for commonly used grouped update operations is increased execution speed.
Setting Multi-user/ODBC Options
You can set the ODBC Refresh Interval (1 to 3,600 s., default = 1,500 s.) to its maximum value if you aren't interested in displaying changes made by other users. Each of these option values can be read or set with the Application.GetOption(OptionName) and Application.SetOption OptionName, Setting, respectively. You may want to set these values with Access Basic code when you open the first form of the application, if the values set for users of prior versions of your application differ from that which you find optimal with attached server tables. It is recommended to set these values to their default values with Access Basic code when the user exits your application.
Using Forward-only Snapshot Recordsets
Snapshot type Recordset objects provide bi-directional scrolling capability by default. If you only need to make a single pass through the records of a Snapshot, you can Or the DB_FORWARDSCROLL flag with any other existing flag in OpenRecordset()'s intOptions argument. This makes the Snapshot more efficient because the result set is not copied to a scrollable buffer area.
Using Jet-to-ODBC SQL Tracing
You can examine the SQL statements for queries executed by the Jet engine against an ODBC data source by adding a TraceSQLMode=1 entry to the [ODBC] section of your MSACC20.INI or your run-time application's APPNAME.INI file. The SQL statements sent by Jet to the ODBC driver appear in a file called SQLOUT.TXT in your ACCESS directory or your application's current directory. The following are the first few entries in SQLOUT.TXT typically generated:
SQLExecDirect: SELECT Config, nValue FROM MSysConf SQLExecDirect: SELECT dbo.tblUsers.Contact_ID FROM dbo.tblUsers SQLExecDirect: SELECT Contact_ID,Initials,StartDate,EndDate,LoginName,Password,TimeSlotType,TimeSlotStart,FirstDay,UseAlarms,AlarmInterval FROM dbo.tblUsers SQLExecDirect: SELECT dbo.tblUsers.Contact_ID FROM dbo.tblUsers WHERE (LoginName = 'admin' ) SQLPrepare: SELECT Contact_ID,LoginName,Password FROM dbo.tblUsers WHERE Contact_ID = ? SQLExecute: (GOTO BOOKMARK) SQLExecDirect: SELECT Contact_ID ,Initials ,StartDate ,EndDate ,LoginName ,Password ,TimeSlotType ,TimeSlotStart ,FirstDay ,UseAlarms ,AlarmInterval FROM dbo.tblUsers WHERE (LoginName = 'admin' ) SQLExecDirect: SELECT FirstDay FROM dbo.tblUsers WHERE (Contact_ID = ? ) SQLExecDirect: SELECT Slot ,SlotType ,dbo.TimeSlots.SlotNumber FROM dbo.TimeSlots SQLPrepare: SELECT SlotNumber,Slot,SlotType FROM dbo.TimeSlots WHERE SlotNumber = ? OR SlotNumber = ? OR SlotNumber = ? OR SlotNumber = ? OR SlotNumber = ? OR SlotNumber = ? OR SlotNumber = ? OR SlotNumber = ? OR SlotNumber = ? OR SlotNumber = ? SQLExecute: (MULTI-ROW FETCH)
When you create a connection to the database the first query executed by Jet is the query to determine if the MSysConf exists and, if it exists, return the Config and nValue data for each of the rows in the table. This is used to determine if the password should be stored for the attached tables and what the background population parameters are. Each time your application executes a query, records are appended to SQLOUT.TXT, so you'll want to periodically erase the SQLOUT.TXT file or disable writing to it.
Caching Dynaset Records
To the extent you can keep heavily used records stored at the workstation, you significantly minimize network traffic and speed up local processing. One method mentioned earlier was to keep local tables that are downloaded regularly. For data that is too dynamic for that solution, you can now perform local cache management to ensure records are kept until you are done with them.
Microsoft Access version 2.0's CacheSize and CacheStart properties let you store all or a part of the data contained in Recordset object of the Dynaset type in local memory. Local caching of rows speeds up operations when traversing dynaset rows bi-directionally. Specify the number of rows, stored by the value of CacheSize (Long), and the beginning row by the bookmark, stored as the value of the CacheStart (String) property. Applying the FillCache method fills the cache with server data. Fetches within the cache boundary occur locally, speeding display of the cached records in a datasheet or in a continuous form. The allowable range of the value of CacheSize is between 5 and 1,200 records. If the cache size exceeds available memory, the excess records spill into a temporary disk file. Typically, you set the value of CacheSize to 100. To recover the cache memory, set CacheSize = 0.
Jet Query Optimization and Partitioning
Jet processes as much of a query as possible on the server for two reasons: (1) Filtering data on the server returns the minimum number of rows, thereby minimizes network traffic and delays; and, (2) Under most conditions, processing queries on the server is faster than processing a query on the client. Conventional SELECT and action (append, update, and delete) queries can be processed entirely by the database server. Exceptions are:
Heterogeneous joins of data from multiple data sources. When joining across two server data sources, the join must be performed locally. Jet optimizes joins between small local tables and large server based tables. In this case, if the remote table is indexed on the Join field, rather than attempting to bring the large server-based table for local processing, Jet creates and sends a separate query for each row in the local table.
SQL syntax that is not supported by the server database. Examples are crosstab queries or Microsoft Access SQL's TRANSFORM, PIVOT, TOP n, and TOP nn PERCENT reserved words. No server database supports special Access Basic and user-defined functions. The functions that are supported by ODBC, and a more detailed discussion of operations that are not supported by most database servers, appear in the Jet Database Engine ODBC Connectivity White Paper, by Neil Black and Stephen Hecht, which will be distributed to section attendees. You can also download the Jet Database Engine ODBC Connectivity White Paper from the ODBC Connectivity section of the MSACCESS forum on CompuServe as JETWP.ZIP.
SQL statements cannot be expressed as a single ANSI SQL statement. An example is a GROUP BY expression that includes another GROUP BY clause or a SELECT DISTINCT expression. Other more remote examples are given in the Jet Database Engine ODBC Connectivity White Paper.
Under the preceding conditions, Jet attempts to partition the query into components executable on the server, and those that must be executed locally. An example of "smart partitioning" is Jet's execution of crosstab queries. If you use a conventional crosstab query, Jet sends a SELECT query with a GROUP BY clause to the server, then transforms the result set to a crosstab query locally. Jet cannot optimize processing the query remotely if any one of the following conditions occur in the crosstab query:
Row headers contain aggregate functions.
Values have more than one aggregation.
A user-defined ORDER BY clause exists.
Locking and Concurrency Considerations
When you attach server tables, the server is responsible for all page- or record-level locking operations. You cannot open a Dynaset object against an attached server table in an exclusive mode. The value of the Record Locks property of all forms must be set to No Locks; All Records is illegal. (Edited Record is treated the same as No Locks.) With server tables, Microsoft Access uses internal optimistic locking; the row is not locked except while the update process occurs, when the edited value is committed. This usually is a very brief interval.
Handling Server-Generated Errors
A typical Error$ value returned by an error passed from the server through ODBC to your Microsoft Access application is shown as the message in Figure 5's message box. You can intercept the error with conventional Access Basic error trapping techniques and use string manipulation methods to alter the message to suit your application's style.
Fig. 5: A Typical Error Message Returned From the Server.
Errors with Err values in the range of -7700 to -7999 result from failure of the ODBC driver to comply with the ODBC specification. A list of these errors is provided in the Jet Database Engine ODBC Connectivity White Paper for Access version 2.0.
If you encounter a error with a Err value in this range, you need to contact the supplier of the ODBC driver to determine the cause of the error and/or obtain a fully compliant driver.
Retrying Failed Client/Server Update Transactions
Update operations may fail after the number of retries you specify as the value of the NumberOfUpdateRetries property. Depending on the nature of your application, you may want to offer the user the opportunity to again attempt the update, abandon the update, or store the data locally for later update. To provide this option, you place the entire transaction in a loop structure, such as:
For intCtr = 1 To 10
On Error Resume Next
Err = 0
BeginTrans
...your transaction code
If Err Then 'transaction failed
Rollback
If MsgBox(strMessage, 36, "Update Failed") <> 6 Then
Exit For
End If
Else
CommitTrans
Exit For
End If
Next intCtr
On Error ...
The preceding code is applicable to transactions executed against attached tables in .MDB files. However, when using transaction processing there are two differences between client/server and attached Microsoft Access tables:
Do not nest transactions sent to client/server databases via ODBC. Unlike Microsoft Access databases, most client/server RDBMSs do not support nested transactions.
Do not include native server-specific transaction processing commands in SQL pass-through queries. The server's transaction processing methods may conflict with those of Jet.
Using Batch Updating Transactions
Many accounting applications use batched transactions that append records from local tables to update journal and ledger tables. Such applications include batch integrity testing within the application prior to performing the server table update. Another opportunity to use batch updating transactions occurs when the server is down and you want to maintain data entry productivity. Here again, batch operations with client/server RDBMSs do not differ from batch operations against tables attached from .MDB files.
Sub PostRecords_Click ()
Dim db As Database
Set db = CurrentDB() On Error GoTo TransferFailed
BeginTrans
db.Execute "INSERT INTO RmtOrdersEmpty
SELECT * FROM LclOrders", DB_FAILONERROR
db.Execute "INSERT INTO RmtOrderDetailsEmpty
SELECT * FROM LclOrderDetails",
DB_FAILONERROR
db.Execute ("DELETE FROM LclOrders")
db.Execute ("DELETE FROM LclOrderDetails")
CommitTrans
Requery
Exit Sub TransferFailed:
MsgBox Error$
Rollback
Exit Sub
End Sub
RmtOrdersEmpty and RmtOrderDetailsEmpty are the following queries:
SELECT * FROM RmtOrders WHERE False SELECT * FROM RmtOrderDetails WHERE False
These "empty" queries on attached remote tables provide the fastest possible speed for the append queries above; similar to opening a recordset with the DB_APPENDONLY option. The Requery method of the form is used after clearing out the local tables to present the user with a clean slate for the next entry. The DB_FAILONERROR option is used on the two append queries to force an error to occur if any part of either append fails. The error handler then rolls back any changes made so far.
Connection management is a key issue to understand. When configuring your database server, you will need to estimate how many simultaneous connections the server will need to be able to support.
Cursor Commit and Active Statements Behavior
Depending on your database server's cursor commit and active statements behavior, Jet requires one or more connections to the database.
If your server's cursor commit behavior is too close any active statements on a single connection after an insert, update, or delete on that connection, then Jet must use multiple connections to maintain the integrity of any pending statements.
If the server database does not allow multiple active statements on a single connection, the number of concurrent connections to the server database depends on the number of simultaneous active statements created by your application. An active statement is a query whose complete result set (either bookmarks or rows of data) has not yet been returned. Jet opens an additional connection for each active statement.
The most limiting of these two behaviors determines Jet's connection strategy.
Dynaset and Snapshot Connection Behavior
If, for example, you update a row in datasheet view while a background fetch of Dynaset keys is occurring, you need two simultaneous connections to a server that does not allow simultaneous pending active statements: one for fetching data and doing server updates, and the other for returning indexes into the Dynaset keyset.
Jet version 2.0 optimizes Dynaset objects created from queries that return less than 100 rows by requiring only a single connection under all circumstances.
Snapshots do not build allow updates, so they only require a single connection.
The following sections briefly describe the complex subject of active connections between a Microsoft Access application and the server database. For further details, see the Jet Database Engine ODBC Connectivity White Paper.
Connection Sharing
Jet shares connections whenever possible. Jet will share connections in which the DSN value and the DATABASE value are identical. (Omission of the DATABASE value in the connect strings qualifies as identity.) A chunk-fetching operation on two tables, each with its own active connection, can be conducted over a third, shared connection. One connection is devoted to managing the keys (bookmarks) for each of the two simultaneously active tables, and the third connection is used to retrieve or send row data corresponding to the active fetch or update operation.
A server database that permits only a single connection from a client and does not support multiple statements on the connection can return rows only to non-updateable Snapshot Recordset objects, unless the set has less than 100 records.
Connection Caching
Jet maintains open, inactive connections to the server database to avoid the time required to re-create the connection. This process is called connection caching. (You can demonstrate the effect of connection caching by closing a datasheet view of an attached table, then re-opening the table. Re-opening the table is considerably quicker.) Jet caches one connection with ORACLE™ databases and two or more connections with SQL Server; this is dependent on the number of multiple active statements allowed. After 600 seconds (default) of inactivity, Jet closes the cached connection automatically. Reconnecting after closing a connection is transparent, except for the additional time required to re-create the connection.
Entries in the [ODBC] section of MSACC20.INI (Access version 2.0's equivalent of MSACCESS.INI) establish the values of parameters used by the Jet engine to connect to ODBC data sources. The table below is from the Jet Database Engine ODBC Connectivity White Paper; I it lists the entries that are valid for Jet database engine version 2.0.
Table 1. Valid Entries in the [ODBC] Section of MSACC20.INI.
Entry |
Value |
Effect |
TraceSQLMode |
0 |
No Jet-level SQL tracing (default) |
1 |
Trace the SQL statements Jet sends to ODBC into the file, SQLOUT.TXT |
|
SQLTraceMode |
0/1 |
Same as TraceSQLMode (for 1.x compatibility) |
TraceODBCAPI |
0 |
No ODBC API tracing (default) |
1 |
Trace ODBC API calls into the file, ODBCAPI.TXT |
|
DisableAsync |
0 |
Use asynchronous query execution if possible (default) |
1 |
Force synchronous query execution |
|
LoginTimeout |
s |
Cancel login attempts that don't finish in s seconds (default: 20) |
QueryTimeout |
s |
Cancel queries that don't finish in s seconds (default: 60) |
ConnectionTimeout |
s |
Close cached connections after s seconds idle time (default: 600) |
AsyncRetryInterval |
m |
Ask server "Is query done?" every m milliseconds (default: 500) |
AttachCaseSensitive |
0 |
Attach to first table matching specified name, regardless of case (default) |
1 |
Attach only to table exactly matching specified name |
|
SnapshotOnly |
0 |
Call SQLStatistics at attach time to allow dynasets (default) |
1 |
Doesn't call SQLStatistics, forces snapshots |
|
AttachableObjects |
string |
List of server object types allow to be attached (default: 'TABLE','VIEW','SYSTEM TABLE', 'ALIAS','SYNONYM' ) |
TryJetAuth |
1 |
Try Jet user ID and password before prompting (default) |
0 |
Doesn't try Jet user ID and password before prompting |
|
PreparedInsert |
0 |
Use custom INSERT that only inserts non-NULL values (default) |
1 |
Use prepared INSERT that inserts all columns |
|
PreparedUpdate |
0 |
Use custom UPDATE that only SETs columns that have changed (default) |
1 |
Use prepared UDPATE that updates all columns |
TraceSQLMode=1 maintains a perpetual log of the SQL statements that Jet has sent to the server in SQLOUT.TXT located in your application's current directory. This log is useful for debugging purposes, as well as determining how Jet partitions queries so the server executes the bulk of the query; thus returning the minimum number of rows. Jet's query optimization and partitioning is the subject of a following section .
TraceODBCAPI=1 maintains a perpetual log of the ODBC API calls executed by Jet in ODBCAPI.TXT. This log can become exceedingly large. If you use either of the Trace... options, make sure to delete the .TXT files periodically. These files are recreated on the next entry after you delete them.
If your Microsoft Access application is secure, requiring a user ID and password to log in, Jet attempts to open a connection to the server database with these login values when TryJetAuth=1 (default). This feature eliminates the necessity of a dual login process with attached server tables, and is a convenience for your application's users. If ODBC login fails, the SQL Server Login dialog box appears.
PreparedInsert=0 and PreparedUpdate=0 (the default values) improves the speed of updating server tables by altering only those columns of the record(s) whose values are modified by the update. This gain is primarily realized by lower network traffic and by not firing server-based triggers when data for that column has not changed.
When you first open a connection to a server database, Jet looks for a Jet-specific configuration table called MSYSCONF whose structure is shown below. This table is significant because it is managed by the database administrator. Options set in this table will always apply to all attached Microsoft Access version 2.0 applications.
If Jet finds this table, it reads the Config and nValue columns of the table to obtain information about the database. Adding this table to a server database is optional; if Jet can't find the table, the default values shown are used.
The Structure of the Optional MSysConf Table.
Column Name |
Datatype |
Description |
Config |
SMALLINT |
The number of the configuration option |
chValue |
VARCHAR(255) |
The text value of the configuration option |
nValue |
INTEGER |
The integer value of the configuration option |
Comment |
VARCHAR(255) |
A description of the configuration option |
If the table exists and errors are encountered, Jet closes the connection and returns an error message. The table following lists the options defined in Microsoft Access version 2.0. The chValue and Comment fields currently are unused.
Allowable values for the Config and nValue fields of the MSysConf table.
Config |
nValue |
Meaning |
101 |
0 |
Don't allow storing userID and password in attachments |
101 |
1 |
Allow storing userID and password in attachments (the default) |
102 |
D |
Access delays D seconds between each background chunk fetch (default=10) |
103 |
N |
Access fetches N rows on each background chunk fetch (default=100) |
Setting the value of nValue column of the Config = 101 row to 0 results in disabling the "Save login ID and password locally" check box when you attach a server table. The first time the application uses an attached table, the user is prompted to enter his or her user ID and password. The purpose of disabling the check box is to prevent unauthorized users gaining access to data by using a Microsoft Access application on another person's computer. Only the database administrator(s) should be allowed access to MSysConf.
Manipulation of the record pointer of attached tables in datasheet view often results in bursts of network traffic; a move to the last record of a large Recordset object of the Dynaset requires that all of the keys between the original and the new position of the record pointer be sent to the workstation. Then the data to populate the datasheet is retrieved from the server. Key values are relatively small when compared to the size of the data in a row. As a result, performance is greatly improved in comparison with a similar operation with a Snapshot object (where all intervening data must be fetched) on a large table.
Microsoft Access version 2.0 provides a background fetching capability that can return additional rows from the current position of the record pointer, and forward the keyset, while the record pointer is inactive. The value in the nValue column of the Config = 102 row controls the time between successive background fetches of the number of rows specified by the nValue of the Config = 103 row. Setting the fetch delay to a high value reduces network traffic, but slows moving to the last record in a datasheet, and vice-versa. Background fetching does not occur with Recordset objects you create with Access Basic code.