Database connection testing 1

[gregmosu]

Is there a quick way to test if this connection is open... like a try/catch block, or maybe the .Open function returns a result. I couldnt find anything online about it.

Thanks in advance!

Set objConnection = CreateObject("ADODB.Connection")
            
objConnection.Open "Driver={SQL Server};SERVER=" & server & ";Database=" & database & ";Uid=" & username & ";Pwd=" & password & ""

 

[rmikesmith]

Try something like:

If objConnection.State = adStateOpen Then
' do whatever
End If

The ObjectStateEnum constants are:

adStateClosed = 0 'The object is closed
adStateOpen = 1 'The object is open
adStateConnecting = 2 'The object is connecting
adStateExecuting = 4 'The object is executing a command
adStateFetching = 8 'The rows of the object are being retrieved

I would think the Open method would throw an error if a connection couldn't be made.

HTH
Mike

 

[gregmosu]

Thanks for the reply Mike. That worked, didnt do quite what I wanted though. I did find a way to catch errors... but it seems to not work all the time. Heres what I came up with...

Call this on Workbook_open

Private Sub DB_Test()
    
    On Error GoTo CatchError
    
    Set objTestConnection = CreateObject("ADODB.Connection")
    Dim server, database, username, password, port
    server = Sheets("DB").Range("B1").Value
    database = Sheets("DB").Range("B2").Value
    username = Sheets("DB").Range("B3").Value
    password = Sheets("DB").Range("B4").Value
        
    objTestConnection.Open "Driver={SQL Server};SERVER=" & server & ";Database=" & database & ";Uid=" & username & ";Pwd=" & password & ""
    objTestConnection.Close
    Set objTestConnection = Nothing
    UserForm1.Show
    
NormalExit:
    Exit Sub
CatchError:
    MsgBox "Unable to establish database connection."
    Sheets("DB").Visible = True
    GoTo NormalExit
End Sub

 

[rmikesmith]

Can you clarify what you mean by

it seems to not work all the time

What doesn't work? Are you not getting the error message when you think you should?


Incidentally, you might want to move the line destroying the object variable as so:

NormalExit:
   Set objTestConnection = Nothing 
   Exit Sub

so that it is destroyed whether an error occurs or not. Or, if you will use this connection elsewhere (I assume so or why would you need to test the connection ) don't destroy it at all until you are finished using it.


Regards,
Mike