I want to list saved querydefs having "tblCorrespondence" 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 "tblCorrespondence"?

Sub ListSavedQrys_Click ()
'************************************************* *****************
' 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_ListSavedQrys_Click
Dim ThisForm As String
ThisForm = Me.Name

Dim MyQryDef As QueryDef, MyDatabase As Database, I As Integer,
HowManyQrys As Integer
Set MyDatabase = DBEngine.Workspaces(0).Databases(0)
Set MyQryDef = MyDatabase.CreateQueryDef("_TestOnly")
HowManyQrys = MyDatabase.QueryDefs.Count
If HowManyQrys > 99 Then
MsgBox "There are " & CStr(HowManyQrys) & " saved queries. The
immediate window will only display 99 lines."
End If
Debug.Print CStr(HowManyQrys)
Exit Sub
For I = 0 To HowManyQrys - 1
Debug.Print MyDatabase.QueryDefs(I).Name
Next I
MyDatabase.QueryDefs.Delete "_TestOnly"

Exit_ListSavedQrys_Click:
Exit Sub

Err_ListSavedQrys_Click:
Dim r As String, k As String, Message3 As String
r = "The following unexpected error occurred in Sub
ListSavedQrys_Click, 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_ListSavedQrys_Click

End Sub

[MGFoster]

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

Like this (air code):

For I = 0 To HowManyQrys - 1
If InStr(MyDatabase.QueryDefs(I).SQL, "tblCorrespondence")>0 Then
Debug.Print MyDatabase.QueryDefs(I).Name
End If
Next I

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

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

iQA/AwUBQmaCfIechKqOuFEgEQKU/wCg7kkUKXSZHRNE99HwOP24NToPU0AAoOG8
ETsSGfAs1XfuVvNbTO09vtmB
=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 "tblCorrespondence"?

Sub ListSavedQrys_Click ()
'************************************************* *****************
' 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_ListSavedQrys_Click
Dim ThisForm As String
ThisForm = Me.Name

Dim MyQryDef As QueryDef, MyDatabase As Database, I As Integer,
HowManyQrys As Integer
Set MyDatabase = DBEngine.Workspaces(0).Databases(0)
Set MyQryDef = MyDatabase.CreateQueryDef("_TestOnly")
HowManyQrys = MyDatabase.QueryDefs.Count
If HowManyQrys > 99 Then
MsgBox "There are " & CStr(HowManyQrys) & " saved queries. The
immediate window will only display 99 lines."
End If
Debug.Print CStr(HowManyQrys)
Exit Sub
For I = 0 To HowManyQrys - 1
Debug.Print MyDatabase.QueryDefs(I).Name
Next I
MyDatabase.QueryDefs.Delete "_TestOnly"

Exit_ListSavedQrys_Click:
Exit Sub

Err_ListSavedQrys_Click:
Dim r As String, k As String, Message3 As String
r = "The following unexpected error occurred in Sub
ListSavedQrys_Click, 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_ListSavedQrys_Click

End Sub

[MLH]

<snip>

Like this (air code):

For I = 0 To HowManyQrys - 1
If InStr(MyDatabase.QueryDefs(I).SQL, "tblCorrespondence")>0 Then
Debug.Print MyDatabase.QueryDefs(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(MyDatabase.QueryDefs(I).SQL, "tblCorrespondence")>0 Then
Debug.Print MyDatabase.QueryDefs(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.QueryDefs(I).SQL
Debug.Print MyString

[MGFoster]

MLH wrote:

<snip>

Like this (air code):

For I = 0 To HowManyQrys - 1
If InStr(MyDatabase.QueryDefs(I).SQL, "tblCorrespondence")>0 Then
Debug.Print MyDatabase.QueryDefs(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.QueryDefs(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:::mgf00 <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("MyQueryName").SQL
End Sub

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

MLH wrote:

<snip>

Like this (air code):

For I = 0 To HowManyQrys - 1
If InStr(MyDatabase.QueryDefs(I).SQL, "tblCorrespondence")>0 Then
Debug.Print MyDatabase.QueryDefs(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.QueryDefs(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:::mgf00 <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(MyDatabase.QueryDefs(I).SQL, "tblCorrespondence")>0 Then
Debug.Print MyDatabase.QueryDefs(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.QueryDefs(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?

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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("MyQueryName").SQL
End Sub

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

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

[Arno R]

"MLH" <CR**@NorthState.net> schreef in bericht news:8f********************************@4ax.com...

<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("MyQueryName").SQL
End Sub

Thx for the suggestion. I tried to implement your idea this way:
For I = 0 To HowManyQrys - 1
If InStr(MyDatabase.QueryDefs(I).SQL, "tblCorrespondence") > 0
MyName = MyDatabase.QueryDefs(I).Name
Debug.Print MyDatabase.QueryDefs(MyName).SQL
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.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("MyQueryName").SQL
End Sub

Thx for the suggestion. I tried to implement your idea this way:
For I = 0 To HowManyQrys - 1
If InStr(MyDatabase.QueryDefs(I).SQL, "tblCorrespondence") > 0
MyName = MyDatabase.QueryDefs(I).Name
Debug.Print MyDatabase.QueryDefs(MyName).SQL
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.OpenSchema(adSchemaViews )
With r
Do While Not .EOF
If InStr(.Fields("VIEW_DEFINITION"), "Classes") <> 0 Then
Debug.Print .Fields("TABLE_NAME"), .Fields("VIEW_DEFINITION")
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

[Arno R]

Hi Lyle,

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).

NO ADO in Access 2.0 So that's not an option.
No need to either. The code as given by MGFoster works.

Arno R

Btw: Thx ! (you know why ...)
Sometimes it looks like "much ADO about nothing"

[MLH]

<snip>

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

xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I probably screwed it up. Will try again.

[Arno R]

> xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I probably screwed it up. Will try again.

Hi MLH
Sorry for this late reaction. My newsserver is completely down since 3 day allready ...

Just wondered if your code works now.
If not, I can post the complete code

Arno R

[MLH]

Thx for the offer.
Please post. I had
no luck & gave up
on it. Renewed
interest, though.
Thx.

xxxxxxxxxxxxxxxxxxxxxx

Hi MLH
Sorry for this late reaction. My newsserver is completely down since 3 day allready ...

Just wondered if your code works now.
If not, I can post the complete code

Arno R

[Arno R]

"MLH" <CR**@NorthState.net> schreef in bericht news:u0********************************@4ax.com...

Thx for the offer.
Please post. I had
no luck & gave up
on it. Renewed
interest, though.
Thx.

xxxxxxxxxxxxxxxxxxxxxx

Hi MLH
Sorry for this late reaction. My newsserver is completely down since 3 day allready ...

Just wondered if your code works now.
If not, I can post the complete code

Arno R

I don't remember what this code was about ..
Could you post your not-working-code, or ask your question again?

Arno R