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

[pietlinden]

huh? You want the SQL query typed out and the resulting recordset it
describes?
Where to? You can loop through the querydefs collection and print the
querydef's SQL

dim qdf as dao.querydef 'redundant, cuz ado doesn't have a querydef
object
for each qdf in dbengine(0)(0).querydefs
debug.print qdf.name
debug.print qdf.SQL
debug.print 'blank line
next qdf

[NickName]

Very good and I appreciate it. Now, two things here.
(A) The given Access database has tons of queries, over 200, so, the
debugging runs out of its default capacity, it hasn't finished even B
yet;
(B) So, is it feasible to interface with File System, that is, write
each query to a single file (query name as file name, query content as
file content and so on and so forth)?

Good job, again thanks.

[Douglas J. Steele]

Take a look at what Arvin Meyer has at
http://www.datastrat.com/Code/DocDatabase.txt

That will dump not only the queries but the forms, reports, modules and
macros, each into its own file.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"NickName" <da****@rock.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...

Very good and I appreciate it. Now, two things here.
(A) The given Access database has tons of queries, over 200, so, the
debugging runs out of its default capacity, it hasn't finished even B
yet;
(B) So, is it feasible to interface with File System, that is, write
each query to a single file (query name as file name, query content as
file content and so on and so forth)?

Good job, again thanks.

[NickName]

Douglas,

Thank you very much for the pointer. It seems great for the idea of
what I intend to do with this db, and we're getting close. With a
little tweak I can get what I need, however,
(A) the undocumented command/function of Application.SaveAsText can't
seem to handle other object other than dbs.QueryDefs(i).Name,
as the following
Application.SaveAsText acQuery, dbs.QueryDefs(i).{otherThanNameObject--
you get the idea}, "C:\aFolder\itsSub\" & dbs.QueryDefs(i).Name &
".txt"
what alternative may I apply?
(B) What if I want to save all the queries into a big AllQueries file?

Again I truly appreciate it.

[Douglas J. Steele]

SaveAsText needs the name of the query (or whatever) as its 2nd parameter.
What would you want to put there instead? (Have you looked at what gets
saved?)

No, I don't believe it would be possible using SaveAsText to put everything
into a single file. You could write each one to a file, append that file to
the "master file", then delete the first file you created. Messy, and
probably not much better than looping writing the text out yourself.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"NickName" <da****@rock.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Douglas,

Thank you very much for the pointer. It seems great for the idea of
what I intend to do with this db, and we're getting close. With a
little tweak I can get what I need, however,
(A) the undocumented command/function of Application.SaveAsText can't
seem to handle other object other than dbs.QueryDefs(i).Name,
as the following
Application.SaveAsText acQuery, dbs.QueryDefs(i).{otherThanNameObject--
you get the idea}, "C:\aFolder\itsSub\" & dbs.QueryDefs(i).Name &
".txt"
what alternative may I apply?
(B) What if I want to save all the queries into a big AllQueries file?

Again I truly appreciate it.

[NickName]

OK, what I meant by
//
Application.SaveAsText acQuery,
dbs.QueryDefs(i).{otherThanNam*eObject--
you get the idea}, "C:\aFolder\itsSub\" & dbs.QueryDefs(i).Name &
".txt"
//
is
Application.SaveAsText acQuery, dbs.QueryDefs(i).SQL,
"C:\aFolder\itsSub\" & dbs.QueryDefs(i).Name &
".txt"
so as to get a query's content (sql statement), however,
Application.SaveAsText method won't do the job.
Is there another way to output dbs.QueryDefs(i).SQL to file?

Thanks.

[pietlinden]

How about something like this:
Public Sub WriteSQLsToFile(ByVal strFile As String)
On Error Resume Next

Dim qdf As DAO.QueryDef
Dim intFileNumber As Integer

intFileNumber = FreeFile ' Get unused file number.
Kill strFile
Open strFile For Output As #intFileNumber ' Create file name.

For Each qdf In DBEngine(0)(0).QueryDefs
If Left$(qdf.Name, 1) <> "~" Then
Print #intFileNumber, qdf.Name
Print #intFileNumber, "-----------"
Print #intFileNumber, qdf.SQL
Print #intFileNumber, ""
Print #intFileNumber, ""
End If
Next qdf

