Findng Dlookup in my queries

[gpierce9]

12/27/18 Is there a way to find all the queries or forms or reports which might contain a Dlookup?


i have been opening each query but sometimes miss a spot.

[ranman256]

you should not have ANY Dlookups in queries. They ARE the Dlookup, by joining tables for lookups.

Try not to use them in reports....everything should be in the query.
Forms can use them for some things.

[June7]

Probably not without writing VBA code to accomplish. Can get third party add-in that can do this kind of search. I have used Rick Fisher's Find and Replace which costs about $50. I have heard about freebie V-Tools.

[Micron]

I suppose you could write a procedure that one by one, loads the sql of each query by way of accessing the query def object, then use the Instr function on the sql.
Or you could use the database documenter, choose only queries, choose only sql and generate a report that shows the sql for each query as long as you don't mind reading it all.
EDIT
I guess you could also output that report to Word (it is an option) and use Find in Word.

[gpierce9]

you should not have ANY Dlookups in queries. They ARE the Dlookup, by joining tables for lookups.

Try not to use them in reports....everything should be in the query.
Forms can use them for some things.

12/27 I agree with you. That is why I am removing all my dlookups. I figured out a way to make a query one gather data
and then query two is able to join. Runs faster too without the dlookup in the Lan.

I'm looking for a way to find where we placed the dlookups. right now, i open each qry , form and report to see.
but i miss some. glen

[Micron]

findinsql "DLookup"

Function FindInSql(strFind As String) As String
Dim qdef As DAO.QueryDef

For Each qdef In CurrentDb.QueryDefs
  If InStr(qdef.sql, strFind) Then FindInSql = FindInSql & qdef.Name & vbCrLf
Next

If Len(FindInSql & vbNullString) > 0 Then
  'switch comment/uncomment next 2 lines to report via message box or printout to immediate window
  'MsgBox FindInSql
  Debug.Print Replace(FindInSql, vbCrLf, "")
Else
  MsgBox "Search term not found."
End If

Set qdef = Nothing

End Function

Depending on your version, you might need a reference to DAO if not already set.
EDIT: you could easily adapt the above to search form or report controls. A more useful tool might be a form with checkboxes to select the type of objects to look at, then iterate through those selected.

[accesstos]

gpierce9, if you like, you can use the functionality of this code:

Option Compare Database
Option Explicit

Private Const cstrQName As String = "qryInspect"
Private Const cstrTName As String = "tblMyQueryDefs"

Sub InspectQueries()
    Dim qd As DAO.QueryDef
    Dim rs As Recordset

    With CurrentDb
        On Error Resume Next
        .Execute "CREATE TABLE " & cstrTName _
                     & " (qName TEXT(50) PRIMARY KEY, qSQL MEMO);"
        .QueryDefs.Delete cstrQName
        On Error GoTo 0
        .Execute "DELETE * FROM " & cstrTName & ";"
        Set rs = CurrentDb.OpenRecordset(cstrTName)
    End With

    With rs
        For Each qd In CurrentDb.QueryDefs
            .AddNew
            !qName = qd.Name
            !qSQL = qd.SQL
            .Update
        Next qd
        .Close
    End With
    Set rs = Nothing

    With CurrentDb.QueryDefs
        On Error Resume Next
        .Delete cstrQName
        On Error GoTo 0
        .Refresh
        Set qd = New DAO.QueryDef
        qd.SQL = "SELECT qName, " _
                 & "InStr(1,[qSQL],'dlookup',1)>0 AS DLookUps, " _
                 & "InStr(1,[qSQL],'dsum',1)>0 AS DSums, " _
                 & "InStr(1,[qSQL],'dcount',1)>0 AS DCounts, " _
                 & "InStr(1,[qSQL],'dmax',1)>0 AS DMaxes, " _
                 & "InStr(1,[qSQL],'dmin',1)>0 AS DMins, " _
                 & "InStr(1,[qSQL],'davg',1)>0 AS DAvgs " _
                 & "FROM " & cstrTName & ";"
        qd.Name = cstrQName
        .Append qd
        .Refresh
        DoCmd.OpenQuery cstrQName
    End With
End Sub

Paste it in a standard module and run the subroutine.

It creates a table that keeps the names of queries and their sql definitions, fills the table with data and creates a query that looks for some domain aggregate functions in separate fields that you can filter as you want.

I hope it helps.

[Micron]

Is there a way to find all the queries or forms or reports which might contain a Dlookup?

Just realized that this isn't 100% clear. When it comes to reports and forms, where is it that you want to look? Underlying queries only, or calculated controls as well? I was assuming the latter. Why specify forms and reports if only looking at their underlying queries? That would be the same as looking at all the queries only. To search form and report controls would be much more intensive, which is why I mentioned adapting the code I wrote. Then again, even that might be insufficient because you could have all sorts of vba constructed sql with lookups in it.

[davegri]

accesstos, Nice little routine.
I had to modify the code a bit to run on a DB with 80 or so queries. The existing code was including some system internal queries the the mod eliminates:

    With rs        
        For Each qd In CurrentDb.QueryDefs
            'Debug.Print qd.Name
            If Left(qd.Name, 4) <> "~sq_" Then
                .AddNew
                !qName = qd.Name
                !qSQL = qd.SQL
                .Update
            End If
        Next qd
        .Close
    End With

[isladogs]

I've just tried the routine and am also impressed by it. Thank you

Just for information, database objects starting with "~sq_" are the record sources to form/reports and control sources to combos/listboxes etc.
Access stores these as 'temporary queries' in the hidden system table MSysObjects

NOTE: ~sq_f = form ; ~sq_c = form control ; ~sq_r = report ; ~sq_d = report control

So if you want to search all form/report controls you need to use the code as originally posted by accesstos
But if you only want to search saved queries, modify the code as suggested by davegri

EDIT: Suggest one more code change.
The maximum number of characters in an Access object name is 64 - see https://support.office.com/en-us/art...8-98c1025bb47c

So to avoid possible errors, I suggest

...
 .Execute "CREATE TABLE " & cstrTName _
                     & " (qName TEXT(65) PRIMARY KEY, qSQL MEMO);"
...

[accesstos]

The existing code was including some system internal queries the the mod eliminates:

You're right davegri! In this case, it is useless to inspect the hidden queries.
An other safer way to skip all hidden queries is to use the GetHiddenAttribute method of Application object, as shown below:

If Not GetHiddenAttribute(acQuery, qd.Name) Then
    'Is not hidden
    '....
End If

ridders52, very useful informations! Your suggestions makes code more robust!
Thank's a lot!

[isladogs]

An other safer way to skip all hidden queries is to use the GetHiddenAttribute method of Application object

That will also ignore any 'standard' but hidden queries which may not be what the OP wants
And as I wrote earlier, if the OP wants to include form & report controls in their check, the original code would work perfectly

I just ran this code on a very large database including the ~sq_ items - it took around 4 seconds to scan & tabulate the SQL for 3427 queries & 'temp' queries - which is in my view pretty impressive

[accesstos]

I fully agree with you. OP has several methods to choose and I think OP is covered.

At a philosophical level, time and space have no objective substance. However, in our object oriented world, we must save them. ;-)

Thank you for your kind words and happy new year!