Re: Test If a DB Is Open

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Brendan Reynolds (anonymous)
Date: 03/13/05 

Date: Sun, 13 Mar 2005 12:32:08 -0000

That code should certainly close the connection provided the code executes
normally, without error. But it will leave the connection open if an error
occurs, or if you reset the code in break mode during debugging.

When debugging in break mode, you can place the insertion point in the
'rs.Close' line, press Ctrl+F9 to make that the next statement, and press F5
to run the code from that line, closing the recordset and the connection.
(Or just go to the Immediate window, and type 'rs.Close' and Enter, then
'cnn.Close', and Enter).

To ensure that the recordset and the connection are closed if an error
occurs, I use code such as this ...

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset

    On Error GoTo ErrorHandler
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = strConnection
    cnn.Open
    Set rst = New ADODB.Recordset
    rst.Open strSQL, cnn
    '...
    rst.Close
    cnn.Close

ExitProcedure:
    On Error Resume Next
    If Not rst Is Nothing Then
        If rst.State <> adStateClosed Then
            rst.Close
        End If
    End If
    If Not cnn Is Nothing Then
        If cnn.State <> adStateClosed Then
            cnn.Close
        End If
    End If
    On Error GoTo 0
    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly Or
vbInformation, "Test Code"
    Resume ExitProcedure 

-- 
Brendan Reynolds (MVP)
"Eddie's Bakery and Cafe'" <eytchisoneb@hotmail.com> wrote in message 
news:9E6A3FE6-F110-4378-A38B-0F73BB682F0B@microsoft.com...
> Hi Nick,  Thanks for the response and suggestions, I am doing everything 
> you
> suggested, except think I am creating a new connection every time I enter 
> the
> sub.  Below is a snapshot of my code:
>
> Public Sub myDBMgr (...)
>  Dim cnn As ADODB.Connection
>  Dim rs  As ADODB.Recordset
>  ...
>  Set cnn New ADODB.Connection
>  Set rs = New ADODB.Recordset
>  ...
>  cnn.Open strCnn
>  rs.Open strSQL, cnn
>  ...
>  rs.Close
>  Set rs = Nothing
>  cnn.Close
>  Set cnn = Nothing
> End Sub
>
> Thanks for help,
>
> Eddie
>
> "Nick Coe (UK)" wrote:
>
>> In news:CB02A104-6067-474F-8064-97B65A6EF8C3@microsoft.com,
>> Eddie's Bakery and Cafe' typed:
>> > Hi Nick, Thanks for the info.  Is there anyway of getting
>> > around this
>> > problem.  Because every time I run my program (via
>> > switching to
>> > "Form-View") after making software changes, I get this
>> > error.
>> > Currently, I have to make my software changes, kill the VB
>> > Editor,
>> > Kill the Form and re-open the form to run the program.
>> > There has to
>> > be a better way of developing code.
>> >
>> > Thanks,  Eddie
>> >
>>
>> Eddie,
>>
>> That's a new one on me.  Are you absolutely _certain_ that
>> all objects that were open in design view are now closed?
>>
>> Sometimes the VBA editor will open umpteen windows all by
>> itself (actually it depends what you were doing when you
>> closed it, but I hate that kind of assumptive behaviour) and
>> you won't notice because they're hidden behind the one
>> you're working on.
>>
>> Could be a persistant lock in the applications' LDB file.
>> Close your app and Access, double check it _is_ closed and
>> then look in your app folder for a file with the same name
>> as your app and the suffix .ldb.  If it exists delete it.
>> Don't do this with your db open!
>>
>> Double check your options, make certain you've got your app
>> set to share.
>>
>> When you set connection are you using
>> CurrentProject.Connection or creating a new connection?
>> Wouldn't have thought it was an issue but I've always used
>> CurrentProject.Connection or CodeProject.Connection, it's a
>> lot easier.
>>
>> Make sure you close connections and recordsets and set to
>> Nothing when done with.
>>
>> Can't think of anything else at the moment...
>>
>> -- 
>> Nick Coe (UK)
>> AccHelp v1.01 Access Application Help File Builder
>> http://www.alphacos.co.uk/   Download Free Demo Copy
>>
>>
>>

Relevant Pages