Close #intFileNumber ' Close file.

MsgBox "All queries written to " & vbCrLf & strFile

End Sub
Oh, this is wrong then... You'd have to open your new file INSIDE the
loop, and close it there too. pass the queryname or something and
output. Maybe do something fun like a routine to strip out illegal
characters or whatever.... but I have to leave SOME of the fun for
you... I mean, otherwise, what do you learn from all of my work?
Nothing.

[David W. Fenton]

"NickName" <da****@rock.com> wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:

OK, what I meant by
//
Application.SaveAsText acQuery,
dbs.QueryDefs(i).{otherThanNam*eObject--
you get the idea}, "C:\aFolder\itsSub\" & dbs.QueryDefs(i).Name &
".txt"
//
is
Application.SaveAsText acQuery, dbs.QueryDefs(i).SQL,
"C:\aFolder\itsSub\" & dbs.QueryDefs(i).Name &
".txt"
so as to get a query's content (sql statement), however,
Application.SaveAsText method won't do the job.
Is there another way to output dbs.QueryDefs(i).SQL to file?

I think you have a fundamental misunderstand of what SaveAsText
does. It's a command that saves all the properties of Access objects
to text files. If you do:

application.saveastext acquery, [queryname], [filename]

You'll get a lot of information, but it's not just the SQL.

If all you want is the SQL, then you've got to write your file with
I/O functions.

Actually, there's one other choice. Create a function that returns
the .SQL property of a querydef, and then use MSysObjects as your
source table. If your function is called ReturnSQL(), the query
would look like this:

SELECT MSysObjects.Name, ReturnSQL([Name]) AS SQL
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((MSysObjects.Name) Not Like
"~*")) ORDER BY MSysObjects.Name;

(if you want the temporary queries, then you can remove the filter
on LIKE "~*")

The function looks like this:

Public Function ReturnSQL(strQueryName As String, _
Optional db As DAO.Database) As String
Dim strTemp As String
Dim bolInitializedDB As Boolean

If db Is Nothing Then
Set db = CurrentDb()
bolInitializedDB = True
End If
strTemp = db.QueryDefs(strQueryName).SQL
ReturnSQL = strTemp
If bolInitializedDB Then Set db = Nothing
End Function

If called only from a query based on MSysObjects, you won't need any
error handling to deal with non-existent queries, but a
general-purpose function would check to see if the query exists, or
have an error handler to recover from that condition.

Once you have a query like this, you can export it as text, if you
like.

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

[NickName]

Very nice, thanks. But I'm not Access programmer, so I did not know
what Application.SaveAsText would do exactly.

[NickName]

One problem, when I tried to appy this approach to get reports and its
respective sql statement, on the third report, I got error, then,
prompted to click through, not a good way to go, what could be the
problem? thks.

[altacct]

David,

Howdy. Long time no correspond. Hope you're doing well.

That's great code. Last I remembered, you didn't do much ADO work. Is
that still the case? I'd love to be able to hook this up in ADO, but
I'm having a brain fart when it comes to finding an object that returns
the sql for a view. Any thoughts?

Jeremy

[David W. Fenton]

"NickName" <da****@rock.com> wrote in
news:11*********************@g14g2000cwa.googlegro ups.com:

One problem, when I tried to appy this approach to get reports and
its respective sql statement, on the third report, I got error,
then, prompted to click through, not a good way to go, what could
be the problem? thks.

I don't know what you mean by "on the third report."

What are you running? The query? And do you mean the third query in
the results list gives you an error? If so, what's the error?

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

[David W. Fenton]

al*****@yahoo.com wrote in
news:11*********************@z14g2000cwz.googlegro ups.com:

Howdy. Long time no correspond. Hope you're doing well.

That's great code. Last I remembered, you didn't do much ADO work.
Is that still the case? I'd love to be able to hook this up in
ADO, but I'm having a brain fart when it comes to finding an
object that returns the sql for a view. Any thoughts?

I still don't do ADO, so can't say how it would be done.

It wouldn't surprise me if ADO supported a function that could pull
the SQL of a view, since that would be useful in any number of db
engines. But I wouldn't even know where to start searching for it.

