querydef.SQL is too open

[wsteinbe]

I have a database (Access 2003) that I need to change some of the SQL code. I have the code below, which works in changing all of the queries that begin with LBR:

Function ReplaceFieldNames()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
For Each qdf In db.QueryDefs
If Left(qdf.Name,3) = "LBR" Then _
qdf.SQL = Replace(qdf.SQL, "LBR", "RAC")
End If
Next qdf
End Function

The problem is, it changes the SQL for EVERY query in the database. I added the following code to see which queries it was accessing:
msgbox (qrydef)
and it is only showing the queries that begin with LBR. However, if I look at the SQL on another set of queries that begin with WAR, but have LBR in their SQL statement, the SQL from them have been changed as well.

So my question is, how do I modify the SQL in specific queries without changing it for every query that contains LBR. Thanks!

 

[wsteinbe]

Stumper? So I am working around it by copying the database, running the code, then copying only the queries I need back to the original database. But I would be interested if anyone can figure this out. Thanks.

 

[james_lankford]

your code looks right
and you say that the msgbox only show the querys that you're interested in,
so it is confusing

but the msgbox says
msgbox (qrydef)
shouldbn't it be
msgbox (qdf.SQL)
or
msgbox (qdf.Name)

Function ReplaceFieldNames()
   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef
   
   Set db = CurrentDb
   
   For Each qdf In db.QueryDefs
      If Left(qdf.Name,3) = "LBR" Then 
         debug.print (qdf.SQL) 
         qdf.SQL = Replace(qdf.SQL, "LBR", "RAC")
         debug.print (qdf.SQL) 
      End If
   Next qdf
End Function

maybe try stepping through that and see what happens ?
its really no different from what you've already done though