Skipping queries to be exported

[Dan_S]

Dear All,

I have a VBA procedure that runs all of the queries of a particular name in
a database, and exports them to an Excel spreadsheet. I'd like to skip the
queries where no results are returned to make the spreadsheets more
manageable by its users.

I can do this with DCount, but the process is very slow. The code I have so
far is as follows:

Public Sub ExportRAEQueries()
Dim dbs As Database, qdf As QueryDef
Set dbs = CurrentDb
For Each qdf In dbs.QueryDefs
If Left(qdf.Name, 7) = "AUD_RAE" Then
If DCount("*", qdf.Name) > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdf.Name,
"c:\Reports\RAE Audits.xls", True
End If
End If
Next qdf
Set dbs = Nothing
End Sub

How can I change this code to increase its performance?

Many Thanks!

Dan

 

[Douglas J. Steele]

I don't think there's much you can do to increase the speed given what
you're trying to do. I don't think you'll find any faster method than DCount
in this case.

Sorry.

 

[Dan_S]

Thanks for getting back to me Douglas. I suspected that that would be the case.

Kind Regards,

Dan