Of course, the approach I presented is tied to undocumented system
tables, which is never the best idea. But it's the only way to get a
recordset result. I guess you could do a disconnected recordset in
ADO, though. You'd have to walk through the collection of saved
queries to get the query names, though.

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

[NickName]

I'm sorry I wasn't clear enough about the problem description.
I changed the sql statement a bit to use
Type = -32764 for reports,
then I got the 3262 item not in the collection error

Now when I read it again I realize
db.QueryDefs(strQueryName).SQL is probably only for SQL statement
within a query, and when I tried to 'minic' it with report by changed
it to
db.ReportDefs(strQueryName).SQL to return SQL statement within a report
VBA complained no such a thing.

So, the question is how to return query statement for all the reprots
of a given database?

Again many thanks.

[Larry Linson]

It is relatively easy to traverse the object model with VBA code, and, there
is good reason that Microsoft has not documented the system tables -- at
least one has changed in every release of Access, sometimes even to deleting
one or adding another.

Larry Linson
Microsoft Access MVP

"NickName" <da****@rock.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

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(content) 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.com> wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:

I'm sorry I wasn't clear enough about the problem description.
I changed the sql statement a bit to use
Type = -32764 for reports,
then I got the 3262 item not in the collection error

Now when I read it again I realize
db.QueryDefs(strQueryName).SQL is probably only for SQL statement
within a query, and when I tried to 'minic' it with report by
changed it to
db.ReportDefs(strQueryName).SQL to return SQL statement within a
report VBA complained no such a thing.

So, the question is how to return query statement for all the
reprots of a given database?

Well, the object model for reports and queries is completely
different, as queries are Jet objects and reports are Access objects
(customized Jet objects with properties that Jet knows nothing
about). And you can't get access to the recordsource property until
you've opened up the form or report.

So, there are two steps:

1. get a list of all the reports/forms

2. open each one in turn and return the Recordsource property.

Now, if you're supplying the name of the report/form already, you
don't need to do step 1, you just need to do step 2:

Public Sub ReturnFormRecordsource(strForm as String) As String
Dim strTemp As String

DoCmd.OpenForm strForm, acDesign, , , , acHidden
strTemp = Forms(strForm).Recordsource
DoCmd.Close acForm, strForm, acSaveNo
End If

If you ever need to do step 1, though, there are two methods:

1. DAO in all versions of Access:

Dim doc As Document
For Each doc In db.Containers!Reports.Documents
Debug.Print doc.Name
Next doc

2. using the new AllReports and AllForms collections in A2K and
beyond:

Dim obj As AccessObject
For Each obj In CurrentProject.AllReports
Debug.Print obj.Name
Next obj

But if you're going to base this on a query using MSysObjects, you
don't need to look up the names of the objects, since those are
stored in MSysObjects already.

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

[NickName]

I'm trying to re-write you wrote for returning report sql stmt as a
function

Public Function ReturnSQLrpt(strForm As String) As String
Dim strTemp As String

DoCmd.OpenForm strForm, acDesign, , , , acHidden
strTemp = Forms(strForm).RecordSource
DoCmd.Close acForm, strForm, acSaveNo

End Function

However, Access 97 complained can't carry out this action at this time,
then about this line
DoCmd.OpenForm strForm, acDesign, , , , acHidden

Should I try it with Access 2003?

Many thanks.

[Br]

NickName <da****@rock.com> wrote:

I'm trying to re-write you wrote for returning report sql stmt as a
function

Public Function ReturnSQLrpt(strForm As String) As String
Dim strTemp As String

DoCmd.OpenForm strForm, acDesign, , , , acHidden
strTemp = Forms(strForm).RecordSource
DoCmd.Close acForm, strForm, acSaveNo

End Function

However, Access 97 complained can't carry out this action at this
time, then about this line
DoCmd.OpenForm strForm, acDesign, , , , acHidden

Should I try it with Access 2003?

Many thanks.

Just a guess, but perhaps it doesn't allow you to open a form in design
view _and_ have it hidden? Try opening it in normal view and hidden. You
an still read the form's propeties...
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[NickName]

Ahe,

I'm a bit too adventurous (not Access form or VBA sort of programmer
but doing this sort advanced Access stuff).

