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?

 

[cstlaurent]

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" ...

If you refer to my first post, I have put some code do loop throught the records and it will not go because my critaria is met, EOF is true right at the beginning because there is no records in the recordset.

Must be language barrier where I am not able to express the situation correctly. I will live with my plan b wich is built a tempory table from the initial query because I am not able to have ADO recordset load correctly the query results but the table results it does.

Thanks anyway.

 

[james_lankford]

I think this is your problem

WHERE (((tbl_movement.[Pstg date]) Like "2010*"))

try changing it to

WHERE (((tbl_movement.[Pstg date]) Like "2010%"))

that will stop it from working in datasheet view but it should get it to work with ado

the sql access uses in not "real" sql
its close, but not standard
access uses * for like, but the "real" way is to use %
ADO may be expecting the real way

 

[cstlaurent]

I think this is your problem

WHERE (((tbl_movement.[Pstg date]) Like "2010*"))

try changing it to

WHERE (((tbl_movement.[Pstg date]) Like "2010%"))

that will stop it from working in datasheet view but it should get it to work with ado

the sql access uses in not "real" sql
its close, but not standard
access uses * for like, but the "real" way is to use %
ADO may be expecting the real way

Well well well,

james_lankford you find the problem. As you said I went into SQL view change the * for % and here we go I am in business. I would never suspect the actual SQL was the source of my headheach.

Thanks for your help on this one...

 

[xenou]

Good catch, James.

 

[leebingate]

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

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.