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