Only show queries that contain results, and close queries with no results

[tucker61]

My code below - looks for the prefix "Crash" and runs any query that contains "Crash" in the name.

This then runs 13 queries - so i am just looking for a way to close the queries down that are blank, and just leave the ones that i need to look into further.

Any thoughts.

Function Test_it()
Dim strPrefix As String
    strPrefix = "Crash"
Run_Queries (strPrefix)
End Function

Function Run_Queries(strPrefix As String) As Integer
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strQryName  As String
Dim iQryCt      As Integer
Dim iQryRan     As Integer
Dim i           As Integer

    Set dbs = CurrentDb
    iQryCt = dbs.QueryDefs.Count
    i = Len(strPrefix)
    iQryRan = 0

    For Each qdf In dbs.QueryDefs
        strQryName = qdf.Name
        Debug.Print strQryName
        If LCase(Left(strQryName, i)) = LCase(strPrefix) Then
            Debug.Print "    ** Run: " & strQryName & vbTab & qdf.Type
            If qdf.Type = 0 Then                ' Select query
                ' Open this query to view results
                DoCmd.OpenQuery strQryName
                'ExportQuery strQryName, "SELECT * FROM strQryName;", , , Environ("Temp") & "\"

            ElseIf qdf.Type = 48 Then           ' Update query
                dbs.Execute strQryName
            End If
            iQryRan = iQryRan + 1
        Else
  
        End If
    Next qdf
    Set qdf = Nothing
    Set dbs = Nothing
    Run_Queries = iQryRan
End Function

 

[jdraw]

??? ...close the queries down that are blank,...

For clarity, identify the queries that result in No records selected?? 0 is the result??
Perhaps you coud provide a sample of your expected result.

 

[Eugene-LS]

Dim lResCount As Long
' ...
            If qdf.Type = 0 Then                ' Select query
                 lResCount = DCount("*", strQryName)
                 If lResCount > 0 Then  'query contain results !
                     ' ...
                  End If
            ElseIf qdf.Type = 48 Then           ' Update query
                dbs.Execute strQryName
            End If
' ...

 

[CJ_London]

I would go with eugene’s suggestion.

note that access is not case sensitive so there is no benefit in using LCase

i presume none of the select queries rely on queries being executed in the other part of your if clause. If they do it is dangerous to assume they will always execute in the same order

 

[MarkK]

If you open a select query as a datasheet, it actually opens as a form, so you can do...

dim frm as form
docmd.openquery "qSomeQuery"
set frm = screen.activedatasheet
if frm.recordset.recordcount = 0 then docmd.close acquery, "qSomeQuery"

 

[Pat Hartman]

If this is something you are doing for yourself, you can do anything you want as long as you are prepared to suffer the consequences. If you are creating an app for others, you would NEVER open naked queries and let the user add/change/delete data using them.

If you would care to share your objective, I'm sure we can offer a safer alternative, even if you are building this for yourself.