Microsoft Jet can share multiple connections to an ODBC data source when the data source name and database are identical or when the data source names of the connections are identical and no connection uses a dedicated database (that is, none of the connections specifies a value for the DATABASE parameter in the connection string). For example, if you connect two applications to different tables in the same database, Microsoft Jet provides a single, dedicated connection to manage the bookmarks for each of the tables. A third, shared connection can then be used to retrieve records from, insert records into, and update records on either of the tables.
Microsoft Jet lets you execute multiple queries and allocates and terminates connections automatically. Depending on the ODBC database server you use, however, you may need to watch the number of queries that remain partially completed at a given time.
For example, an Oracle server can handle multiple, simultaneous partially completed queries. It does so by allocating additional resources at the database server layer. Microsoft SQL Server, however, uses a streamlined connection model that requires fewer resources on the database server. Consequently, each connection to Microsoft SQL Server can service only one query at a time.
If the database server used by your application supports multiple, simultaneous, partially completed queries on a single connection, your application effectively uses only one connection. If your application connects to a database server that services one query at a time, you should try to reduce the number of connections your application requires.