Query doen't load into ADO recordset

[cstlaurent]

I am using the same code serveral time with Table or Query. But it doesn't work with a specific query that is build out of 4 tables who works fine in datasheet view. I have build a table from the query and the code work just fine, just doensn't want to work directly from the query.

Set cnn = CurrentProject.Connection
Dim rs_actual As New ADODB.Recordset

rs_actual.Open "qry_Actual", cnn, adOpenStatic, adLockReadOnly

Debug.Print rs_actual.RecordCount (return -1, the actual query return 2566 records in datasheet view)

Do While Not rs_actual.EOF

Debug.Print rs_actual(1)
rs_actual.MoveNext
Loop


I have surf the web for a few hours, and didn't find anything that could help my situation. Any idea why is that happening?

 

[GeofW]

Try

if rs_actual.eof = false then
rs_actual.movelast
Debug.Print rs_actual.RecordCount
end if

 

[cstlaurent]

Tks, but the issue is there is actually no record load in recordset from the Query. I am not able to move through the recordset because there is actually none. when I change from qry_actual to tbl_actual which is a table created from the query all the recordset is load.

Is there anything that stop a query to populate a ado recordset.

Thanks

 

[GeofW]

So rs_actual.EOF is true, as soon as you open it?

 

[cstlaurent]

So rs_actual.EOF is true, as soon as you open it?

This is correct. Even if there is more than 2000 records in the actual query.

 

[GeofW]

qry_Actual opens with records in the query window?

Or are you creating a query string dynamically, storing it in qry_Actual, and then opening it?

If you are using a string, perhaps there is a problem with the criteria. I assume you are not getting any syntax errors.

 

[james_lankford]

...when I change from qry_actual to tbl_actual which is a table created from the query all the recordset is load. .

I don't understand
qry_actual creates tbl_actual ?

so is qry_actual a make table query ?
or an append query ?
or an ordinary select query ?

 

[xenou]

I'm also unclear here. If this is a make table query then perhaps there is no recordset due to the nature of the query - and it needs to be converted to a select query.

Perhaps if you are still having trouble post the query here.

ξ

 

[cstlaurent]

This is the query that I am trying to use as a recordset. From the query Tool Bar I select Datasheet View it return over 2000 records, when I connect using ADO it return 0 records. To go around the problem, I am creating a tempory table from this query wich is working correctly.

SELECT Format([Pstg date],"mm") AS [month], Sum([quantity]*[sku_st time]*[ratio]) AS STD_HR, tbl_project_actual.peats_ID, tbl_Sku.SKU_Type, Sum([amount]*[ratio]) AS [ENI_$], tbl_CLLI.tbl_Region_2_ID
FROM (tbl_Sku INNER JOIN ((tbl_movement INNER JOIN tbl_network_desc ON tbl_movement.Order = tbl_network_desc.Order) INNER JOIN tbl_project_actual ON tbl_network_desc.project = tbl_project_actual.Project) ON tbl_Sku.material = tbl_movement.Material) INNER JOIN tbl_CLLI ON tbl_movement.CLLI = tbl_CLLI.CLLI
WHERE (((tbl_movement.[Pstg date]) Like "2010*"))
GROUP BY Format([Pstg date],"mm"), tbl_project_actual.peats_ID, tbl_Sku.SKU_Type, tbl_CLLI.tbl_Region_2_ID;

But I am not a big fan of creating temp table, so I was looking to understand why this select query wasn't working with ADO recordset.

Thanks in advance.

 

[xenou]

The record count property is not consistent across different platforms or technologies (though I've not tested this out in very many scenarios myself). I would simply avoid relying on it.

So, if the recordset is not EOF, then you know records exist, but not how many. If you need to know the count, run a query using Count():

SELECT COUNT(*) FROM [COLOR="RoyalBlue"](Your Query Here)[/COLOR]

You may certainly feel free to pursue more information on this - but I think your experience shows already that the record count isn't working here. If the recordset is not large, you could also, of course loop through its rows and count them "yourself" ...