Search for string in query?

[prn]

Hi folks,

I have an inherited access application. At least a half-dozen people have worked on this one before me. The application contains hundreds of queries.

My (current) problem involves some information that is encoded in, shall we say, a non-intuitive way. And that encoding has changed over the years. One of my users noticed today that a report she ran produced an unexpected result. Tracking it down, I found that different queries treated this particular encoding differently, so I checked further and determined which treatment was correct, but now the question is are there other queries that use the old, bad criteria?

As I said, there are hundreds of queries here. Some of them are unused. Some were ad-hoc queries. But there are still hundreds. If I just had to search the VB code, that would be no problem. However, I don't know of a comparable method for searching the contents of queries. There is a characteristic string I could search for, but how to search?

Any thoughts?

Thanks,
Paul

[MMcCarthy]

Hi Paul

My advice is to use the built in Documenter Go to Tools Menu - Analyse - Documenter.

Go to the queries tab and select all. Then go to options and tick the sql box. Make sure the other options are not ticked or set to nothing. When run this produces an access report of all the SQL code for each query. You cannot save this document but you can print it or export it as a rtf for example.

You can then search the text.

Mary

[prn]

Hi Mary,

Thanks for the tip. I had forgotten about the documenter. It's going to be a major operation to extract almost a thousand queries at once, but I guess this may be the only way to search them all. I think I'll let it run overnight. :)

Thanks,
Paul

[MMcCarthy]

@prn
There is a vba code solution but I think it would be very heavy. Essentially it would be a matter of opening every query in query definition and searching the sql for key words, then closing that query and looping to the next one. I can't imagine it would be anything but onerous considering the number of queries. Actually I'm even trying to think how to search the sql as it's not something I would normally do.

[prn]

@msquared
I get the feeling that it's not something that most people normally do. I tend to be an exception to that, I guess. I like to be able to search for things so that I know what needs to be fixed. On occasions like this I feel uncomfortable about Access, but I do appreciate the power of Access as a tool most of the rest of the time.

Thanks again,
Paul

[MMcCarthy]

@prn
If you run into problems come back and we'll try to come up with a VBA solution.

And you are right, Access is a great tool but you have to be patient with it's idiosyncrasies as a developer.

[prn]

Well, the Documenter is running at this very moment. When I come to work tomorrow morning, I'll find out how well it did or did not do. Access is a terrific tool, but, like all tools, it can be frustrating when you need to do something that your tool was not designed for.

Thanks,
Paul

[prn]

WOW! That actually worked already! I was able to export it (File > Export) to a .txt file and now I can search that for the diagnostic strings.

That was great!

Thanks so much, Mary!

Paul

[MMcCarthy]

You're welcome :D

[NeoPa]

The Documenter shouldn't take too long to work at all Paul. You won't need to leave it running overnight. I usually save it (export it) as rtf then open it in MS Word. That way you have the power of Word searching to find your patterns etc.

PS. The choosing of the settings should probably take you longer than it will take Access to produce the report ;)

[NeoPa]

Alternatively try this code in a procedure somewhere. I tested it in a form's open procedure.

    Dim qry As QueryDef
 
    For Each qry In CurrentDb.QueryDefs
        If InStr(1, qry.SQL, "Your Search String") > 0 Then Debug.Print qry.Name
    Next qry

PS. Don't forget to use Ctrl-G to show the results in the Immediate Pane ;)

[ADezii]

@prn
Hello prn! I basically expanded NeoPa's idea. Why not pinpoint exactly the Queries in which your Keywords appear?

1. Create a Table named tblKeywords with a single Field named Keyword {TEXT}
2. Populate this Field with all the Keywords on which you want to search for.
3. Run this generic code.
   

   Dim qdf As DAO.QueryDef
   Dim MyDB As DAO.Database
   Dim rstKeywords As DAO.Recordset
    
   Set MyDB = CurrentDb
    
   Set rstKeywords = MyDB.OpenRecordset("tblKeywords", dbOpenForwardOnly)
    
   With rstKeywords
     Do While Not .EOF
       For Each qdf In CurrentDb.QueryDefs
         If Left$(qdf.Name, 3) <> "~sq" Then         'Filter out Tem Queries
           If InStr(qdf.SQL, ![Keyword]) > 0 Then      'Keyword found in SQL
             Debug.Print "Keyword [" & ![Keyword] & "] found in SQL for {" & qdf.Name & "}"
           End If
         End If
       Next
       .MoveNext
     Loop
   End With
    
   rstKeywords.Close
   Set rstKeywords = Nothing

