I want to list saved querydefs having "tblCorresponde nce" in their SQL...

[MLH]

I have the following code to list query names in Access 2.0's
Immediate Window. Currently, it lists names of all saved queries.
Can you suggest a modification that will result in it listing only
names of queries referencing "tblCorresponde nce"?

Sub ListSavedQrys_C lick ()
'************** *************** *************** *************** *******
' Lists ALL the table names in the immediate window.
'
' This is useful code in many applications. Save it in template &
' refer to it whenever needed. Uses DAO.
'************** *************** *************** *************** *******

On Error GoTo Err_ListSavedQr ys_Click
Dim ThisForm As String
ThisForm = Me.Name

Dim MyQryDef As QueryDef, MyDatabase As Database, I As Integer,
HowManyQrys As Integer
Set MyDatabase = DBEngine.Worksp aces(0).Databas es(0)
Set MyQryDef = MyDatabase.Crea teQueryDef("_Te stOnly")
HowManyQrys = MyDatabase.Quer yDefs.Count
If HowManyQrys > 99 Then
MsgBox "There are " & CStr(HowManyQry s) & " saved queries. The
immediate window will only display 99 lines."
End If
Debug.Print CStr(HowManyQry s)
Exit Sub
For I = 0 To HowManyQrys - 1
Debug.Print MyDatabase.Quer yDefs(I).Name
Next I
MyDatabase.Quer yDefs.Delete "_TestOnly"

Exit_ListSavedQ rys_Click:
Exit Sub

Err_ListSavedQr ys_Click:
Dim r As String, k As String, Message3 As String
r = "The following unexpected error occurred in Sub
ListSavedQrys_C lick, CBF on " & ThisForm & "."
k = CRLF & CRLF & Str$(Err) & ": " & QUOTE & Error$ & QUOTE
Message3 = r & k
MsgBox Message3, 48, "Unexpected Error - " & MyApp$ & ", rev. " &
MY_VERSION$
Resume Exit_ListSavedQ rys_Click

End Sub

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Like this (air code):

For I = 0 To HowManyQrys - 1
If InStr(MyDatabas e.QueryDefs(I). SQL, "tblCorresponde nce")>0 Then
Debug.Print MyDatabase.Quer yDefs(I).Name
End If
Next I

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmaCfIechKq OuFEgEQKU/wCg7kkUKXSZHRNE 99HwOP24NToPU0A AoOG8
ETsSGfAs1XfuVvN bTO09vtmB
=q6do
-----END PGP SIGNATURE-----

MLH wrote:

I have the following code to list query names in Access 2.0's
Immediate Window. Currently, it lists names of all saved queries.
Can you suggest a modification that will result in it listing only
names of queries referencing "tblCorresponde nce"?

Sub ListSavedQrys_C lick ()
'************** *************** *************** *************** *******
' Lists ALL the table names in the immediate window.
'
' This is useful code in many applications. Save it in template &
' refer to it whenever needed. Uses DAO.
'************** *************** *************** *************** *******

On Error GoTo Err_ListSavedQr ys_Click
Dim ThisForm As String
ThisForm = Me.Name

Dim MyQryDef As QueryDef, MyDatabase As Database, I As Integer,
HowManyQrys As Integer
Set MyDatabase = DBEngine.Worksp aces(0).Databas es(0)
Set MyQryDef = MyDatabase.Crea teQueryDef("_Te stOnly")
HowManyQrys = MyDatabase.Quer yDefs.Count
If HowManyQrys > 99 Then
MsgBox "There are " & CStr(HowManyQry s) & " saved queries. The
immediate window will only display 99 lines."
End If
Debug.Print CStr(HowManyQry s)
Exit Sub
For I = 0 To HowManyQrys - 1
Debug.Print MyDatabase.Quer yDefs(I).Name
Next I
MyDatabase.Quer yDefs.Delete "_TestOnly"

Exit_ListSavedQ rys_Click:
Exit Sub

Err_ListSavedQr ys_Click:
Dim r As String, k As String, Message3 As String
r = "The following unexpected error occurred in Sub
ListSavedQrys_C lick, CBF on " & ThisForm & "."
k = CRLF & CRLF & Str$(Err) & ": " & QUOTE & Error$ & QUOTE
Message3 = r & k
MsgBox Message3, 48, "Unexpected Error - " & MyApp$ & ", rev. " &
MY_VERSION$
Resume Exit_ListSavedQ rys_Click

End Sub

[MLH]

<snip>

Like this (air code):

For I = 0 To HowManyQrys - 1
If InStr(MyDatabas e.QueryDefs(I). SQL, "tblCorresponde nce")>0 Then
Debug.Print MyDatabase.Quer yDefs(I).Name
End If
Next I

I just knew the above was gonna work when I tried it. But, to my
surprise, it did not. I'm gonna try to find out why.

[MLH]

<snip>

Like this (air code):

For I = 0 To HowManyQrys - 1
If InStr(MyDatabas e.QueryDefs(I). SQL, "tblCorresponde nce")>0 Then
Debug.Print MyDatabase.Quer yDefs(I).Name
End If
Next I

Hmmmm??? The problem must be with the .SQL specifier...
I could not even get this to print in the immediate window:

MyString = MyDatabase.Quer yDefs(I).SQL
Debug.Print MyString

[MGFoster]

MLH wrote:

<snip>

Like this (air code):

For I = 0 To HowManyQrys - 1
If InStr(MyDatabas e.QueryDefs(I). SQL, "tblCorresponde nce")>0 Then
Debug.Print MyDatabase.Quer yDefs(I).Name
End If
Next I

