getrows not retriving all records (1 Viewer)

Hi,

I'm using the getrows recordset method to load a variant array but the not all records are loaded. The recordcount is correct but the UBound value is way to small. In all the attempts I've tried I have noticed it did work occasionally. It makes me wonder if I have some resource problem even though the number of records is only 58 with 9 fields.

Any ideas anyone?

Cheers

Private Sub cmdGo_Click()
Dim strSqlQuery As String
Dim strAdd As String
Dim intRecCount As Integer
Dim intArrayClear As Integer
Dim intNumberRows As Integer
Dim intNumberFields As Integer
Dim intRowCnt As Integer
Dim intFieldCnt As Integer
Dim qdf As DAO.QueryDef
Dim strResultArray As Variant
Dim strTransResultArray As Variant


' Clear results box
If Me![lstResult].ListCount <> 0 Then
For intArrayClear = 0 To Me![lstResult].ListCount - 1
Me![lstResult].RemoveItem 0
Next
End If

strResultArray = Empty

Set myDb = CurrentDb()
Set rs = Nothing
Set qdf = myDb.QueryDefs("qryAssetDetails")

qdf.Parameters(0) = Me.cboChannel

Set rs = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)

' Sometimes required to get accurate count
rs.MoveLast
rs.MoveFirst
intRecCount = rs.RecordCount
Debug.Print intRecCount

strResultArray = rs.GetRows(rs.RecordCount)
Debug.Print UBound(strResultArray, 1)
Debug.Print UBound(strResultArray, 2)

it may very well be a resource problem. My TV has a machine connected to it with a 2.8 Ghz dual core processor and 2GBs of RAM and sometime I can't even open VBA help. But I am usually running Dreamweaver, plenty of browser tabs and other stuff when I'm doing it.

what all is going on when you try to run this procedure? Any bandwidth usage going on, or network connections?

This is verbatim from VBA help, which you should already know:

The number of rows that you can retrieve is constrained by the amount of available memory. You shouldn't use GetRows to retrieve an entire table into an array if it is large.

Because GetRows returns all fields of the Recordset into the array, including Memo and Long Binary fields, you might want to use a query that restricts the fields returned.

Click to expand...

Thanks for the reply.
The recordset is fine (58 records) so there's no problem with getting the data its just the array loading.
For this task I'll just loop thru the rs to load an array. It'll do the job as the number of columns is static and the number of rows is available via recordcount

Thanks
Cheers