This is a simple question, which has been debated before, but can I ask it
again with reference to a specific example? The question is basically
should I close and set to nothing an ADO connection object? Normally, I
would do this and not worry about whether I could have chosen not to bother.
However, in this example, setting the variable to nothing can take ages, up
to 15 seconds, and I wonder whether it is worth it.
I would like a function to test whether I can open a connection to my SQL
Server database. This part is quite easy but sometimes the function can
take a long time to return true or false, especially if the server cannot be
found. The connection object has a ConnectionTimeout property, but this
seems to time from when the server is found and I do not seem to be able to
limit the time that the function spends looking for the server.
To work around this, I open a form which in turn opens a connection
asynchronously. The form has a timer event and if it times out, the form is
hidden and so quickly returns a value to the function. The timer will then
close the form on the next loop. This all works great and allows me to
limit the total time before my function returns false, eg tell it to give up
after 3 seconds.
If the connection object is properly opened, then closing and setting to
nothing is immediate. However, if I stop the SQL Server so the connection
never gets properly opened, then trying to set it to nothing takes ages. So
why not simply close the form and let the module level connection object go
out of scope? What would happen if I didn't bother to close if I had opened
in the first place? Also, I would like to know whether anyone else has
found another way to solve the actual problem of time-limiting the process
of testing a connection.
Here are the gory code details:
' --------------------------------- modConnection
tart ---------------------------------
Option Compare Database
Option Explicit
Public Function CanConnect() As Boolean
On Error GoTo Err_Handler
DoCmd.OpenForm "frmConnection", , , , , acDialog
If IsFormLoaded("frmConnection") Then
If Forms!frmConnection.Form.Tag = "OK" Then
CanConnect = True
End If
End If
Exit_Handler:
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Private Function IsFormLoaded(strName As String)
IsFormLoaded = (SysCmd(acSysCmdGetObjectState, acForm, strName) <> 0)
End Function
' --------------------------------- modConnection
nd ---------------------------------
' --------------------------------- frmConnection
tart ---------------------------------
Option Compare Database
Option Explicit
Dim WithEvents m_cnn As ADODB.Connection
Dim m_strConnection As String
Dim m_lngTimeout As Long
Dim m_lngCount As Long
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Handler
m_strConnection = "Provider=sqloledb;" & _
"Data Source=PC001;" & _
"Initial Catalog=EDL;" & _
"Integrated Security=SSPI"
m_lngTimeout = 3
Set m_cnn = New ADODB.Connection
Me.TimerInterval = 1000
m_cnn.Open m_strConnection, , , adAsyncConnect
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Sub
Private Sub Form_Timer()
On Error GoTo Err_Handler
If Me.Visible Then
m_lngCount = m_lngCount + 1
If m_lngCount >= m_lngTimeout Then
Me.Visible = False
End If
Else
If Me.Tag = "OK" Then
m_cnn.Close
End If
'Set m_cnn = Nothing
DoCmd.Close
End If
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Sub
Private Sub m_cnn_ConnectComplete(ByVal pError As ADODB.Error, _
adStatus As ADODB.EventStatusEnum, _
ByVal pConnection As ADODB.Connection)
On Error GoTo Err_Handler
If adStatus = adStatusOK Then
Me.Tag = "OK"
End If
Me.Visible = False
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Sub
' --------------------------------- frmConnection
nd ---------------------------------