SAMPLE OUTPUT:

Keyword [Employees] found in SQL for {Employee Sales by Country}
Keyword [Employees] found in SQL for {Invoices}
Keyword [Employees] found in SQL for {qry_OptimizeIt3}
Keyword [Employees] found in SQL for {qryEmployees}
Keyword [Employees] found in SQL for {qryEmployees_2}
Keyword [Employees] found in SQL for {qryReport}
Keyword [Employees] found in SQL for {Query1}
Keyword [Employees] found in SQL for {Query3}
Keyword [Employees] found in SQL for {Query5}
Keyword [Employees] found in SQL for {Query6}
Keyword [UnitPrice] found in SQL for {Invoices}
Keyword [UnitPrice] found in SQL for {Order Details Extended}
Keyword [UnitPrice] found in SQL for {Order Subtotals}
Keyword [UnitPrice] found in SQL for {Product Sales for 1997}
Keyword [UnitPrice] found in SQL for {Products Above Average Price}
Keyword [UnitPrice] found in SQL for {Quarterly Orders by Product}
Keyword [UnitPrice] found in SQL for {Ten Most Expensive Products}
Keyword [OrderID] found in SQL for {Employee Sales by Country}
Keyword [OrderID] found in SQL for {Invoices}
Keyword [OrderID] found in SQL for {Invoices Filter}
Keyword [OrderID] found in SQL for {Order Details Extended}
Keyword [OrderID] found in SQL for {Order Subtotals}
Keyword [OrderID] found in SQL for {Orders Qry}
Keyword [OrderID] found in SQL for {Product Sales for 1997}
Keyword [OrderID] found in SQL for {Quarterly Orders by Product}
Keyword [OrderID] found in SQL for {Sales by Category}
Keyword [OrderID] found in SQL for {Sales by Year}
Keyword [OrderID] found in SQL for {Sales Totals by Amount}
Keyword [OrderID] found in SQL for {Summary of Sales by Quarter}
Keyword [OrderID] found in SQL for {Summary of Sales by Year}
Keyword [ShippedDate] found in SQL for {Employee Sales by Country}
Keyword [ShippedDate] found in SQL for {Invoices}
Keyword [ShippedDate] found in SQL for {Orders Qry}
Keyword [ShippedDate] found in SQL for {Product Sales for 1997}
Keyword [ShippedDate] found in SQL for {Sales by Year}
Keyword [ShippedDate] found in SQL for {Sales Totals by Amount}
Keyword [ShippedDate] found in SQL for {Summary of Sales by Quarter}
Keyword [ShippedDate] found in SQL for {Summary of Sales by Year}

P.S. - You can also easily add Replace capability into the code logic.

[prn]

NeoPa and ADezii: Thank You!

In fact, with the Documenter's options set to include the SQL only and nothing for Fields and Indexes, the Documenter managed to create a buffer full of all 1245 queries in less than 20 minutes. I first failed to select "Nothing" for Fields and Indexes and it looked like it was going to run for a looooong time. Then, with the result in the active window, I was also able to export it (File > Export) to a text file (1.8 MB, 95,669 lines) and could search that.

Fortunately, the diagnostic strings for my current needs were "simple" strings, so I didn't need much search capability. This would make NeoPa's and ADezii's suggestions very attractive, and if I need to to something similar again, I'll certainly consider using the VBA solution. If I had needed real RE matching capabilities, it appears that I could still do that in vba, but it looks a bit more complicated. (Certainly not as straightforward as in Perl :) but doable.)

Thanks, guys, I'm saving a bookmark to this information for sure.

Paul

[NeoPa]

Pleased to be able to help Paul.

Have a very Merry Christmas :)