Yea, that is what I think too. No, I am hard wired, and thru vpn. But, how can I trap it so I can vb refresh query (vb refresh query does reconnect and return results)? Or is there a method to "Ping" the server... Maybe I should put in the "Form Open" a 2 minute "Ping" routine? What ping routine? Just a simple query that returns no results? OR maybe set the timeout to a higher value
Here is my connection code...I use a global variable for the connection. When called, if it is the 1st call, the connection will be created, every call after that, it checks to see if the connection is open. If not open, it reconnects. I run this before every sql server call. My problem is that it "Errors" after the connection is made and the results are returned - timing of error seems variable.
Code:
Public Sub Check_Set_Sql_Server_Connection()
If SQL_SERVER_ADODB_CN Is Nothing Then
Set SQL_SERVER_ADODB_CN = New ADODB.Connection
End If
If SQL_SERVER_ADODB_CN.State <> adStateOpen Then
SQL_SERVER_ADODB_CN.Provider = "sqloledb.1" ' Original connect string
SQL_SERVER_ADODB_CN.Properties("Data Source").Value = "XXXXXXXX" ' production integrated security production
SQL_SERVER_ADODB_CN.Properties("Initial Catalog").Value = "XXX" ' integrated security
SQL_SERVER_ADODB_CN.Properties("Integrated Security").Value = "SSPI" ' integrated security
SQL_SERVER_ADODB_CN.Open
End If
End Sub
I just checked... in the code above, I never set the commandtimeout. I checked default and it is 30sec. I think I will change it to 120 (random guess) and have a background query run on the form open event at 100 sec. Does commandtimeout = 0 keep it from closing?
Thanks