searching database objects

[AccessMan]

Is there a way to search database objects for the presence of embedded text
strings? Although I'm interested in searching all objects, queries are the
priority.

Thanks!

 

[Allen Browne]

So you want to search the query design for a field name (not the query
results for a value stored in a field)?

If so, see:
Where is a field used?
at:
http://allenbrowne.com/ser-73.html

The code lets you choose which object type(s) to look in.

 

[AccessMan]

Yes, I'm not talking about searching query results. Essentially, I'm talking
about searching query SQL for text strings, and getting a listing of queries
whose SQL contains the string, all without having to open the query first.

I don't want to be limited to searching for field names. I'd like to be
able to search for substrings in an expression used in a query.

I shall check your page.

Thanks!

 

[Allen Browne]

In essence, you want to loop through the QueryDefs, and examine the SQL
property of each one. Use Instr() to see if the desired text is there.

That's what the FindField function does. Amongst other things, it loops:
For Each qdf In db.QueryDefs
and then the FindInTableQuery() loops each Field in the QueryDef.

For good measure, it also examines the Caption of the fields (since that's
the way the user sees the field), and also looks in the query's Filter and
OrderBy properties (since that can cause Access to pop up a parameter
dialog.)

 

[Larry Linson]

Is there a way to search database objects for the presence of embedded text
strings? Although I'm interested in searching all objects, queries are the
priority.

Speed Ferret, http://www.moshannon.com, and FindAndReplace,
http://www.rickworld.com, are two third-party tools that do exactly what you
want. Check the specs... I don't know if Speed Ferret has released an
Access 2003 version. I have both 2002 and 2003 on my computer, so still use
the 2002 version. I've used both these products, and both get good reviews.

Larry Linson
Microsoft Office Access MVP