Re: Find text in SQL

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 11/10/04 

Date: Wed, 10 Nov 2004 05:28:01 -0500

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
http://I.Am/DougSteele
(no e-mails, please!)
"Brian" <Brian@discussions.microsoft.com> wrote in message
news:8A810A2A-E2DB-432F-A3AC-B93895C36E88@microsoft.com...
> 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.
>
> "Ken Snell [MVP]" wrote:
>
> > 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
> >
> >
> >
> > -- 
> >
> >         Ken Snell
> > <MS ACCESS MVP>
> >
> >
> > "Brian" <Brian@discussions.microsoft.com> wrote in message
> > news:81E64A15-A0FA-4C63-BA58-A77DA97D245B@microsoft.com...
> > > 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?
> >
> >
> >

Relevant Pages

  • Re: DAO takes too much time to link tables
    ... I am trying to link some tables to a back-end database. ... 'Link the tables contained at the given query. ... Dim rst As DAO.Recordset ... Dim success As Boolean ...
    (microsoft.public.access.modulesdaovba)
  • Re: HELP WITH AUTO EXE PROGRAMMING/CODE
    ... Create a separate database for those times when you won't have your database ... create a Query that returns only the Records about which you ... Dim strsubject As String ...
    (comp.databases.ms-access)
  • Re: error loading DLL
    ... > ' Contains a bitmask that specifies the valid bits in dwFileFlags. ... >> Dim refCurr As Reference ... >>> Dim qdf As DAO.querydef ... >>> ' If query exists, ...
    (microsoft.public.access.formscoding)
  • Re: error loading DLL
    ... ' Contains a bitmask that specifies the valid bits in dwFileFlags. ... > Dim refCurr As Reference ... >> Dim qdf As DAO.querydef ... >> ' If query exists, ...
    (microsoft.public.access.formscoding)
  • Re: mailmerge and sql
    ... means that you will not be able to see them in a database you open using the ... I believe you may have to use DAO instead of ADO to ... then creates a View containing a UNION query. ... Dim oCatalog As ADOX.Catalog ...
    (microsoft.public.word.mailmerge.fields)