RecordSet GetRows error

[Jon von der Heyden]

Hi

I am getting an error attempting to load and array from a recordset... This is slimmed down version, initially populating recordset with array but then eventually want to write it back out to an array too...

[COLOR=blue]Public[/COLOR] [COLOR=blue]Sub[/COLOR] phoo1()
 
    [COLOR=blue]Dim[/COLOR] arr()
    [COLOR=blue]Dim[/COLOR] lngArrItem [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
    [COLOR=blue]Dim[/COLOR] recData [COLOR=blue]As[/COLOR] ADODB.Recordset
    [COLOR=blue]Dim[/COLOR] pvc [COLOR=blue]As[/COLOR] PivotCache
 
    [COLOR=blue]Set[/COLOR] recData = [COLOR=blue]New[/COLOR] ADODB.Recordset
    arr = [COLOR=blue]Array[/COLOR]("V", "Z", "O", "W", "N", "J", "W", "X", "V", "O", "L")
 
    recData.Fields.Append "Field1", adVariant
    recData.Open
 
    [COLOR=blue]For[/COLOR] lngArrItem = [COLOR=blue]LBound[/COLOR](arr) [COLOR=blue]To[/COLOR] [COLOR=blue]UBound[/COLOR](arr)
        recData.AddNew
        recData!Field1 = arr(lngArrItem)
        recData.Update
    [COLOR=blue]Next[/COLOR] lngArrItem
 
    [COLOR=blue]Debug.Print[/COLOR] "records:" & recData.RecordCount, "array items:" & [COLOR=blue]UBound[/COLOR](arr) + 1
 
    [COLOR=blue]Set[/COLOR] pvc = ThisWorkbook.PivotCaches.Add(xlExternal)
    [COLOR=blue]Set[/COLOR] pvc.Recordset = recData
    pvc.CreatePivotTable Range("A40")
 
    arr = recData.GetRows [COLOR=darkgreen]'error here[/COLOR] [COLOR=red]********[/COLOR]
 
    [COLOR=blue]Set[/COLOR] recData = [COLOR=blue]Nothing[/COLOR]
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

Error Msg: Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

How can I make this spit out an array again?

Edit: recData.MoveFirst doesn't seem to do the trick either

 

[Richard Schollar]

Hi Jon

Try using the Erase statement on the array first:

Erase arr

 

[Jon von der Heyden]

Thanks, but still no joy. Same error...

 

[Richard Schollar]

Try the Requery method on the recordset:

recData.Requery

Erase arr

arr = recData.GetRows

 

[Jon von der Heyden]

That returns err description 'Operation is not allowed in this context'.

Also, I get an error if I try to sort the field:

recData.Sort = "Field1"

...before the Debug.Print line ("sort order cannot be applied")...

The Debug.Print statement suggests that all values have been loaded as new records but I still think that the problem possibly lies in the way that I load the recordset using the array...

For instance, if I use Range("A1").CopyFromRecordSet recData without the recData.MoveFirst statement then it only returns the last record. It returns the entire recordset when I use MoveFirst. That bit kinda makes sense but I thought I'd metion that in case the issue is linked.

This routine throws errors left right and centre, yet I have used each method successfully in previous projects, only never compiled like this...

 

[Richard Schollar]

Forget the Requery - I've just actually read your code. Didn't appreciate you were creating the recordset purely in memory with no db interaction.

Right, I think you'll still need the Erase statement and use MoveFirst, but change your Dim of arr to this:

Dim arr 'note no brackets

and see if that makes a difference

 

[Jon von der Heyden]

No error produced but it doesn't load the array either. Ubound(arr) = 0...

[COLOR=blue]Public[/COLOR] [COLOR=blue]Sub[/COLOR] phoo1()
    [COLOR=blue]Dim[/COLOR] arr
    [COLOR=blue]Dim[/COLOR] lngArrItem [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
    [COLOR=blue]Dim[/COLOR] recData [COLOR=blue]As[/COLOR] ADODB.Recordset
 
    [COLOR=blue]Set[/COLOR] recData = [COLOR=blue]New[/COLOR] ADODB.Recordset
    arr = [COLOR=blue]Array[/COLOR]("V", "Z", "O", "W", "N", "J", "W", "X", "V", "O", "L")
 
    recData.Fields.Append "Field1", adVariant
    [COLOR=green]'recData.CursorLocation = adUseClient '< believe this is necessary for sort but it still doesn't work[/COLOR]
    recData.Open
 
    [COLOR=blue]For[/COLOR] lngArrItem = [COLOR=blue]LBound[/COLOR](arr) [COLOR=blue]To[/COLOR] [COLOR=blue]UBound[/COLOR](arr)
        recData.AddNew
        recData!Field1 = arr(lngArrItem)
        recData.Update
    [COLOR=blue]Next[/COLOR] lngArrItem
 
    recData.MoveFirst
 
    [COLOR=green]'recData.Sort = "Field1" '< error: sort order cannot be applied[/COLOR]
    [COLOR=green]'Range("J10").CopyFromRecordset recData '< spits out the recordset if I use MoveFirst[/COLOR]
 
    [COLOR=blue]Debug.Print[/COLOR] "records:" & recData.RecordCount, "array items:" & [COLOR=blue]UBound[/COLOR](arr) + 1
 
    [COLOR=blue]Erase[/COLOR] arr
    arr = recData.GetRows
 
    [COLOR=blue]Debug.Print[/COLOR] [COLOR=blue]UBound[/COLOR](arr) [COLOR=green]'< returns zero[/COLOR]
 
    [COLOR=blue]Set[/COLOR] recData = [COLOR=blue]Nothing[/COLOR]
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[Jon von der Heyden]

The plot thickens... I used a 2nd array just to see if the problem lies in using the same variable. The following:

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] phoo1()
    [COLOR="Blue"]Dim[/COLOR] arr(), grrrrr()
    [COLOR="Blue"]Dim[/COLOR] lngArrItem [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] recData [COLOR="Blue"]As[/COLOR] ADODB.Recordset
    
    [COLOR="Blue"]Set[/COLOR] recData = [COLOR="Blue"]New[/COLOR] ADODB.Recordset
    arr = [COLOR="Blue"]Array[/COLOR]("V", "Z", "O", "W", "N", "J", "W", "X", "V", "O", "L")
    
    recData.Fields.Append "Field1", adVariant
    [COLOR="Green"]'recData.CursorLocation = adUseClient '< believe this is necessary for sort but it still doesn't work[/COLOR]
    recData.Open
    
    [COLOR="Blue"]For[/COLOR] lngArrItem = [COLOR="Blue"]LBound[/COLOR](arr) [COLOR="Blue"]To[/COLOR] [COLOR="Blue"]UBound[/COLOR](arr)
        recData.AddNew
        recData!Field1 = arr(lngArrItem)
        recData.Update
    [COLOR="Blue"]Next[/COLOR] lngArrItem
    
    recData.MoveFirst
    
    [COLOR="Green"]'recData.Sort = "Field1" '< error: sort order cannot be applied[/COLOR]
    [COLOR="Green"]'Range("J10").CopyFromRecordset recData '< spits out the recordset if I use MoveFirst[/COLOR]
    
    [COLOR="Blue"]Debug.Print[/COLOR] "records:" & recData.RecordCount, "array items:" & [COLOR="Blue"]UBound[/COLOR](arr) + 1
    
    grrrrr = recData.GetRows
    
    [COLOR="Blue"]Debug.Print[/COLOR] [COLOR="Blue"]UBound[/COLOR](grrrrr) [COLOR="Green"]'< returns zero[/COLOR]
    
    [COLOR="Blue"]Set[/COLOR] recData = [COLOR="Blue"]Nothing[/COLOR]
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

I get the same behavious to when I used arr. Further more the error only throws if I uncomment the CopyFromRecordSet line. Why is that?

So further testing reveals that it returns a two dimensional array. This works:

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] phoo1()
    [COLOR="Blue"]Dim[/COLOR] arr(), grrrrr()
    [COLOR="Blue"]Dim[/COLOR] lngArrItem [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] recData [COLOR="Blue"]As[/COLOR] ADODB.Recordset
    
    [COLOR="Blue"]Set[/COLOR] recData = [COLOR="Blue"]New[/COLOR] ADODB.Recordset
    arr = [COLOR="Blue"]Array[/COLOR]("V", "Z", "O", "W", "N", "J", "W", "X", "V", "O", "L")
    
    recData.Fields.Append "Field1", adVariant
    [COLOR="Green"]'recData.CursorLocation = adUseClient '< believe this is necessary for sort but it still doesn't work[/COLOR]
    recData.Open
    
    [COLOR="Blue"]For[/COLOR] lngArrItem = [COLOR="Blue"]LBound[/COLOR](arr) [COLOR="Blue"]To[/COLOR] [COLOR="Blue"]UBound[/COLOR](arr)
        recData.AddNew
        recData!Field1 = arr(lngArrItem)
        recData.Update
    [COLOR="Blue"]Next[/COLOR] lngArrItem
    
    recData.MoveFirst
    
    [COLOR="Green"]'recData.Sort = "Field1" '< error: sort order cannot be applied[/COLOR]
    [COLOR="Green"]'Range("J10").CopyFromRecordset recData '< spits out the recordset if I use MoveFirst[/COLOR]
    
    [COLOR="Blue"]Debug.Print[/COLOR] "records:" & recData.RecordCount, "array items:" & [COLOR="Blue"]UBound[/COLOR](arr) + 1
    
    grrrrr = recData.GetRows
    
    [COLOR="Blue"]Debug.Print[/COLOR] [COLOR="Blue"]UBound[/COLOR](grrrrr, 2) [COLOR="Green"]'< returns 10[/COLOR]
    
    [COLOR="Blue"]Set[/COLOR] recData = [COLOR="Blue"]Nothing[/COLOR]
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

Why is that? How do I force a one-dimensional array from it?

 

[Jon von der Heyden]

Aaaah ok, I need to use:

grrrrr = Application.Transpose(recData.GetRows())

But I still don't get why the CopyFromRecordSet affects this?

 

[Jon von der Heyden]

Howdy Rich

Thanks for your efforts on this. I've successfully managed to get an array back out of the recordset now (albeit still a 2D array - but that's easily solved)...

I'm going to start a new thread on sorting a recordset since it's a different question altogether.