General Network Error debugging

[The_Kurgan]

The following code has worked consistently until recently when we received this error: [DBNETLIB][ConnectionWrite (send()).]General network error. Check your network documentation. After a while, the user tried again and it worked. I have to assume that the server became temporarily unavailable. My question is, what is the best way to error-handle this? Should I do some sort of a On Error GoTo loop until the network becomes available? At first, I thought I would go to the line above the error, but that only works for one iteration. Any ideas would be appreciated!

Public Sub Simulate_List1_Click()
Application.ScreenUpdating = False
Review.ListBox6.ColumnWidths = "30; 90; 190; 90"
Review.ListBox6.Clear
Call Refresh_Review
'PLACE DATA ON FORM
'suspense notes
If IsNull(Review.ListBox1.List(Review.ListBox1.ListIndex, 13)) = False Then Review.TextBox2.Text = Review.ListBox1.List(Review.ListBox1.ListIndex, 13)
'loan & borrower last
Review.Label1.Caption = Review.ListBox1.List(Review.ListBox1.ListIndex, 1) & ":  " & Review.ListBox1.List(Review.ListBox1.ListIndex, 2)
'item #
Review.Label39.Caption = 1
'get data from Master table
'Initialize all variables
Set objMyConn1 = New ADODB.Connection
Set objMyRecordset1 = New ADODB.Recordset
'Open Connection
objMyConn1.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=FWY-SQL-DAGP09\Secondary;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SECONDARYTS;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=ReportingAnalytics"
objMyConn1.Open
'Open Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
strSQL1 = "SELECT * FROM WorkArea.Master WHERE Loan_Number = '" & Review.ListBox1.List(Review.ListBox1.ListIndex, 1) & "' ORDER BY [Item Number];"
[B]rs.Open strSQL1, objMyConn1, adOpenStatic, adLockBatchOptimistic   '<------------------------ FAILED HERE ------------------------[/B]
If rs.RecordCount = 0 Then GoTo NoneFound
rs.MoveFirst
'place data in listbox
Do While rs.EOF = False
    Review.ListBox6.AddItem rs(47)
    Review.ListBox6.Column(1, Review.ListBox6.ListCount - 1) = rs(37)
    Review.ListBox6.Column(2, Review.ListBox6.ListCount - 1) = rs(36)
    Review.ListBox6.Column(3, Review.ListBox6.ListCount - 1) = rs(40)
    rs.MoveNext
Loop
'Disconnect the Recordset
NoneFound:
Set rs.ActiveConnection = Nothing
'Close everything
objMyConn1.Close
rs.Close
Set objMyConn1 = Nothing
Set rs = Nothing
Review.Label39.Caption = Review.ListBox6.ListCount + 1
Sheets("Main").Select
Application.ScreenUpdating = True
End Sub

 

[mole999]

If the server isn't available a loop won't help, could test that its there before throwing the query into it, and if not escape out and stop the routine

 

[The_Kurgan]

Thanks for the reply.

I just happened to think of this during the night... The error occurred after I connected, but while I was trying to select a recordset. I guess that would imply it was available and that something happened. Does that sound right?

 

[mole999]

i would connect the server. I would step through the excel and after the query has been built capture with print debug, copy that into the server and see if that runs cleanly

 

[Norie]

Perhaps you could check the State of the connection before trying to open the recordset?

 

[The_Kurgan]

Looking into that... thanks Norie