Hmmmm??? The problem must be with the .SQL specifier...
I could not even get this to print in the immediate window:

MyString = MyDatabase.Quer yDefs(I).SQL
Debug.Print MyString

I'm not familiar w/ Access 2.0, so maybe the .SQL property of the
QueryDefs wasn't available in that version. My earliest version was '95.

When you say it's not working, what is it doing instead - error,
nothing? If an error, which one?
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Arno R]

SQL.Property surely is available in Access 2.0
This wil print out the SQL-string in the Immediate window:
Sub test ()
Set Db = dbEngine(0)(0)
Debug.Print Db.QueryDefs("M yQueryName").SQ L
End Sub

--
Hope this helps
Arno R
"MGFoster" <[e-mail] > schreef in bericht news:%c******** ***********@new sread1.news.pas .earthlink.net. ..

MLH wrote:

<snip>

Like this (air code):

For I = 0 To HowManyQrys - 1
If InStr(MyDatabas e.QueryDefs(I). SQL, "tblCorresponde nce")>0 Then
Debug.Print MyDatabase.Quer yDefs(I).Name
End If
Next I

Hmmmm??? The problem must be with the .SQL specifier...
I could not even get this to print in the immediate window:

MyString = MyDatabase.Quer yDefs(I).SQL
Debug.Print MyString

I'm not familiar w/ Access 2.0, so maybe the .SQL property of the
QueryDefs wasn't available in that version. My earliest version was '95.

When you say it's not working, what is it doing instead - error,
nothing? If an error, which one?
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[MLH]

On Thu, 21 Apr 2005 02:28:11 GMT, MGFoster <[e-mail] > wrote:

MLH wrote:

<snip>

Like this (air code):

For I = 0 To HowManyQrys - 1
If InStr(MyDatabas e.QueryDefs(I). SQL, "tblCorresponde nce")>0 Then
Debug.Print MyDatabase.Quer yDefs(I).Name
End If
Next I

Hmmmm??? The problem must be with the .SQL specifier...
I could not even get this to print in the immediate window:

MyString = MyDatabase.Quer yDefs(I).SQL
Debug.Print MyString

I'm not familiar w/ Access 2.0, so maybe the .SQL property of the
QueryDefs wasn't available in that version. My earliest version was '95.

When you say it's not working, what is it doing instead - error,
nothing? If an error, which one?

xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxx
Strangely enough, its not returning an error. What does display is the
last value of i on a line by itself.

[MLH]

<snip>

SQL.Property surely is available in Access 2.0
This wil print out the SQL-string in the Immediate window:
Sub test ()
Set Db = dbEngine(0)(0)
Debug.Print Db.QueryDefs("M yQueryName").SQ L
End Sub

Thx for the suggestion. I tried to implement your idea this way:
For I = 0 To HowManyQrys - 1
If InStr(MyDatabas e.QueryDefs(I). SQL, "tblCorresponde nce") > 0
MyName = MyDatabase.Quer yDefs(I).Name
Debug.Print MyDatabase.Quer yDefs(MyName).S QL
End If
Next I

Doesn't work for me. Could it be a timing issue? Hard to imagine.

[Arno R]

"MLH" <CR**@NorthStat e.net> schreef in bericht news:8f******** *************** *********@4ax.c om...

<snip>

SQL.Propert y surely is available in Access 2.0
This wil print out the SQL-string in the Immediate window:
Sub test ()
Set Db = dbEngine(0)(0)
Debug.Print Db.QueryDefs("M yQueryName").SQ L
End Sub

Thx for the suggestion. I tried to implement your idea this way:
For I = 0 To HowManyQrys - 1
If InStr(MyDatabas e.QueryDefs(I). SQL, "tblCorresponde nce") > 0
MyName = MyDatabase.Quer yDefs(I).Name
Debug.Print MyDatabase.Quer yDefs(MyName).S QL
End If
Next I

Doesn't work for me. Could it be a timing issue? Hard to imagine.

Well the code works for me. Perfectly...

You DID adapt the aircode MGFoster gave you?
You did NOT only use the code as you posted I hope?

Sorry if I ask the obvious ... just trying to be sure.

Arno R

[Lyle Fairfield]

MLH wrote:

<snip>

SQL.Propert y surely is available in Access 2.0
This wil print out the SQL-string in the Immediate window:
Sub test ()
Set Db = dbEngine(0)(0)
Debug.Print Db.QueryDefs("M yQueryName").SQ L
End Sub

Thx for the suggestion. I tried to implement your idea this way:
For I = 0 To HowManyQrys - 1
If InStr(MyDatabas e.QueryDefs(I). SQL, "tblCorresponde nce") > 0
MyName = MyDatabase.Quer yDefs(I).Name
Debug.Print MyDatabase.Quer yDefs(MyName).S QL
End If
Next I

Doesn't work for me. Could it be a timing issue? Hard to imagine.

Can one set a reference to ADO in Access 2? I don't know, but if so then
I would do so and then try: (Probably I'd have to hard code the
connection string).

Dim r As ADODB.Recordset
Set r = CurrentProject. Connection.Open Schema(adSchema Views)
With r
Do While Not .EOF
If InStr(.Fields(" VIEW_DEFINITION "), "Classes") <> 0 Then
Debug.Print .Fields("TABLE_ NAME"), .Fields("VIEW_D EFINITION")
End If
.MoveNext
Loop
End With

--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet