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]

Never, NEVER declare variables like this:

Dim arr()

Always, ALWAYS do it like this:

Dim arr

Second achieves exactly the same thing but is more flexible.

You know GetRows returns an upside down 0 based 2 D array? ie the second dimension is the big one, not the first.

 

[Jon von der Heyden]

Never, NEVER declare variables like this:

Dim arr()

Always, ALWAYS do it like this:

Dim arr

Ok Boss will do in future...
Any idea why the CopyFromRecordSet stops me from being able to use GetRows?

 

[Richard Schollar]

I presume it's because it moves the cursor to the end of the recordset (ie to EOF). So if you move it back to the start with MoveFirst then you should be able to assign it to an undimensioned variant variable with GetRows. But you will end up with a 2D array, with records across the 2nd dimension (ie reversed compared to the array generated by

v = Range("A1:A10").Value

 

[Jon von der Heyden]

Gotcha Thanks

 

[RoryA]

Never, NEVER declare variables like this:

Dim arr()

Always, ALWAYS do it like this:

Dim arr

Second achieves exactly the same thing but is more flexible.

No it doesn't. First one is an array of type variant, second is a variant.

 

[Richard Schollar]

OK terminology may not be perfect but in practice that statement (AFAIK) is valid. There's no point declaring it arr() when arr does the job just as well.

 

[RoryA]

Depends what sort of error checking you have in place...

 

[Richard Schollar]

It better be a bucket of beer you're buying for your round tonight you pedant

 

[RoryA]

Do I drink anything else?