DAO - Read through table, how about stored query?

[strangeryet]

In a previous thread we worked out reading through an Access table, but what do I need to do to modify this code to use a stored query as input rather than the table? Can it be done with DAO? Below is the table example:

Dim strTableName As String
Dim rs As DAO.Recordset

strTableName = "TableName"

Set rs = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset)
Do While Not rs.EOF
MsgBox "Route " & rs.Fields("Tablefieldname")
MsgBox "Field Draw = " & rs.Fields("anotherTableFieldname")
' include processing here
'************************
rs.MoveNext
Loop

This code worked, but now I would like to use a stored query ('qryDailyStarts') instead of the table name. I tried to just change the tablename to the query name in hopes that Access would magically know what I wanted to do...this of course did not work!
Thanks

 

[bubba100]

take a look at qrydef

 

[strangeryet]

I searched all around the internet and in some of the books I have, and although they write about qrydefs..they do not give a firm example as to how I want to use it.
I keep thinking that it is too simple and everyone in the Access VB world knows how to do it...except me.!

I just want to be able to open the query and read each record at a time and take some action on those records.
I got it working on the table level, but I can't get it to work using a query name.

Can anyone supply a example? Thank you.

 

[redapples]

Your code work on a query for me. How did it fail for you? What error occured?

Want the best answers? See FAQ181-2886

 

[strangeryet]

I need to put in a stored query...qryDailyStarts that is a query in the application, instead of using the table name.

If I run this...

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qryDailyStarts", dbOpenDynaset)

Do While Not rs.EOF
MsgBox "Route " & rs.Fields("RoutNum")
MsgBox "Field Draw = " & rs.Fields("NumOfPapers")
' include processing here
'************************
rs.MoveNext
Loop

Nothing happens.

 

[JerryKlmns]

Just to make sure, qryDailyStarts is a SELECT query not an action one, right?

 

[Remou]

It might be better to cut and paste the SQL, so you can see what is happening:

[tt]strSQL="Select ID, FirstName From tblMembers Where ID=" & intID
Set rs = CurrentDb.OpenRecordset(strSQL)[/tt]

 

[strangeryet]

yes it is just a Select query. (there is a 'union' in
the query), but it is only returning records.

 

[strangeryet]

If I cut an paste the query into place, does this mean you can't use a query name as input? If a table name can be used, why not a query name? It's just a different view of the same data?

 

[redapples]

I need to put in a stored query...qryDailyStarts that is a query in the application, instead of using the table name.

If I run this...

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qryDailyStarts", dbOpenDynaset)

Do While Not rs.EOF
MsgBox "Route " & rs.Fields("RoutNum")
MsgBox "Field Draw = " & rs.Fields("NumOfPapers")
' include processing here
'************************
rs.MoveNext
Loop

Nothing happens.

Well something must happen even if it is not what you expect. Please can you tell us what does happen. As I said your code worked for me.

I tried this on a union query also. Have you tried ADO?

Want the best answers? See FAQ181-2886

 

[JerryKlmns]

redapples

Although no1 asked for it, strangeryet should have already posted the SQL statement of the weird qryDailyStarts...