ADODB Connection Timeout in VBA for Excel 2016 - how to check if a connection is still active?

Question

I have developed a small Excel addin using VBA which connects directly to a database. I set up the connection via a DSN. The addin works wonderfully when opening it and going right at it. However, after a while the connection to the database seems to timeout. More precisely, a perfectly valid query returns an error when trying to open the recordset.

My code is something like this:

'Check Connection
If Not MakeConnectionToDB Then
    'Connection failed
[do something]
        Exit Function
    End If
    'Active connection - run the query!
            If Rs Is Nothing Then Set Rs = New ADODB.Recordset  'make sure its an active object
            If Not Rs.State = adStateClosed Then Rs.Close       'make sure its not full (throws error if a query was called before)
            Rs.Open strSQLQuery, CON                            'Run query

the rs.open statement fails if the application was open but not used for a while. This is despite the MakeConnectionToDB UDF, which looks something like this:

If Not ConIsActive Then 'If there is no active connection, make it so
            If CON Is Nothing Then  'Connection may be inactive because the object dropped, or because it timed out, or any other reason - Only recreate the object if the former is the case
                Set CON = New ADODB.Connection
            End If
            On Error Resume Next
            CON.Open strCon         'Try to connect - on error resume statement in order to ignore a connection error, that will be caught below
            On Error GoTo 0
            Err.Clear
            MakeConnectionToDB = ConIsActive    'This is where a connection error will be caught if it occurred
        Else
            MakeConnectionToDB = True 'connection is active already
        End If

and ConIsActive looks like:

Private Function ConIsActive() As Boolean
    'return TRUE if there is an active connection, false otherwise
    Dim blnTemp As Boolean
    blnTemp = False
    If (Not (CON Is Nothing)) And (Not (CON = "")) Then If CON.State = adStateOpen Then blnTemp = True
    ConIsActive = blnTemp
End Function

Basically, I check if the connection is open. My problem: All these checks return TRUE, but the connection isn't open at all. If I connect, then leave the application for a while, then get back to it, all the above will return that the connection is active, but when trying to open the recordset with a new query it will fail, presumably because the server closed the connection or something. I need to find a way to check if the connection is actually able to open a recordset.

Can I ping the server or something? How can I check if the database actually returns a result to my queries? Is there a way that has a higher performance than just sending a test query to the server combined with error handling on the recordset? I suppose that would work, but I need a high performance solution and I don't think doubling the number of queries for a simple connection check is a superior solution...

Any help is appreciated!

ADODB is a huge pain to work with in my experience. Would recommend using one of the db reader types. And also using a using statement. — Jacob H
– Jacob H, Commented Oct 2, 2017 at 13:48
@JacobH you're thinking in VB.NET, this is VBA. There's no using statements, and there's no db reader type. — Mathieu Guindon
– Mathieu Guindon, Commented Oct 2, 2017 at 14:04

Mathieu Guindon · Accepted Answer · 2017-10-02 14:44:34Z

2

Your CON object seems to be globally-scoped, opened once, and then used everywhere in your code, and possibly closed at some point... or not.

Like every single object in any code base written in any language that supports objects, a database connection should be as short-lived as possible.

You open it, you do what you need to do with it, and then you close it. If you don't know what the next command is going to be executed against it and when, then the connection has no business remaining open.

Delete your global-scope CON. Kill it, with fire. A connection should be local to the function or procedure that uses it - it begins in that scope, and ends in that scope.

Or you can encapsulate it in your own object, if that makes things easier for you.

'@Folder("Data.SqlConnection")
Option Explicit
Private Const CONNECTION_STRING As String = "{CONNECTION STRING}"
Private mConnection As ADODB.Connection

Private Sub Class_Initialize()
    Set mConnection = New ADODB.Connection
    mConnection.Open
End Sub

Private Sub Class_Terminate()
    mConnection.Close
    Set mConnection = Nothing
End Sub

Public Sub ExecuteNonQuery(ByVal sql As String, ParamArray params())
    With New ADODB.Command
        Set .ActiveConnection = mConnection
        Dim p As ADODB.Parameter
        For Each p In params
            .Paramaters.Append p
        Next
        .Execute
    End With
End Sub

'...

An instance of that SqlConnection class should also be as short-lived as possible, but now most of the plumbing is abstracted away so your calling code can look like this:

 Const sql As String = "exec dbo.LogUserIn @userName=?, @password=?;"
 With New SqlConnection

     Dim userName As ADODB.Parameter
     Set userName = .CreateStringParameter(Environ$("USERNAME"))

     Dim password As ADODB.Parameter
     Set password = .CreateStringParameter(PromptForPassword)

     .ExecuteNonQuery sql, userName, password

 End With

The connection begins at New SqlConnection, cleanly ends at End With, and you can tweak that SqlClass as you need, to support transactions, and/or as illustrated above, to abstract away the parameter-creating boilerplate.

But the idea remains: you don't create a database connection and leave it dangling in global scope, not knowing whether some code somewhere might have set it to Nothing, or closed it, or started a transaction that was never committed, or God knows what.

Create
Open
Execute
Close

Always. As tightly-scoped as possible. Then you won't have any object lifetime issues.

answered Oct 2, 2017 at 14:44

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bijan Soltani Over a year ago

Thanks a lot Mat's Mug! You are very correct. In fact, I use a class module within which the connection is opened. the CON object is global within the class module. Then there is a module with UDFs that has a global instance of that class. I wrote it this way because I literally have thousands of UDFs in an excel spreadsheet that each require the connection. Therefore opening and closing the connection for each individual query appears tedious. Would you still recommend that approach?

Mathieu Guindon Over a year ago

Global is global, either it's encapsulated in a class or it's exposed. There's no such thing as "global within the class module" - that's Private, not global. UDF's requiring a database connection strike me as dangerously inefficient, regardless of whether the connection remains open or not. Consider writing a macro instead of UDFs. In any case, yes, I'd still recommend that approach. Don't open a connection that you don't know if and/or when it's going to be closed.

Bijan Soltani Over a year ago

Yes, I meant encapsulated. It is inefficient, and I did consider a macro, but for reasons not worth going into I'm forced into this route for a temporary solution. I'll rewrite it accordingly. Thanks again!

Collectives™ on Stack Overflow

ADODB Connection Timeout in VBA for Excel 2016 - how to check if a connection is still active?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related