CODEWITHSUNDEEP
sqlvb6timeoutdatabase-connectionado
user881048
user881048

Reputation: 21

How do I make an ADO connection timeout sooner if the server doesnt exist?

I have been tasked w/ supporting an old VB6 app. (yay me) I am having trouble with the ADO connection timeout property. The method below works fine if the server exists, but if the server does not exist or network connections havent started up for the machine it will take a full 30 seconds to timeout even with the intTimeout set to 1.

Is there a way for ADO to fail to connect sooner? Is this even possible? Thanks!

Public Sub GetConnectionObject(ByRef oCn As ADODB.Connection, strServer As String,     strInitialCatalog As String, Optional intTimeout = 10)

    Dim strConnectionString As String
    strConnectionString = "Data Source=[SERVER];Provider=SQLOLEDB.1;User ID=ScanReq1;Password=ScanR3Q;Initial Catalog=[INITIALCATALOG];ConnectionTimeout=" & intTimeout & ";"
    strConnectionString = Replace(strConnectionString, "[SERVER]", strServer)
    strConnectionString = Replace(strConnectionString, "[INITIALCATALOG]", strInitialCatalog)

    Set oCn = New ADODB.Connection
    oCn.CursorLocation = adUseClient
    oCn.ConnectionString = strConnectionString
    oCn.CommandTimeout = intTimeout
    oCn.ConnectionTimeout = intTimeout

    oCn.Open

End Sub

Upvotes: 2

Views: 3641

Answers (2)

aucuparia
aucuparia

Reputation: 2051

I've also hit this one. An alternative to setting ConnectionTimeout could be to make the Open call asynchronous, then handle the timeout in your own code. Quick and dirty example below (note: this is in VBA, but should be easily ported to VB6):

Dim conn As New ADODB.Connection
Dim time As Single, timeOut As Single
conn.ConnectionString = "your connection string here"
conn.Open Options:=adAsyncConnect  ' value is 16
timeOut = 5
time = Timer()
Do Until Timer() - time > timeOut Or conn.State = adStateOpen
    DoEvents
Loop
If conn.State <> adStateOpen Then    'value is 1
    'timed out
Else
    'successful
End If

To do it "properly", there is a ConnectionComplete event which you could handle.

Upvotes: 1

AngryHacker
AngryHacker

Reputation: 61596

The ConnectionTimeout kicks in after the TCP connection is made. If the server can't be found, this value is controlled by the Windows TCP subsystem.

If this really is an issue for you, I'd try to ping the box first (there are plenty examples of pinging via VB6 on the net).

Upvotes: 5

Related Questions