Posted by Erland Sommarskog on 10/25/05 01:28

Chris (chris.laycock@addept.co.uk) writes:
> I am experiencing strange behaviour with Processes that are created
> when I create a DAO Database Object with the following line:
>
> Set m_ResDatabase = DBEngine.Workspaces(0).OpenDatabase(strDSN, False,
> False, strODBC)
>
> This creates the process as expected.
>
> However the following lines don't always close the ensuing Process:
>
> If Not m_ResRecordSet Is Nothing Then
> m_ResRecordSet.Close
> Set m_ResRecordSet = Nothing
> End If
> If Not m_ResDatabase Is Nothing Then
> m_ResDatabase.Close
> Set m_ResDatabase = Nothing
> End If
> If Not m_ResWorkspace Is Nothing Then
> m_ResWorkspace.Close
> Set m_ResWorkspace = Nothing
> End If
>
> It seems as if SQL Server keeps hold of the first two Processes and
> then will release any subsequent ones.
> Can anyone shed any light in this - or any good web pages where I
> might find some answers?

Do DAO have connection pooling? Modern client libraries have connection
pooling, which means that when you close a connection from the code,
the API lingers on the connection for a minute, in case you would
reconnect directly. In such case, it's perfectly normal to see the
connections around.

Else, the only reason I can think of is that you had a transaction in
progress when you closed the connections, and the rollback takes a
long time. If you vie the processes with sp_who what state and active
command do they have?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]