SQL Server connections not closing when Access connection also open 
Author Message
 SQL Server connections not closing when Access connection also open

I am currently experiencing a very strange bit of behaviour from ADO (MDAC
2.6, JET 4.0 SP 5) which I have been able to find any explanation for. I
would be very grateful if someone could have a look at the following and see
if they can make sense of it.

I have a VB6 application that uses an Access database to store various
configuration settings. One of the features of this application is that it
is able to communicate with SQL Server 2000 in order to update data in SQL
Server databases.

When my application starts it opens the connection to its Access database
and this remains open throughout the lifetime of the application. The
problem I have been having is that whilst this connection is open,
connections to my SQL Server database are not released when I close them.

The sequence of events I am experiencing is as follows:

1. Create an ADO Connection object and open it to the Access database.
2. Create a second ADO Connection object and open it to the SQL Server
database.
3. Open a recordset in the SQL Server connection and read out some data.
4. Close the recordset and set it to Nothing.
5. Close the SQL Server connection and set it to nothing.
6. Close the Access connection and set it to nothing.

When I execute these steps, I would expect my actual connection to SQL
Server to be removed after step 5. By using both "sp_who" in Query Analyzer
and also the Profiler tool it is quite clear that the connection does not
close until step 6 is executed. If I repeat the steps without executing step
1 (i.e., don't open the Access database connection), the SQL Server
connection does indeed disconnect immediately after step 5.

This is causing me a real headache, as this unwanted SQL Server connection
is blocking my subsequent database updates.

I have also found that the SQL Server connection will close on its own
eventually (after about 30 seconds) if I leave the Access database
connection open. This is no good, though, I need it to close immediately
rather than at some indeterminate time in the future.

The actual VB code I'm using is as follows:

Private Sub Command1_Click()

    Dim accessConn As ADODB.Connection
    Dim accessRs As ADODB.Recordset
    Dim sqlConn As ADODB.Connection
    Dim sqlRs As ADODB.Recordset

    'Connect to the Access database
    Set accessConn = New ADODB.Connection
    accessConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=MyDatabase.mdb;"

    'Connect to the SQL Server database
    Set sqlConn = New ADODB.Connection
    sqlConn.Open "Provider=SQLOLEDB;Server=SERVER;" & _
                "Trusted_Connection=yes;" & _
                "Initial Catalog=MyDatabase"

    'Read out some data
    Set sqlRs = New Recordset
    sqlRs.Open "select * from MyTable", _
                sqlConn, adOpenForwardOnly, adLockReadOnly

    Do Until sqlRs.EOF
        sqlRs.MoveNext
    Loop

    sqlRs.Close
    Set sqlRs = Nothing

    'Close the SQL Server connection
    sqlConn.Close
    Set sqlConn = Nothing
    '^^ I expect the connection to be released at this point

    'Close the Access connection
    accessConn.Close
    Set accessConn = Nothing
    '^^ Connection is actually released at this point

End Sub

I would be very grateful for any light that anyone can shed on this as it is
driving my crazy at the moment!

My thanks in advance,

--

Adam.



Sat, 05 Feb 2005 22:06:36 GMT  
 SQL Server connections not closing when Access connection also open
IS there a good reason for keeping the Access connection open all of the time? Conventional wisdom says to open the connection, get the
recordset, disconnect the recordset from teh connection then close the connection. WHen you need to update data back to teh database,
reopen the connection then reconnect and update. Then you should not have that problem, though I have never seen that behavior before.
(For my own part, I can't ever remeber creating a program using Access and SQL server at the same time.. If you have a small piece of code
that you can throw together (MAybe using the Nwind.mdb and the SQL Northind or Pubs database) that demonstrates the problem I would like
to see it. IF it is as you say this is something that needs to be looked into...

Scot Rose, MCSD
Microsoft Visual Basic Developer Support

Want to know more? Check out the MSDN at msdn.microsoft.com or the Microsoft Knowledge Base at support.microsoft.com

This posting is provided AS IS, with no warranties, and confers no rights.

--------------------

Quote:

>Subject: SQL Server connections not closing when Access connection also open
>Date: Tue, 20 Aug 2002 15:06:36 +0100
>Lines: 97
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300

>Newsgroups: microsoft.public.data.ado,microsoft.public.vb.database.ado
>NNTP-Posting-Host: 213.121.105.251
>Path: cpmsftngxa10!tkmsftngp01!tkmsftngp08
>Xref: cpmsftngxa10 microsoft.public.vb.database.ado:75004 microsoft.public.data.ado:50476
>X-Tomcat-NG: microsoft.public.vb.database.ado

>I am currently experiencing a very strange bit of behaviour from ADO (MDAC
>2.6, JET 4.0 SP 5) which I have been able to find any explanation for. I
>would be very grateful if someone could have a look at the following and see
>if they can make sense of it.

>I have a VB6 application that uses an Access database to store various
>configuration settings. One of the features of this application is that it
>is able to communicate with SQL Server 2000 in order to update data in SQL
>Server databases.

>When my application starts it opens the connection to its Access database
>and this remains open throughout the lifetime of the application. The
>problem I have been having is that whilst this connection is open,
>connections to my SQL Server database are not released when I close them.

>The sequence of events I am experiencing is as follows:

>1. Create an ADO Connection object and open it to the Access database.
>2. Create a second ADO Connection object and open it to the SQL Server
>database.
>3. Open a recordset in the SQL Server connection and read out some data.
>4. Close the recordset and set it to Nothing.
>5. Close the SQL Server connection and set it to nothing.
>6. Close the Access connection and set it to nothing.

>When I execute these steps, I would expect my actual connection to SQL
>Server to be removed after step 5. By using both "sp_who" in Query Analyzer
>and also the Profiler tool it is quite clear that the connection does not
>close until step 6 is executed. If I repeat the steps without executing step
>1 (i.e., don't open the Access database connection), the SQL Server
>connection does indeed disconnect immediately after step 5.

>This is causing me a real headache, as this unwanted SQL Server connection
>is blocking my subsequent database updates.

>I have also found that the SQL Server connection will close on its own
>eventually (after about 30 seconds) if I leave the Access database
>connection open. This is no good, though, I need it to close immediately
>rather than at some indeterminate time in the future.

>The actual VB code I'm using is as follows:

>Private Sub Command1_Click()

>    Dim accessConn As ADODB.Connection
>    Dim accessRs As ADODB.Recordset
>    Dim sqlConn As ADODB.Connection
>    Dim sqlRs As ADODB.Recordset

>    'Connect to the Access database
>    Set accessConn = New ADODB.Connection
>    accessConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>                "Data Source=MyDatabase.mdb;"

>    'Connect to the SQL Server database
>    Set sqlConn = New ADODB.Connection
>    sqlConn.Open "Provider=SQLOLEDB;Server=SERVER;" & _
>                "Trusted_Connection=yes;" & _
>                "Initial Catalog=MyDatabase"

>    'Read out some data
>    Set sqlRs = New Recordset
>    sqlRs.Open "select * from MyTable", _
>                sqlConn, adOpenForwardOnly, adLockReadOnly

>    Do Until sqlRs.EOF
>        sqlRs.MoveNext
>    Loop

>    sqlRs.Close
>    Set sqlRs = Nothing

>    'Close the SQL Server connection
>    sqlConn.Close
>    Set sqlConn = Nothing
>    '^^ I expect the connection to be released at this point

>    'Close the Access connection
>    accessConn.Close
>    Set accessConn = Nothing
>    '^^ Connection is actually released at this point

>End Sub

>I would be very grateful for any light that anyone can shed on this as it is
>driving my crazy at the moment!

>My thanks in advance,

>--

>Adam.



Sun, 06 Feb 2005 10:24:30 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Connection not Open + Server has not yet been opened error messages

2. Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000

3. Need Connection string for VB6 DAO DSN-LESS Connections to SQL Server 2000

4. Problem with closing and/or orphaned SQL Server Connections

5. Connection closed after SQL Server Error Message 532

6. Closing Connections SQL Server

7. winsock control not closing connections to a server

8. Problem closing a connection to Sql Server

9. Connection closed after SQL Server Error Message 532

10. connection is closed due to sql server has stopped running

11. HELP VB40 will not close my SQL connection

12. limit connections open sql server

 

 
Powered by phpBB® Forum Software