I tried to turn the above form into report function, but failed since
no method like OpenReport exists.

Public Function ReturnSQLrpt(strReport As String) As String

Dim strTemp As String

DoCmd.OpenReport strReport, acDesign, , , , acHidden
strTemp = Reports(strReport).RecordSource
DoCmd.Close acReport, strReport, acSaveNo

End Function

"Try opening it in normal view and hidden"
How to do that? Thanks.

[Br]

NickName <da****@rock.com> wrote:

"Try opening it in normal view and hidden"

How to do that? Thanks.

DoCmd.OpenReport strReport, acDesign, , , , acHidden

Change the "acDesign" to "acNormal" (I think) ?
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Larry Linson]

"NickName" wrote

I tried to turn the above form into
report function, but failed since
no method like OpenReport exists.

Works for me.

Where did you get the information that "no method like OpenReport exists"?

Larry Linson
Microsoft Access MVP

[NickName]

I've tried Access 97 and Access 2003, both failed with the report
function.
Access 2003 complained that "wrong number of arguments or invalid
property assignment". It freaks me out. Help. Thanks.

Don

[David W. Fenton]

"NickName" <da****@rock.com> wrote in
news:11*********************@z14g2000cwz.googlegro ups.com:

I've tried Access 97 and Access 2003, both failed with the report
function.
Access 2003 complained that "wrong number of arguments or invalid
property assignment". It freaks me out. Help. Thanks.

The arguments for forms and reports are different.

Type it into the debug window and hit Crtl-Spacebar. This will pop
up the intellisense and show you the arguments for each command.

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

[David W. Fenton]

"NickName" <da****@rock.com> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:

I'm trying to re-write you wrote for returning report sql stmt as
a function

Public Function ReturnSQLrpt(strForm As String) As String
Dim strTemp As String

DoCmd.OpenForm strForm, acDesign, , , , acHidden
strTemp = Forms(strForm).RecordSource
DoCmd.Close acForm, strForm, acSaveNo

End Function
Ah, yes -- I forgot to assign the return value. You need to add this
line at the end:

ReturnSQLrpt = strTemp
However, Access 97 complained can't carry out this action at this
time, then about this line
DoCmd.OpenForm strForm, acDesign, , , , acHidden

Should I try it with Access 2003?

Just thinking through this (as you can see, I didn't actually test
my code!), I remember having problems with trying to use a function
in a form recordsource that returned the value of a global variable,
and that if it hadn't been initialized, opened a dialog form to get
the value. This doesn't work -- a form can't be opened in order for
a form recordsource.

But, my function used the acDialog argument.

Now, just testing, I found that you can't open a form in a function
called in a query, so my approach is not going to work.

I was just certain that there was a way to get to properties like
the Recordsource of a form without having to open it, but I can't
seem to find it.

So, the only approach that I think is going to work is store the SQL
strings in some kind of persistent structure, either in memory or in
a table. I'd probably store this in a class module, but for you it's
probably easiest to store it in a table.

So, here's something that would do the job and then you can use that
table instead of MSysObjects.

First, create a table, e.g., tblMyObjects. The columns should be:

Fieldname Data Type
ObjectID AutoNumber set as Primary Key
ObjectType Text 10
ObjectName Text 255
ObjectSQL Memo

Now, you need code to populate this table. It's best to have this
code delete all existing records, since you don't want stale
information about the SQL.

So, the code to populate this table would look something like this:

Public Sub PopulateMyObjects(Optional db As DAO.Database)
On Error GoTo errHandler
Dim bolDBInitialize As Boolean
Dim strSQL As String
Dim doc As Document
Dim strObjectName As String
Dim strObjectSQL As String
Dim lngCounter As Long

If db Is Nothing Then
Set db = CurrentDb()
bolDBInitialize = True
End If

' clear out the existing data
strSQL = "DELETE * FROM tblMyObjects;"
db.Execute strSQL, dbFailOnError

