Problem populating array from SQL statement

[tngourmet]

I'm attemting to run a simple select statement and populate a named array. This is the code I am using. However, no records are returned (.RecordCount = 0)

Dim arrAttc As Variant
Dim adoConn As ADODB.Connection
Dim rsSql As New ADODB.Recordset

Set adoConn = CurrentProject.Connection
rsSql.Open "SELECT prv.prv_code FROM prv", adoConn
arrAttc = rsSql.GetRows()

It's got to be something simple I'm missing here.

 

[Zion7]

Well, so far so good. just now when retrieving th results, you must reference the correct, elemants.

For x = 0 To UBound(arrAttc, 2)
Debug.Print arrAttc(0, x)
Next

 

[MajP]

Not sure if this helps, but the RecordCount property in ADO does not always return what you think it should. It depends on how you call the recordset. Here is a little discussion on it,but there are even more cases that will return 0 as well. You can search this site or the web:

As you know, the ADO RecordCount property returns the number of records in an ADO recordset. Of course, in several instances, this property also returns a -1 instead. The value RecordCount returns depends on the recordset's cursor type: -1 for a forward- only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source

 

[tngourmet]

Thanks, but irregardless of cursor type, I'm still getting a record count of 0. The next few steps in the sub will be calling a loop of records from the array.
str1 = arrAttc(1)
str2 = arrAttc(2)
etc.

With no records being returned, I'm getting a "subscript out of range" error when I get to the array functions.

 

[RoyVidar]

Consider that recordcount = 0 might mean that zero records are returned ... do you have any records in this table/query?

What is the result of testing for rsSql.bof and rsSql.eof?

Roy-Vidar

 

[tngourmet]

Zion7,

Thanks. When calling my arrays I was only referencing them as a one-dimensional array and not two.

 

[SiberBob]

tngourmet,

Ok,

So can you post your corrected version? I'm new to ADO and trying to see what has happened here...

I have used part of what you have above but I'm getting an error:

Eihter BOF or EOF is True or the current record is deleted. Requested operation requires a current record.

I'm guessing this has to do with the solution you figured out but I'm not sure...

 

[Zion7]

SB, you can try this,
Set adoConn = CurrentProject.Connection
rsSql.Open "SELECT prv.prv_code FROM prv", adoConn
If Not rsSQL.EOF or NOT rsSQL.BOF Then
arrAttc = rsSql.GetRows()
End If

 

[PHV]

Zion7, I'd use either this:
If Not rsSQL.EOF And Not rsSQL.BOF Then
or this:
If Not (rsSQL.EOF Or rsSQL.BOF) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

I would agree, PHV!
Slightly illogical, my suggestion.
Thank-you!