[thelist] (apparently) Unclosed database connections

Ken Schaefer ken at adOpenStatic.com
Sat Jul 19 01:17:15 CDT 2003


a) It would help if you let us know what the error was. :-)

b) That sub may be helpful, but only if you have recordsets called rs and
connections called conn
Something more flexible might involve passing the object ByRef to the
procedure, which means you could handle any named object. Personally I use:

Sub objDispose( _
    ByRef objToDispose, _
    ByVal blnClose, _
    ByVal blnNothing _
    )

    If blnClose then
        objToDispose.Close
    End If

    If blnNothing then
        Set objToDispose = Nothing
    End If

End Sub

Call objDispose(objRS, True, True)
Call objDispose(objCommand, False, True)
Call objDispose(objConn, True, True)

c) Check to *implicitly* opened connections. If you have something like:

objRS.Open strSQL, strConn
    -or-
objRS.ActiveConnection = objConn

then you implicitly opening a connection which you do not have a handle to,
so you can't get rid of it. You need:

objRS.Open strSQL, objConn
    -or-
Set objRS.ActiveConnection = objConn

Cheers
Ken


----- Original Message ----- 
From: "Bruce MacKay" <b.mackay at massey.ac.nz>
To: <thelist at lists.evolt.org>
Sent: Saturday, July 19, 2003 1:20 PM
Subject: [thelist] (apparently) Unclosed database connections


: Hi folks,
:
: Hope this is not too off-track.
:
: I run a IIS server running 15 separate sites, all running the same VLE
: (virtual learning environment) ASP application.  Each site uses a separate
: Access database as its backend.
:
: For the past 1.5 years, the system has worked flawlessly, but over the
last
: 4 weeks, we have started encountering faults on all the sites - database
: connections do not appear to be closing.   The end result is that any site
: function involving a database call results in an error and the site
becomes
: unusable.
:
: While we have checked the code and cannot find any page which opens a
: connection but doesn't subsequently close it, it is quite possible we have
: missed something.  As a type of shotgun approach, we are thinking of
: calling the following function at the very bottom of each page....
:
: sub Destroy
:      On error Resume Next
:      If (Not (rs Is Nothing)) Then
:          If (rs.State = adStateOpen) Then
:              rs.Close
:              set rs=nothing
:          end if
:      end if
:      If (Not (conn Is Nothing)) Then
:          If (rs.State = adStateOpen) Then
:              conn.Close
:              set conn=nothing
:          end if
:      end if
: End sub
:
: Does anyone have any other suggestions of what we might do?  Changing the
: database is not on our list - any one of our sites has only 10-15 students
: on at any given time, with 20-50 students per site per day.  Access has
: coped with this "load" without any problems thus far.  The databases are
: regularly compacted and currently range from 8-24 MB in size.
:
: The sysop added Norton AntiVirus about 3 weeks ago - given its known
: problems with the FSO object, I had him later remove it hoping that it was
: the source of the problem - unfortunately we weren't able to restore
normal
: behaviour.
:
: If there is another list to which I should be directing this question,
: please let me know.
:
: Cheers,
:
: Bruce
:
: -- 
: * * Please support the community that supports you.  * *
: http://evolt.org/help_support_evolt/
:
: Evolt.org conference in London, July 25-27 2003.  Register today at
http://evolt.org.uk
:
: For unsubscribe and other options, including the Tip Harvester
: and archives of thelist go to: http://lists.evolt.org
: Workers of the Web, evolt !



More information about the thelist mailing list