Error

[chapran]

I have one procedure which create ADODB.Recordset this way:

    strSQL = "SET NOCOUNT ON; SELECT PreferredState, Backcolor, OverrideOtherColorings FROM dbo.PreferredState " & _
        "WHERE (RowDeleted IS NULL OR RowDeleted = 0)"
    Set rsPreferredStates = New ADODB.Recordset
    rsPreferredStates.Open strSQL, objConn, adOpenStatic, adLockOptimistic

The same approach is used in hundreds of places in the application and do not give any problem.
This particular procedure produces an error on 2-3 machines (there are 100+ machines all together in the company) 1-2 times a day. The error is either 3704, Operation is not allowed when the object is closed or
-2147467259, [Microsoft][ODBC SQL Server Driver]Communication link failure

The table PreferredState from which records are selected is used in some other places in the application and there are no problems. The table is tiny - just 3 fields and 2 records.

The procedure is used by every user tens time a day, so totally it is running thousands times and the problem occures 1-2 times, so it is just 0.1%, maybe less. It drives me crazy

Any idea what is wrong either with those machines or with code.

[George1111]

I have one procedure which create ADODB.Recordset this way:

    strSQL = "SET NOCOUNT ON; SELECT PreferredState, Backcolor, OverrideOtherColorings FROM dbo.PreferredState " & _
        "WHERE (RowDeleted IS NULL OR RowDeleted = 0)"
    Set rsPreferredStates = New ADODB.Recordset
    rsPreferredStates.Open strSQL, objConn, adOpenStatic, adLockOptimistic

The same approach is used in hundreds of places in the application and do not give any problem.
This particular procedure produces an error on 2-3 machines (there are 100+ machines all together in the company) 1-2 times a day. The error is either 3704, Operation is not allowed when the object is closed or
-2147467259, [Microsoft][ODBC SQL Server Driver]Communication link failure

The table PreferredState from which records are selected is used in some other places in the application and there are no problems. The table is tiny - just 3 fields and 2 records.

The procedure is used by every user tens time a day, so totally it is running thousands times and the problem occures 1-2 times, so it is just 0.1%, maybe less. It drives me crazy

Any idea what is wrong either with those machines or with code.

Your problem may be that under some circumstances the "objConn" is closed by your program, thereby not allowing a recordset to be opened.

The way I get around this is a follows

If objConn.State = 0 then
      Open_objConn  (Use your code here to open the connection)
Endif

 strSQL = "SET NOCOUNT ON; SELECT PreferredState, Backcolor, OverrideOtherColorings FROM dbo.PreferredState " & _
        "WHERE (RowDeleted IS NULL OR RowDeleted = 0)"
    Set rsPreferredStates = New ADODB.Recordset
    rsPreferredStates.Open strSQL, objConn, adOpenStatic, adLockOptimistic

Sometimes another way is to force the connection closed, before opening it

On Error Resume Next
objConn.Close
Open_objConn  (Use your code here to open the connection)

Run the recordset opening code here

If you want to be a perfectionist and find out exactly where the problem lies, then I seggest you hunt up every occurance of
"objConn.Close" and when that happens, set a flag to OFF, and when it is opened set the flag to ON

Then all you need do is (just before opening the recordset)

If Flag is OFF then
    Open_objConn
End If

Then you will never have the problem (hopefully)