How Access Uses SQL Server Connections 

Summary:

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

This article assumes the reader is 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. Access, however, is not the 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 Access to obtain more connections
than the conventional front-end needs.

Although some servers, such as Microsoft SQL Server, are not too
stingy on giving out connections, there are 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 Access uses connections.

Connections are opened by 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 Access (static views and dynasets)
are built by executing a query (called a "local" query to
differentiate it from other types of queries discussed below). This
local query processes 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, 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 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 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 doesn't support it or because the
   semantics provided by the server differ greatly with those provided
   by 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 isn't executable on the server is somehow involved
   with the join between the two tables. Note that in 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 an
   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, Access opens only one connection per attached server,
   provided that the link attachment information is the same for each
   server tables.

As a result of the above rules, when an Access query is executed, it
typically requires only one (for a static view or two (for a dynaset)
connection. This doesn't represent an extravagant use of server
connections, but good applications could involve the execution of many
Access queries that could occupy many server connections very quickly.
There are some steps the designer can take to minimize the use of
connections:

 - 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 type 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.

With an understanding of how Access uses connections, and with prudent
application design, you should not run out of server connections.