Find text in SQL

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

Guest

Programmer's nighmare: after writing an applicaiton to manipulate & import
fairly complex data from a flat data file to a DB2 database, the user turned
around & asked that I not filter by certain criteria. However, the particular
criteria shows up in quite a number of the 100+ queries involved in the app.

Is there a way to search through the queries collection to find all
occurrences of a string in the SQL statements?

I posted once before on how to do a search/replace and got this as a response:

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

How can I now modify this to simply list each query that contains the string
"User1" so that I can find all the occurrences without necessarily replacing
each one?
 
Here are two subroutines that you can use. The
FindQueriesThatUseAQueryOrTable subroutine uses InStr function to find a
text string within the SQL statement. The FindQueriesThatUseATextString
subroutine uses the Like operator to find a match, which allows you to put
wildcards in your search string if you wish.



Public Sub FindQueriesThatUseAQueryOrTable(strFindString As String)
Dim dbs As DAO.Database
Dim qdf As QueryDef
Dim intC As Integer
Set dbs = CurrentDb
For Each qdf In dbs.QueryDefs
If InStr(qdf.SQL, strFindString) > 0 Then Debug.Print qdf.Name
Next qdf
dbs.Close
End Sub



Public Sub FindQueriesThatUseATextString(strFindString As String)
Dim dbs As DAO.Database
Dim qdf As QueryDef
Dim intC As Integer
Set dbs = CurrentDb
For Each qdf In dbs.QueryDefs
If qdf.SQL Like "*" & strFindString & "*" Then Debug.Print qdf.Name
Next qdf
dbs.Close
End Sub
 
Thanks. That gave me what I needed.

I did, however, get a little surprise; actually rather a large surprise. I
had a query that started with "~sq_c..." show up here.I inserted a MsgBox
qdf.SQL, and it shows the full SELECT statement from this query.

This, in turn, led me to look at the contents of the MSysObjects &
MSysQueries tables, and there they all are - every query I have ever had in
the various databases I used in developing to this point. This is probably
because I generally copy an existing database, then rename & edit objects to
get started so that I do not have to start from scratch. There are several
thousand of these entries (424 in MSysObjects, and 3254 in MSysQueries!). I
cannot imagine having to recreate this database by copying and pasting each
form, query, & report to a new database. Is there any way to purge all the
irrelevant query entries? Compact/repair has no effect.

Thanks in advance. Sorry this ended up being a more complex post than I
intended.
 
Don't assume that the "~sq_c..." queries aren't being used. If you've built
a combo or listbox using the wizard, so that the Row Source is a SQL
statement rather than a named query, that SQL is actually stored in the
database as a query named something along those lines.

However, copying all the forms everything into a new database isn't that
much work. Create the new database then go to File | Get External Data |
Import and select them.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
Yes, I discovered that some of the ~sq_c queries were the
form/control-related queries. It is still a mystery (to me, anyway) why
Access retains all the prior queries. In addition to the ~sq_c's, all the
non-form/control-related queries from the prior development are there in the
MSysQueries table. Is this normal, or was something just corrupted?

I did simply import all to a new database, which cleaned it up.

Thanks.
 
Just another followup on the import. When using this method to clean up the
database, I have to go in and re-add my custom menu & re-set startup options,
etc. and re-append the AllowBypassKey property to my database.
 
Back
Top