Prev: reseber mensagen
Next: om
From: PeterM on 6 Mar 2010 17:10
Does anyone know of a way to search thru AC2003 queries to find a text
string? For example, I need to find all queries that contain the text
"Forms".

Thanks for your help!
From: John W. Vinson on 6 Mar 2010 18:43
On Sat, 6 Mar 2010 14:10:01 -0800, PeterM <PeterM(a)discussions.microsoft.com>
wrote:

>Does anyone know of a way to search thru AC2003 queries to find a text
>string? For example, I need to find all queries that contain the text
>"Forms".
>
>Thanks for your help!

There might be a way to use the systems tables, but I wasn't able to figure it
out. Try:


Public Sub SearchQueries()
Dim q As QueryDef
For Each q In CurrentDb.QueryDefs
If InStr(q.SQL, "Forms") > 0 Then
Debug.Print q.Name, q.SQL
End If
Next q
End Sub
--

John W. Vinson [MVP]
From: AG on 6 Mar 2010 20:50
http://www.rickworld.com/products.html

--

AG
Email: npATadhdataDOTcom


"PeterM" <PeterM(a)discussions.microsoft.com> wrote in message
news:2E969E10-6EBF-41B3-8207-AD8312CF6483(a)microsoft.com...
> Does anyone know of a way to search thru AC2003 queries to find a text
> string? For example, I need to find all queries that contain the text
> "Forms".
>
> Thanks for your help!



From: Rob Parker on 6 Mar 2010 21:42
This seems to work:
SELECT MSysObjects.Name, MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId
WHERE (((MSysQueries.Expression) Like "*forms*") AND
((MSysObjects.Type)=5));

It will return "internal" queries (recordsources for forms, combo-boxes,
etc, which have a SQL statement); if you want to exclude those, use this:
SELECT MSysObjects.Name, MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
MSysQueries.ObjectId
WHERE (((MSysObjects.Name) Not Like "~*") AND ((MSysQueries.Expression) Like
"*forms*") AND ((MSysObjects.Type)=5));

HTH,

Rob


PeterM wrote:
> Does anyone know of a way to search thru AC2003 queries to find a text
> string? For example, I need to find all queries that contain the text
> "Forms".
>
> Thanks for your help!


From: PeterM on 7 Mar 2010 13:00
John, AC and Rob... thanks for your answers, works like a champ!

PeterM

"Rob Parker" wrote:

> This seems to work:
> SELECT MSysObjects.Name, MSysQueries.Expression
> FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
> MSysQueries.ObjectId
> WHERE (((MSysQueries.Expression) Like "*forms*") AND
> ((MSysObjects.Type)=5));
>
> It will return "internal" queries (recordsources for forms, combo-boxes,
> etc, which have a SQL statement); if you want to exclude those, use this:
> SELECT MSysObjects.Name, MSysQueries.Expression
> FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id =
> MSysQueries.ObjectId
> WHERE (((MSysObjects.Name) Not Like "~*") AND ((MSysQueries.Expression) Like
> "*forms*") AND ((MSysObjects.Type)=5));
>
> HTH,
>
> Rob
>
>
> PeterM wrote:
> > Does anyone know of a way to search thru AC2003 queries to find a text
> > string? For example, I need to find all queries that contain the text
> > "Forms".
> >
> > Thanks for your help!
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: reseber mensagen
Next: om