Challenge: SQL STMT for Retrieving Content(SQL) of Saved Queries

[NickName]

I understand it's easy to list all saved queries of a given Access
database via Msysobjects system table. However, I have not seen any
posting over this NG or other similar ones that also include SQL
statement(conte nt) of these queries, though I've noticed some VB code
for that. Is that because it's simply impossible to get a query
content (not query resultset) from a SQL stmt?

Thanks in advance.

[David W. Fenton]

"NickName" <da****@rock.co m> wrote in
news:11******** **************@ g14g2000cwa.goo glegroups.com:

OK, I tried the help file, and if my understanding is correct the
last three arguments are all optional, though here I explicitly
include the acViewNormal argument, and I also tried without it.
Both failed. Now it seemed that it's syntax error but something
else, error code # 2486

DoCmd.OpenRepor t strReport, acViewNormal
' try the following (not to include the acViewNormal arg)
' DoCmd.OpenRepor t strReport
The next line will fail because the OpenReport command is commented
out.
strTemp = Reports(strRepo rt).RecordSourc e
DoCmd.Close acReport, strReport, acSaveNo
ReturnSQLReport = strTemp

' I put the following line at the end to try to figure out what
the error code means to no avail
Application.Acc essError (2486)

Looks like it's truly a Labor day :) Thanks.

And I'd like to tackle this one (this solution) first.

Why did you domment out the OpenReport?

If you open a report with acViewNormal, it will go to the printer.
YOu don't want that. That's why my original code used acViewDesign
instead.

Try running my code in its original form, but comment out the error
handler before you do. Change thies:

On Error GoTo errHandler

to this:

' On Error GoTo errHandler

Then run the code. If there's an error, the exact line that's
producing the error will be highlighted when you enter Debug mode.
Then come back and tell us what line produced the error and what the
error number and description were.

As long as you insist on altering the code I provided I'm not
interested in helping. The code I wrote works in my environment. I'm
only interested in helping you run it in your environment, and that
means figuring what's causing it to error out for you and not for
me. That means you have to start from my EXACT code.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[NickName]

OK.

1. Let me explain, the following (prevously posted) code snip did not
convey what I was trying to say.
DoCmd.OpenRepor t strReport, acViewNormal
' try the following (not to include the acViewNormal arg)
' DoCmd.OpenRepor t strReport
strTemp = Reports(strRepo rt).RecordSourc *e
DoCmd.Close acReport, strReport, acSaveNo
ReturnSQLReport = strTemp

In actually, I tried the following code to no avail
DoCmd.OpenRepor t strReport
strTemp = Reports(strRepo rt).RecordSourc *e
DoCmd.Close acReport, strReport, acSaveNo
ReturnSQLReport = strTemp

2. I just tried your other solution (commenting out the error handler
on top as 'On Error GoTo errHandler),
created tblMyObjects table with (objectName, objectSQL and objectType
fields),
I tried to run the module, Run / Go or Continue, no error, however, no
data inserted into that table, then,
I tried, Ctrl-G, to bring up debug window, typed in
?PopulateMyObje cts() Enter, got compiling error,
then tried, varation of ?PopulateMyObje cts('reports'), not effective.

Thanks.

P.S. I swear I did not change your code, fyi, I'm using Access 97

?PopulateMyObje cts()

[NickName]

Please see post #31 (my reply followed yours, that is, 28, however, my
newest one showed up as 31), thanks. Maybe something more than weird
is happening ... thks

[David W. Fenton]

"NickName" <da****@rock.co m> wrote in
news:11******** *************@g 14g2000cwa.goog legroups.com:

Please see post #31 (my reply followed yours, that is, 28,
however, my newest one showed up as 31), thanks. Maybe something
more than weird is happening ... thks

These numbers are meaningless as posts are not numbered in my
newsreader, and since I'm not reading from the same newsserver as
you are, the order is unlikely be the same. Likewise, since I'm not
reading at exactly the same time as you are, even if I *were* using
the same newsserver I might be seeing a different set of messages,
since I may have different ones marked read/unread.

The only way to do this is to post a followup to the article that
your post is a followup to. Anything else is basically worthless.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"NickName" <da****@rock.co m> wrote in
news:11******** **************@ z14g2000cwz.goo glegroups.com:

OK.

1. Let me explain, the following (prevously posted) code snip did
not convey what I was trying to say.
DoCmd.OpenRepor t strReport, acViewNormal
' try the following (not to include the acViewNormal arg)
' DoCmd.OpenRepor t strReport
strTemp = Reports(strRepo rt).RecordSourc *e
DoCmd.Close acReport, strReport, acSaveNo
ReturnSQLReport = strTemp
Omitting the acViewNormal argument would have no effect, as the
default behavior is acViewNormal, which means to send it directly to
the printer. The correct argument is acViewPreview.
In actually, I tried the following code to no avail
DoCmd.OpenRepor t strReport
strTemp = Reports(strRepo rt).RecordSourc *e
DoCmd.Close acReport, strReport, acSaveNo
ReturnSQLReport = strTemp
That won't work -- you must use the acViewDesign argument for it to
work as a function.

But the function itself is of little use for your original purpose,
as it can't be called in a query.
2. I just tried your other solution (commenting out the error
handler on top as 'On Error GoTo errHandler),
created tblMyObjects table with (objectName, objectSQL and
objectType fields),
I tried to run the module, Run / Go or Continue, no error,
however, no data inserted into that table, then,
I tried, Ctrl-G, to bring up debug window, typed in
?PopulateMyObje cts() Enter, got compiling error,
then tried, varation of ?PopulateMyObje cts('reports'), not
effective.
Well, it's not a function -- it's a subroutine. So you call it in
the Debug window as:

PopulateMyObjec ts

and hi enter. No question mark (which is required for returning a
value from an expression) and no parens, which are for functions,
not for subroutines.
P.S. I swear I did not change your code, fyi, I'm using Access 97

?PopulateMyObje cts()

Get rid of the question mark and the trailing parens.

If that causes an error, then post the error number and the error
message and tell us which line of my code is highlighted in yellow.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[NickName]

OK. I got the problem resolved. Thank you very much.