By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,183 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

ADO Connection Object

P: n/a
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 ---------------------------------
Jan 24 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a

Anthony England wrote:
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?


You could try and report back to us ... unless your computer explodes?

By setting ConnectionTimeout to 1 (the default value is 15 as you know,
I'm sure) I reduce my wait times from 22 seconds to 7... still too
slow but less so.

Jan 24 '06 #2

P: n/a
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Anthony England wrote:
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?


You could try and report back to us ... unless your computer explodes?

By setting ConnectionTimeout to 1 (the default value is 15 as you know,
I'm sure) I reduce my wait times from 22 seconds to 7... still too
slow but less so.

Hi Lyle

Thanks for the reply. If I simply close the form without any regard to
whether the connection was open or not, and without any attempt to close or
set to nothing the module-level connection object - everything *seems* to be
fine. I can repeatedly call the function whether the server is on, off or
paused. It has never failed to limit the total wait time to a maximum of 3
seconds (or whatever I specify).

Now, although the computer has not exploded, I feel somewhat unsure about
recomending this as best practice. I don't know what is happening under the
covers and I cannot explain why closing and setting to nothing an open
connection is almost instantaneous, whereas setting to nothing a connection
object which never got opened (due to the SQL Server being stopped) takes so
long. There also seems to be a bug in that if you try to inspect the State
property after the ConnectComplete event has occurred, a runtime error
occurs if the connection did not get opened. You might say this is
expected, since the connection wasn't opened, but I can inspect the
ConnectionString property without a problem so I would expect the State
property to simply tell me the connection wasn't opened, not cause a runtime
error.

As a more general point, may I ask what you would do if you were asked to
check if you could open a connection against SQL Server? The function would
need to run on the client machines which have Access runtime installed but
no special SQL tools or libraries. Ideally it would put a limit on the
amount of time spent before returning false. When you say you bring your
time down to 7 seconds, is that just opening synchronously and waiting to
catch an error, or are you trying the asynchronous approach?

Jan 24 '06 #3

P: n/a
I guess I'd have a long lunch with several martinis and think about it.
If we're talking internet here I'd use the Wininet functions to find
out if the server actually existed ; I think that is virtually
instantaneous.

This is my seven second stuff:

Dim c As ADODB.Connection
Dim t As Long
Dim s As String
s = "PROVIDER=SQLOLEDB.1"
s = s & ";DATA SOURCE=SomeServer"
s = s & ";INITIAL CATALOG=SomeDB"
s = s & ";USER ID=JohnDoe"
s = s & ";PASSWORD=password"
t = Timer()
Set c = New ADODB.Connection
c.ConnectionTimeout = 1
c.ConnectionString = s
c.CursorLocation = adUseClient
On Error Resume Next
c.Open
On Error GoTo 0
Debug.Print c.Errors.Item(0).Description, Timer - t
'[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. 6.26953125

If the connection fail, the connection errors count will be >0 and
checking this will not bomb.

Jan 24 '06 #4

P: n/a

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I guess I'd have a long lunch with several martinis and think about it.
If we're talking internet here I'd use the Wininet functions to find
out if the server actually existed ; I think that is virtually
instantaneous.

This is my seven second stuff:

Dim c As ADODB.Connection
Dim t As Long
Dim s As String
s = "PROVIDER=SQLOLEDB.1"
s = s & ";DATA SOURCE=SomeServer"
s = s & ";INITIAL CATALOG=SomeDB"
s = s & ";USER ID=JohnDoe"
s = s & ";PASSWORD=password"
t = Timer()
Set c = New ADODB.Connection
c.ConnectionTimeout = 1
c.ConnectionString = s
c.CursorLocation = adUseClient
On Error Resume Next
c.Open
On Error GoTo 0
Debug.Print c.Errors.Item(0).Description, Timer - t
'[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. 6.26953125

If the connection fail, the connection errors count will be >0 and
checking this will not bomb.


The advantage of your code is that it is simpler, does not involve a form
and although I do not understand why, setting the connection to nothing
after an error (caused by me stopping the server) is instantaneous - whereas
it takes up to 15 seconds if the connection is opened asynchronously.
The single advantage of my suggested method is that I have greater control
over the timeout period. Although your code is currently returning in under
7 seconds (which is acceptable), this may vary from network to network and I
don't know whether you have any guarantee that it would return in, say,
under 20 seconds. Anyway, I am only looking for the server on a LAN not
over the internet, so perhaps I will go for the standard synchronous
approach.
Thanks for your thoughts.
Jan 24 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.