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

msquared

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

msquared

Quote:

Originally Posted by 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

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

Quote:

Originally Posted by msquared

Actually I'm even trying to think how to search the sql as it's not something I would normally do.

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

msquared

Quote:

Originally Posted by prn

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

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

msquared

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.

Expand|Select|Wrap|Line Numbers

     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

Quote:

Originally Posted by 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

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

Create a Table named tblKeywords with a single Field named Keyword {TEXT}
Populate this Field with all the Keywords on which you want to search for.
Run this generic code.
Expand|Select|Wrap|Line Numbers
1. Dim qdf As DAO.QueryDef
2. Dim MyDB As DAO.Database
3. Dim rstKeywords As DAO.Recordset
5. Set MyDB = CurrentDb
7. Set rstKeywords = MyDB.OpenRecordset("tblKeywords", dbOpenForwardOnly)
9. With rstKeywords
10. Do While Not .EOF
11. For Each qdf In CurrentDb.QueryDefs
12. If Left$(qdf.Name, 3) <> "~sq" Then 'Filter out Tem Queries
13. If InStr(qdf.SQL, ![Keyword]) > 0 Then 'Keyword found in SQL
14. Debug.Print "Keyword [" & ![Keyword] & "] found in SQL for {" & qdf.Name & "}"
15. End If
16. End If
17. Next
18. .MoveNext
19. Loop
20. End With
22. rstKeywords.Close
23. Set rstKeywords = Nothing

SAMPLE OUTPUT:

Expand|Select|Wrap|Line Numbers

 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 :)

Search for string in query?

re: Search for string in query?

re: Search for string in query?

re: Search for string in query?

re: Search for string in query?

re: Search for string in query?

re: Search for string in query?

re: Search for string in query?

re: Search for string in query?

re: Search for string in query?

re: Search for string in query?

re: Search for string in query?

re: Search for string in query?

re: Search for string in query?

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about