I use the following from within some excel procedures to establish a connection to our database.
Private Const strConn As String = _
"PROVIDER=SQLOLEDB.1 ..."
Sub OpenConnection()
Set cn = CreateObject("ADODB.Connection")
cn.Open strConn
cn.CommandTimeout = 0
Set rs = CreateObject("ADODB.Recordset")
Set rs.ActiveConnection = cn
End Sub
In subsequent code I open the connection using various SQL strings.
I'd like to test if rs
is open so I know that it needs to be closed but the following does not work. How can I change the condition in the following to work?
If (rs.Open = True) Then
rs.Close
End If
The following works but I'd rather not use error trapping in this way:
On Error Resume Next
rs.Close
This is an old topic, but in case anyone else is still looking...
I was having trouble after an undock event. An open db connection saved in a global object would error, even after reconnecting to the network. This was due to the TCP connection being forcibly terminated by remote host. (Error -2147467259: TCP Provider: An existing connection was forcibly closed by the remote host.)
However, the error would only show up after the first transaction was attempted. Up to that point, neither Connection.State nor Connection.Version (per solutions above) would reveal any error.
So I wrote the small sub below to force the error - hope it's useful.
Performance testing on my setup (Access 2016, SQL Svr 2008R2) was approx 0.5ms per call.
Function adoIsConnected(adoCn As ADODB.Connection) As Boolean
'----------------------------------------------------------------
'#PURPOSE: Checks whether the supplied db connection is alive and
' hasn't had it's TCP connection forcibly closed by remote
' host, for example, as happens during an undock event
'#RETURNS: True if the supplied db is connected and error-free,
' False otherwise
'#AUTHOR: Belladonna
'----------------------------------------------------------------
Dim i As Long
Dim cmd As New ADODB.Command
'Set up SQL command to return 1
cmd.CommandText = "SELECT 1"
cmd.ActiveConnection = adoCn
'Run a simple query, to test the connection
On Error Resume Next
i = cmd.Execute.Fields(0)
On Error GoTo 0
'Tidy up
Set cmd = Nothing
'If i is 1, connection is open
If i = 1 Then
adoIsConnected = True
Else
adoIsConnected = False
End If
End Function
This topic is old but if other people like me search a solution, this is a solution that I have found:
Public Function DBStats() As Boolean
On Error GoTo errorHandler
If Not IsNull(myBase.Version) Then
DBStats = True
End If
Exit Function
errorHandler:
DBStats = False
End Function
So "myBase" is a Database Object, I have made a class to access to database (class with insert, update etc...) and on the module the class is use declare in an object (obviously) and I can test the connection with "[the Object].DBStats":
Dim BaseAccess As New myClass
BaseAccess.DBOpen 'I open connection
Debug.Print BaseAccess.DBStats ' I test and that tell me true
BaseAccess.DBClose ' I close the connection
Debug.Print BaseAccess.DBStats ' I test and tell me false
Edit : In DBOpen I use "OpenDatabase" and in DBClose I use ".Close" and "set myBase = nothing" Edit 2: In the function, if you are not connect, .version give you an error so if aren't connect, the errorHandler give you false
Source: Stackoverflow.com