ADODB error - Object was open 1

[keithf01]

Has anyone run into this?
I use the same recordset over in a processing loop by just closing it.


' CHECK RECORDSET STATE, IF OPEN THEN CLOSE
If objAdoMP08RecordSet3.State = adStateOpen Then
objAdoMP08RecordSet3.Close
End If

strQuery = "select pieces_per_pad_qty, green_style from design_screen_area_uv01 where screen_nbr = '" & strScreenNbr & "' and chip_style = '" & strChipStyle & "'"

' OPEN THE RECORD SET
objAdoMP08RecordSet3.Open strQuery


The problem is when it hits the recordset.open it throws an error: -2147217915 'Object was open.'

When I step through the code the value for
objAdoMP08Recordset3.state is 0 which is closed in the
line of code before setting the query.

The recordeset object only gets created once before the processing loop:



' ***** INSTANTIATE THE RECORDSET OBJECT *****
Set objAdoMP08RecordSet3 = New ADODB.Recordset
'PASS THE ACTIVE CONNECTION TO THE RECORDSET OBJECT
objAdoMP08RecordSet3.ActiveConnection = ObjAdoMP08Connection
'SET THE CURSORTYPE TO "STATIC"
objAdoMP08RecordSet3.CursorType = adOpenStatic



Thanks,
Keith

 

[strongm]

I think we only disagree on whether &H200 is reserved or not. And I prefer to go with the details on COMs use of the facilty in MSDN:

http://msdn2.microsoft.com/en-us/library/ms679751.aspx

(but then I would, it supports my view that &200 is OK to use ... ;-) )

 

[BobRodes]

< HRESULT will be the documented error code

Perhaps so, but I've seen considerable opinion to the contrary, admittedly from rather old sources. e. g. from

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/office97/html/web/014.asp:

The actual error codes may be documented without the vbObjectError offset, or they may be documented after being added to the offset, in which case the Case Else statement should subtract vbObjectError, rather than add it.

Here's a code example that removes the offset, too, from

http://msdn2.microsoft.com/en-us/library/Aa140050(office.10).aspx:

HandleError:
  [COLOR=red]If Err.Number = vbObjectError + 1001 Then [/color]
    MsgBox "Your request returned too many results." _
      & "Please refine your search.", vbInformation, "Result Error"
      Err.Clear
  ElseIf Err.Number <> 0 Then
    MsgBox "There was a problem retrieving the data you requested from the following URL:" & vbCrLf _
      & strTargetURL & vbCrLf _
      & "Please contact your systems administrator.", vbInformation, "Data retrieval error"
      Err.Clear
  Else
    GoTo ExitFinally
  End If

Of course, this is Office code, and we all know that Office coders get about the same respect as a banjo player at a guitar camp. So strongm, are you saying that yours is a more recent prevailing point of view or something? In any case, it was on the understanding that error codes may be documented both ways that I checked the number with the offset removed.

Bob

 

[strongm]

>but I've seen considerable opinion to the contrary ... e. g.

As I said earlier, the documentation of vbObjectError is poor (and often wrong, or misleading). Once you find that vbObjectError is actually an FACILITY_ITF HRESULT you can check the much more solid, rigourous COM documentation, you quickly find that the the rule laid down by Microsoft is to document the full HRESULT value, not just the status code

>Here's a code example that removes the offset

Firstly, I have to disagree That example both raises and checks for vbObjectError + 1001. It never removes the offset

Secondly, the error is raised and handled within the same module, there's no client involved so in facty in this case you can (in theory) do more or less what you like.

 

[dilettante]

For the most part vbObjectError seems to be meant for use by components authored in VB. I usually end up defining mine as literal values anyway though (e.g. &H80044101) in a public error Enum, thus providing symbolic names and making the values visible in the Object Browser.

Doing this with exceptions raised within internal program modules is pretty painless anyway though. I think keeping my custom exception numbering and naming consistent with that of system components costs me little or nothing and makes the code more readable.


Consistency sounds great... in theory.

Option Explicit

Private rs As New ADODB.Recordset

Private Sub Command1_Click()
    On Error Resume Next
    rs.Open "NotThere.csv", _
            "Provider=Microsoft.Jet.OLEDB.4.0;" _
          & "Data Source=""" & App.Path & """;" _
          & "Extended Properties = ""Text;HDR=NO;FMT=Delimited""", _
            adOpenStatic, adLockReadOnly, adCmdTable
    MsgBox Hex$(Err.Number) & vbNewLine _
         & Err.Description & vbNewLine _
         & Err.Source
End Sub

Private Sub Command2_Click()
    On Error Resume Next
    rs.MoveNext
    MsgBox Hex$(Err.Number) & vbNewLine _
         & Err.Description & vbNewLine _
         & Err.Source
End Sub

80040E37
The Microsoft Jet database engine could not find the object 'NotThere.csv'. Make sure the object exists and that you spell its name and the path name correctly.
Microsoft JET Database Engine

E78
Operation is not allowed when the object is closed.
ADODB.Recordset

Oops!

I'm beginning to think I agree with both of you. Yes, use the facility code. Too bad Microsoft isn't consistent too though, so does it matter?

 

[BobRodes]

<That example both raises and checks for vbObjectError + 1001. It never removes the offset

Yes, I was expecting you to bring that up.

I say that if a=b+c then a-b=c, therefore this statement is analogous to removing the offset. But the practical point is that either one implies that the error is documented as 1001. Also, one may argue that the fact that the error is raised in the same module simply means that the person providing the example didn't want to go to the trouble of putting together multiple modules for the example. There wouldn't be any reason to use vbobjecterror (other than habit, or some vague idea that it was "best practice") if you weren't exposing the error outside of the internal context.

On the other hand, I definitely agree with you that the COM code is much more rigorous, and it's clear that it's documented using the HRESULT. So, the above argument is really rather academic. I guess I'm really defending my having done a search with the offset removed, because as I said "a lot of Object defined errors are documented under their actual number before the vbObjectError constant is applied." Whether or not it's best practice to document the number minus the offset is really another issue, and I'm finding myself agreeing with you that it isn't. Perhaps the practice (such as it is) needs to be sent the way of the 1 based array.

So ok, you shouldn't do it, but also people do do it and probably document their deeds. Meanwhile, have we found any insight into the OP's problem yet?

Bob

 

[strongm]

>have we found any insight into the OP's problem yet?

Not here. I've been sidetracked ...

 

[BobRodes]

Me too. I've looked around, and found a number of posts on other forums with the same problem, and all with no answer.

 

[JoeAtWork]

Regarding the OP's original problem, I note that you are only checking for the State = adStateOpen. Now I'm wondering if there is any chance the recordset could be in another state other than open or closed, i.e.

adStateConnecting
adStateExecuting
adStateFetching

Maybe your test should actually be for "not closed", i.e.

If objAdoMP08RecordSet3.State <> adStateClosed Then

Although, IMHO, I think the proper thing is to New the recordset each time. That should by itself remove the error.

 

[BobRodes]

Joe, that gets me thinking. Perhaps the issue is with the Connection object rather than the Recordset.