Can this be done? Code to loop through querys and tell if field exists 1

[Juddy58]

Hi, I have a very large access database Im currently working on for a client. One of the main tables has had to have a field changed (moved to a seperate table) and this has affected some of the queries. The problem is that because I didn't design it im not sure what queries it has affected.
I have started going through the queries to fix them however because there are hundreds of queries I realise its going to take me ages!
I know its a long shot, but just wondering if anyone has some code that could automate this process (tell me which queries contain this field).
Thanks
Justin

 

[JohnLowell]

Try This:

Sub CheckQuerySQL()
Dim dbs as Database
Dim qdf as QueryDef
Dim strSQL as String
Dim strSearchFor as String

Set dbs = CurrentDB
dbs.QueryDefs.Refresh

strSearchFor = "ColumnName"
For Each qdf In dbs.QueryDefs
strSQL = qdf.SQL
If Instr(1,strSQL, strSearchFor) > 0 then
Debug.Print qdf.Name
End If
Next qdf
Set dbs = Nothing

End Sub

 

[MichaelRed]

Be aware that this (and many similar approaches) will not find many of the more obscure methods of 'queryin'. Queries are often just string constructs used as the "source" object statements for forms and controls, or just recordsets to be processed in other manners.





MichaelRed

 

[FancyPrairie]

MichaelRed is correct. However, here is a query that will prompt you for the name of the field and then will list all queries that include the field. Of cource if the field exists in a sql statement like this "Select * from...", then obviously it won't show up.

PARAMETERS FieldName Text ( 255 );
SELECT MSysObjects.Name, MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId
WHERE (((MSysQueries.Expression) Like "*" & [FieldName] & "*") AND ((Mid([name],1,1))<>"~") AND ((Mid([name],1,4))<>"Msys" And (Mid([name],1,4))<>"USys"))
ORDER BY MSysObjects.Name;

 

[Juddy58]

Thanks for the replys, I will have a look at your query FancyPrarie.
Thanks
Justin

 

[Juddy58]

That query works great thanks!

 

[MichaelRed]

for recent versions of Ms. A. you coulf cut down on the cluutter by useing the split function with the field name as the arg on the query def in F.P.'s search. If the array returned by split has a single element, the field exists and could easily be replaced ans the query ree-generated with the Join function. Such a technique would also work for small modules/procedures. Some instances will still be missed if the field name is derived from a 'calculation'.






MichaelRed