By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 921 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

Search for string in query?

prn
Expert 100+
P: 254
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
Dec 18 '08 #1
Share this Question
Share on Google+
13 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 18 '08 #2

prn
Expert 100+
P: 254
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
Dec 18 '08 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
@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.
Dec 18 '08 #4

prn
Expert 100+
P: 254
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
Dec 18 '08 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
@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.
Dec 18 '08 #6

prn
Expert 100+
P: 254
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
Dec 18 '08 #7

prn
Expert 100+
P: 254
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
Dec 18 '08 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
You're welcome :D
Dec 18 '08 #9

NeoPa
Expert Mod 15k+
P: 31,419
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 ;)
Dec 22 '08 #10

NeoPa
Expert Mod 15k+
P: 31,419
Alternatively try this code in a procedure somewhere. I tested it in a form's open procedure.
Expand|Select|Wrap|Line Numbers
  1.     Dim qry As QueryDef
  2.  
  3.     For Each qry In CurrentDb.QueryDefs
  4.         If InStr(1, qry.SQL, "Your Search String") > 0 Then Debug.Print qry.Name
  5.     Next qry
PS. Don't forget to use Ctrl-G to show the results in the Immediate Pane ;)
Dec 22 '08 #11

ADezii
Expert 5K+
P: 8,623
@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.
    Expand|Select|Wrap|Line Numbers
    1. Dim qdf As DAO.QueryDef
    2. Dim MyDB As DAO.Database
    3. Dim rstKeywords As DAO.Recordset
    4.  
    5. Set MyDB = CurrentDb
    6.  
    7. Set rstKeywords = MyDB.OpenRecordset("tblKeywords", dbOpenForwardOnly)
    8.  
    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
    21.  
    22. rstKeywords.Close
    23. Set rstKeywords = Nothing
SAMPLE OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Keyword [Employees] found in SQL for {Employee Sales by Country}
  2. Keyword [Employees] found in SQL for {Invoices}
  3. Keyword [Employees] found in SQL for {qry_OptimizeIt3}
  4. Keyword [Employees] found in SQL for {qryEmployees}
  5. Keyword [Employees] found in SQL for {qryEmployees_2}
  6. Keyword [Employees] found in SQL for {qryReport}
  7. Keyword [Employees] found in SQL for {Query1}
  8. Keyword [Employees] found in SQL for {Query3}
  9. Keyword [Employees] found in SQL for {Query5}
  10. Keyword [Employees] found in SQL for {Query6}
  11. Keyword [UnitPrice] found in SQL for {Invoices}
  12. Keyword [UnitPrice] found in SQL for {Order Details Extended}
  13. Keyword [UnitPrice] found in SQL for {Order Subtotals}
  14. Keyword [UnitPrice] found in SQL for {Product Sales for 1997}
  15. Keyword [UnitPrice] found in SQL for {Products Above Average Price}
  16. Keyword [UnitPrice] found in SQL for {Quarterly Orders by Product}
  17. Keyword [UnitPrice] found in SQL for {Ten Most Expensive Products}
  18. Keyword [OrderID] found in SQL for {Employee Sales by Country}
  19. Keyword [OrderID] found in SQL for {Invoices}
  20. Keyword [OrderID] found in SQL for {Invoices Filter}
  21. Keyword [OrderID] found in SQL for {Order Details Extended}
  22. Keyword [OrderID] found in SQL for {Order Subtotals}
  23. Keyword [OrderID] found in SQL for {Orders Qry}
  24. Keyword [OrderID] found in SQL for {Product Sales for 1997}
  25. Keyword [OrderID] found in SQL for {Quarterly Orders by Product}
  26. Keyword [OrderID] found in SQL for {Sales by Category}
  27. Keyword [OrderID] found in SQL for {Sales by Year}
  28. Keyword [OrderID] found in SQL for {Sales Totals by Amount}
  29. Keyword [OrderID] found in SQL for {Summary of Sales by Quarter}
  30. Keyword [OrderID] found in SQL for {Summary of Sales by Year}
  31. Keyword [ShippedDate] found in SQL for {Employee Sales by Country}
  32. Keyword [ShippedDate] found in SQL for {Invoices}
  33. Keyword [ShippedDate] found in SQL for {Orders Qry}
  34. Keyword [ShippedDate] found in SQL for {Product Sales for 1997}
  35. Keyword [ShippedDate] found in SQL for {Sales by Year}
  36. Keyword [ShippedDate] found in SQL for {Sales Totals by Amount}
  37. Keyword [ShippedDate] found in SQL for {Summary of Sales by Quarter}
  38. Keyword [ShippedDate] found in SQL for {Summary of Sales by Year}
P.S. - You can also easily add Replace capability into the code logic.
Dec 23 '08 #12

prn
Expert 100+
P: 254
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
Dec 23 '08 #13

NeoPa
Expert Mod 15k+
P: 31,419
Pleased to be able to help Paul.

Have a very Merry Christmas :)
Dec 23 '08 #14

Post your reply

Sign in to post your reply or Sign up for a free account.