' Queries
strSQL = "INSERT INTO tblMyObjects ( ObjectName, ObjectSQL, "
strSQL = strSQL & " ObjectType ) SELECT MSysObjects.Name, "
strSQL = strSQL & " ReturnQuerySQL([Name]) AS SQL, 'Query'"
strSQL = strSQL & " FROM MSysObjects"
strSQL = strSQL & " WHERE (((MSysObjects.Name) Not Like '~*')"
strSQL = strSQL & " AND ((MSysObjects.Type)=5));"
db.Execute strSQL, dbFailOnError
Debug.Print "Inserted Query SQL Strings (" & db.RecordsAffected &
")"

' Forms
For Each doc In db.Containers!Forms.Documents
strObjectName = doc.Name
DoCmd.OpenForm strObjectName, acDesign, , , , acHidden
strObjectSQL = Forms(strObjectName).RecordSource
If Len(strObjectSQL) = 0 Then strObjectSQL = "None"
DoCmd.Close acForm, strObjectName, acSaveNo
strSQL = "INSERT INTO tblMyObjects ( ObjectName, ObjectSQL, "
strSQL = strSQL & " ObjectType ) VALUES ('"
strSQL = strSQL & strObjectName & "', '" & strObjectSQL & "'"
strSQL = strSQL & ", 'Form');"
db.Execute strSQL, dbFailOnError
lngCounter = lngCounter + 1
Next doc
Debug.Print "Inserted Form SQL Strings (" & lngCounter & ")"
lngCounter = 0

' Reports
Application.Echo False
For Each doc In db.Containers!Reports.Documents
strObjectName = doc.Name
DoCmd.OpenReport strObjectName, acViewDesign
strObjectSQL = Reports(strObjectName).RecordSource
If Len(strObjectSQL) = 0 Then strObjectSQL = "None"
DoCmd.Close acReport, strObjectName, acSaveNo
strSQL = "INSERT INTO tblMyObjects ( ObjectName, ObjectSQL, "
strSQL = strSQL & " ObjectType ) VALUES ('"
strSQL = strSQL & strObjectName & "', '" & strObjectSQL & "'"
strSQL = strSQL & ", 'Report');"
db.Execute strSQL, dbFailOnError
lngCounter = lngCounter + 1
Next doc
Application.Echo True
Debug.Print "Inserted Report SQL Strings (" & lngCounter & ")"

Set doc = Nothing
If bolDBInitialize Then Set db = Nothing

exitRoutine:
Set doc = Nothing
If bolDBInitialize Then Set db = Nothing
Debug.Print "...Finished!"
Exit Sub

errHandler:
MsgBox Err.Number & ": " & Err.Description, , "Error in
PopulateMyObjects()" Resume exitRoutine
End Sub

This following function is called for the Queries:

Public Function ReturnQuerySQL(strQueryName As String, _
Optional db As DAO.Database) As String
Dim strTemp As String
Dim bolInitializedDB As Boolean

If db Is Nothing Then
Set db = CurrentDb()
bolInitializedDB = True
End If
strTemp = db.QueryDefs(strQueryName).SQL
ReturnQuerySQL = strTemp
If bolInitializedDB Then Set db = Nothing
End Function

Now, you can write a query on tblMyObjects to display all the SQL
for Queries, Forms and Reports.

Now, after all of that, it could be that you *can* get the SQL out
of the forms and reports without opening them. I just couldn't
figure it out when I tried. If someone else is able to do it, I hope
they'll post, because it would be much cleaner than this method,
since it wouldn't require this persistent table.

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

[NickName]

> End Function

"Ah, yes -- I forgot to assign the return value. You need to add this
line at the end:

ReturnSQLrpt = strTemp
"

I tried both of the following to no avail with Access 2003, what did I
miss? Thanks.
Public Function ReturnSQLrpt(strReport As String) As String

Dim strTemp As String
DoCmd.OpenReport strReport, acDesign, , , , acHidden
strTemp = Reports(strReport).RecordSourc*e
ReturnSQLrpt = strTemp
DoCmd.Close acReport, strReport, acSaveNo
End Function
Public Function ReturnSQLrpt(strReport As String) As String
Dim strTemp As String

DoCmd.OpenReport strReport, acDesign, , , , acHidden
strTemp = Reports(strReport).RecordSourc*e
DoCmd.Close acReport, strReport, acSaveNo
ReturnSQLrpt = strTemp

End Function

[David W. Fenton]

"NickName" <da****@rock.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

End Function

