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.
