question about using embedded SQL insted of saved query 1

[VickyC]

hi
I have a loop in VBA code that needs to retrieve the number of records in a Totals Query on each loop iteration. Between each iteration, changes have been made to the underlying table, so I need to retest the number of records in the Totals Query with each loop. I currently use DAO to save the QryDef As TempTotalsQry, then use DCOUNT("X", "TempTotalsQry") to access the number of records in the query, then delete the QryDef.

My question is this: Is there any way to get these counts by using SQL embedded in the VBA, not saved queries?? For what it's worth, the SQL is "SELECT t.X FROM Tbl1 t GROUP BY t.X".

thank you in advance
Vicky

 

[VickyC]

Actually, something like this will do fine...

Dim db As DAO.Database, rs As DAO.Recordset, strSQL_rs As String

Set db = CurrentDb
strSQL_rs = "SELECT t.Tm FROM " & strTblOutput_d & " t GROUP BY t.Tm"
Set rs = db.OpenRecordset(strSQL_rs)
Debug.Print rs.RecordCount

Vicky

 

[PHV]

Another way:

strSQL_rs = "SELECT Count(*) AS X FROM (SELECT DISTINCT Tm FROM " & strTblOutput_d & ") D"
Set rs = db.OpenRecordset(strSQL_rs)
Debug.Print rs!X

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886