"Ah, yes -- I forgot to assign the return value. You need to add
this line at the end:

ReturnSQLrpt = strTemp

I tried both of the following to no avail with Access 2003, what
did I miss? Thanks.

Public Function ReturnSQLrpt(strReport As String) As String

Dim strTemp As String

DoCmd.OpenReport strReport, acDesign, , , , acHidden
strTemp = Reports(strReport).RecordSourc*e
ReturnSQLrpt = strTemp
DoCmd.Close acReport, strReport, acSaveNo

End Function

Public Function ReturnSQLrpt(strReport As String) As String

Dim strTemp As String

DoCmd.OpenReport strReport, acDesign, , , , acHidden
strTemp = Reports(strReport).RecordSourc*e
DoCmd.Close acReport, strReport, acSaveNo
ReturnSQLrpt = strTemp

End Function

Well, both should be erroring out on the line:

DoCmd.OpenReport strReport, acDesign, , , , acHidden

because the arguments are wrong for OpenReport -- they are only for
OpenForm. I don't remember if that error was in my original code, or
if you introduced it.

In any event, even if one gets those functions to work propertly
(which you can), it won't work in a query. That's why I created a
completely different method for accomplishing the task.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[NickName]

Hi David,

Thank you for all your help.
So, what's the correct arguments for reports, or how to correct the
following line?
DoCmd.OpenReport strReport, acDesign, , , , acHidden

Most preferably you could help me with the above.

I've taken care of the query problem. And I've also tried your other
solution, however, I got error, (I saved them to a module, not sure if
that's the right way to go)

Don

[David W. Fenton]

"NickName" <da****@rock.com> wrote in
news:11**********************@g44g2000cwa.googlegr oups.com:

So, what's the correct arguments for reports, or how to correct
the following line?
DoCmd.OpenReport strReport, acDesign, , , , acHidden
Check out the help file, or use Intellisense in the Debug window.
Most preferably you could help me with the above.

I've taken care of the query problem. And I've also tried your
other solution, however, I got error, (I saved them to a module,
not sure if that's the right way to go)

Yes, of course, that's the only way to do it.

The only thing I can think of that would produce an error is if you
failed to create the table first.

What error did it return?

Try turning off the error handler in it (by commenting out the On
Error GoTo errHandler, which you do by adding an apostrophe at the
beginning of that line), then you'll see exactly which line is
producing the error.

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

[NickName]

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.OpenReport strReport, acViewNormal
' try the following (not to include the acViewNormal arg)
' DoCmd.OpenReport strReport
strTemp = Reports(strReport).RecordSource
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.AccessError (2486)

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

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

[David W. Fenton]

"NickName" <da****@rock.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.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.OpenReport strReport, acViewNormal
' try the following (not to include the acViewNormal arg)
' DoCmd.OpenReport strReport
The next line will fail because the OpenReport command is commented
out.
strTemp = Reports(strReport).RecordSource
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.AccessError (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.OpenReport strReport, acViewNormal
' try the following (not to include the acViewNormal arg)
' DoCmd.OpenReport strReport
strTemp = Reports(strReport).RecordSourc*e
DoCmd.Close acReport, strReport, acSaveNo
ReturnSQLReport = strTemp

In actually, I tried the following code to no avail
DoCmd.OpenReport strReport
strTemp = Reports(strReport).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
?PopulateMyObjects() Enter, got compiling error,
then tried, varation of ?PopulateMyObjects('reports'), not effective.

Thanks.

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

?PopulateMyObjects()

[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.com> wrote in
news:11*********************@g14g2000cwa.googlegro ups.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.com> wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:

OK.

1. Let me explain, the following (prevously posted) code snip did
not convey what I was trying to say.
DoCmd.OpenReport strReport, acViewNormal
' try the following (not to include the acViewNormal arg)
' DoCmd.OpenReport strReport
strTemp = Reports(strReport).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.OpenReport strReport
strTemp = Reports(strReport).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
?PopulateMyObjects() Enter, got compiling error,
then tried, varation of ?PopulateMyObjects('reports'), not
effective.
Well, it's not a function -- it's a subroutine. So you call it in
the Debug window as:

PopulateMyObjects

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

?PopulateMyObjects()

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.