ACC1x: How Microsoft Access 1.x Uses SQL Server Connections

• Microsoft Access versions 1.0, 1.1

SUMMARY

This article describes how Microsoft Access uses connections to SQL Server and how application developers can minimize the use of these connections.

This article assumes the you are highly knowledgeable about the low- level interaction between applications and SQL Server.

MORE INFORMATION

The conventional application that accesses a server does it in a simple single-tasking manner, requiring a single connection to the server. Microsoft Access, however, is not a typical conventional server front-end. Many of its features, including updatable views (dynasets), joins between local and server data, execution of complex expressions that the server may not be able to handle, and seamless transition from one server to another, require Microsoft Access to obtain more connections than a conventional front-end.

Although some servers, such as Microsoft SQL Server, are not stingy giving out connections, there are other servers that place strict limits on the number of connections an application can open. (In some installations, SQL Server also places absolute restrictions on connections.) When designing applications for the more restrictive servers, it is important to understand how Microsoft Access uses connections.

Connections are opened by Microsoft Access when it needs to execute a query on the server (which it must do to build dynasets or static views), obtain the data in a dynaset, or update data on the server. These connections can be categorized into two types: connections needed to build the working set and connections needed to service dynasets.

Both types of views available in Microsoft Access (static views and dynasets) are built by executing a query (called a "local" query to differentiate it from other types of queries discussed in this article). This local query processes Microsoft Access and foreign ISAM data (both locally and file server based) and remote server data, and pulls it into a temporary table that represents the view. To build static views, the actual data is stored in the temporary table, whereas to build dynasets, pointers to the actual data are stored.

When server data is included in the local query, execution of the query involves asking the server (or servers) for data, which is done by opening connections and sending queries to the server. Each query sent to the server requires that a connection be opened. If more than one query is to be sent to the server, a single connection could be used and the queries executed serially. However, this would typically require the user to wait for all of the server queries to complete before any data would appear. Therefore, Microsoft Access executes the server queries in parallel, requiring a connection for each.

The number of queries sent to a server may not be entirely obvious. Much of the time, access to all of the remote base tables will be combined into a single query, if all of the base tables reside on the same server. In the worst case, each server base table referenced by the local query will require a connection. There are several factors that could compel Microsoft Access to split a local query into several server queries:

• If server data is joined to data from other sources (that is, local, foreign ISAM, and other servers), the Microsoft Access optimizer may decide that it is more efficient not to combine references to two tables on the same server into a single query. For example, if the Customers and Order Items tables are on a server and the Orders table is in a local Microsoft Access database, then it is likely that the optimizer will decide to ask for the Customers and Order Items tables separately, rather than asking for the cross-product of the two, to join to the Orders table. As you can tell from this example, this is unlikely to happen in a real scenario.



• If an expression involved in the query cannot be executed on the server (either because the server does not support it or because the semantics provided by the server differ greatly from those provided by Microsoft Access), the expression will be executed locally. An extreme example of this is a local query with a restriction that calls a user-defined function. Although this may not require separating remote tables into separate server queries, it will if the expression that is not executable on the server is somehow involved with the join between the two tables. Note that in Microsoft Access, expressions containing a conjunction that can be executed on the server will be executed locally in their entirety. These expressions are not split to execute part locally and part on the server.



• The parameters used to attach tables on the same server to a Microsoft Access database differ. For example, if two tables are in different databases, or if they are being accessed using different user names and passwords, then separate connections will be required to execute a local query referencing both of them.
  
  Connections opened in the execution of a local query will be kept open until the working set containing the queries is closed.
  
  Connections are also opened in support of dynasets. But, unlike the server queries sent during the execution of a local query, the queries sent to the server in support of a dynaset are executed entirely and very quickly. This is because they are used to fill portions of the dynaset with data (given the pointers to the data provided by the local query), and to do updates to the server. Therefore, Microsoft Access opens only one connection per attached server, provided that the link attachment information is the same for each server tables.

• Reduce the number of queries required by the application. Each form, subform, data sheet, report, and list box requires a query, and each of these queries that accesses server data will require at least one or two connections. Close forms, data sheets, and reports as soon as they are no longer needed. Also, bring server data to a local database where practical, especially for filling list boxes.



• Avoid queries that join server data to local data to server data, as described above. These types of queries will most likely be executed using a connection for each server table.



• Avoid expressions that must be executed locally when joining server data together. Each of the tables involved with such a join expression, or that is restricted by a WHERE clause containing such an expression, will need to be executed locally. It should be noted that separating expressions into separate query objects will not help alleviate this problem, because the optimizer combines all query objects in a single query before execution for efficiency.



• Use static working sets when seeing other users' changes, or when making changes yourself is not required. This will avoid the connections needed to support dynasets.

Keywords : kbusage OdbcOthr
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbhowto