When the results of a query on a remote data source haven’t been completely retrieved, the connection to the data source has an active statement on the connection. Depending on the type of data source or the behavior of the driver, you may not be able to execute another SQL statement on a connection until the active statement has completed. Furthermore, this type of connection is held open until the last record of the result set has been retrieved. In this case, Microsoft Jet may need to allocate additional connections to support other queries or operations (rather than discarding query results that have not been retrieved or forcing the application to complete the retrieval operation to close the statement).
To determine whether your ODBC data source or driver allows multiple, active statements on a single connection, Microsoft Jet requests the SQL_ACTIVE_STATEMENTS value from the ODBC driver with the SQLGetInfo API call. The value returned from the driver can represent a limitation in either the driver or the data source.
If the ODBC driver or data source is further limited by supporting only one statement per connection and only one connection per session (that is, a driver that doesn’t allow Microsoft Jet to open a second connection), Microsoft Jet forces linked tables to behave as snapshot-type Recordset objects by ignoring any unique indexes on the remote table. This allows Microsoft Jet to provide read-only data access to the remote data source, rather than denying access to the data source due to insufficient connection resources.