Locate string in queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When customizing an Access application for various clients, I have an
occasional need to go through my application to find and replace all
occurrences of a string (e.g. Change all references to [User1] field to
[User2] because the structure of a referenced ODBC database has changed). The
VBA part is easy using Search/Replace, but is there a way to search through
the contents of all queries to locate all occurrences of a string?

Right now, I must manually go through all the queries to find/replace the
references.
 
Brian,

Warning: "air code"... but try something like this...

Dim qdf As DAO.QueryDef
For Each qdf In CurrentDb.QueryDefs
qdf.SQL = Replace(qdf.SQL,"User1","User2")
Next qdf
 
I just reposted a related question before I searched again & found this.
Let's say that I now want to just find all the occurrences of a particular
sting in the SQL content of the queries collection without actually replacing
anything.

Any ideas?

Steve Schapel said:
Brian,

Warning: "air code"... but try something like this...

Dim qdf As DAO.QueryDef
For Each qdf In CurrentDb.QueryDefs
qdf.SQL = Replace(qdf.SQL,"User1","User2")
Next qdf

--
Steve Schapel, Microsoft Access MVP

When customizing an Access application for various clients, I have an
occasional need to go through my application to find and replace all
occurrences of a string (e.g. Change all references to [User1] field to
[User2] because the structure of a referenced ODBC database has changed). The
VBA part is easy using Search/Replace, but is there a way to search through
the contents of all queries to locate all occurrences of a string?

Right now, I must manually go through all the queries to find/replace the
references.
Click to expand...
Click to expand...
 
Brian,

Well, I suppose this might go part way there?...

Dim qdf As DAO.QueryDef
For Each qdf In CurrentDb.QueryDefs
If InStr(qdf.SQL, "YourString") Then
MsgBox qdf.Name
End If
Next qdf
 
Back
Top