Method of Treating Views and Stored Procedures the same?

[pietlinden]

I am working on a database where users routinely run a series of
queries and copy and paste the results into Word. (Think of it sort
of like a report, but they can edit it... and the editing part is
critical, so I can't do it in a report.)

In the 2002 Developer's Handbook Desktop edition, I have the MultiPik
listbox thing working fine. (Even with a collection of queries).

Herein lies the rub, though... DAO would be *perfect* if I could use
the GetString method (but since it's an ADO method, I can't rightly do
that!), because I'm just converting the recordset into a delimited
list and then stuffing it into Word and converting it to a table.

Because ADO is funky about what it considers a Views and Stored
Procedures, how would I make the code work regardless (as if I were
dealing with a "catch-all" kind of QueryDef object)? I can get the
record-returning queries in DAO by using...

If CurrentDB.QueryDefs(strQueryName).Type = dbQSelect Or
CurrentDB.QueryDefs(strQueryName).Type = dbQCrosstab Then...

'--- Do something with the query

End If

Is there a way in ADO to treat the two the same way? Or are there any
other options? Otherwise, I get stuck with ... looking in the wrong
collection (Views/Sprocs) or whatever...
and that's the part I want to be transparent.

Thanks!
Pieter

 

[pietlinden]

Run the series of queries and use a make table query to insert the results in
a temp db.  Open the db file for them, then open the table.  They canedit,
sort, filter, create new queries, whatever ... in Access.  No fixed report to
edit and no need for Word.

If the results are standardized in structure you can create a form in a
template db file.  Copy the template as a temp db when needed.  Open that for
the user once the results are inserted into the table.

Chris

Their end goal is to create a report in Word with all the summary data
from their database (the results of the queries they have chosen), so
they need the stuff in Word. I suppose I could create a temporary
table out of the Crosstabs and send that as a table-type recordset
through ADO... I suppose...

I'll try a few things and see what works...

THanks for the feedback

Pieter

 

[pietlinden]

I've had a few customers who used to put their data inWordso they could
edit it too.  Once they saw how it was done in Access they slapped their
foreheads and said "Why didn't we think of that?"

Your customers might do the same thing if you show them a better, simplerway.

Chris

the problem is that they create a word document with a table from the
data, and then add comments in a column, so it's not that simple. I
got the crosstab problem solved by cheating and reverting to DAO,
which recognizes query types (dbQCrosstab, dbQSelect), which works
fine. I just write the crosstab queries to a temporary table (the
structure is always the same, so I just delete the contents). I can
get the data into the word table cell, but for some reason, I can't
figure out how to select the contents of the cell and turn it into a
table.

Is there a better way? The problem with an Access report is that it's
not editable.... I suppose I could grab the contents of a few custom
properties, stuff them in the report and then have like 10 subreports,
but that sounded like too much of a hassle. If I use the two
listboxes trick from the book (base the contents on a collection -- in
my case of record-returning queries), it works a champ... the only
problem I am having is converting the passed data into a (nested)
table.

 

[RoyVidar]

I am working on a database where users routinely run a series of
queries and copy and paste the results into Word. (Think of it sort
of like a report, but they can edit it... and the editing part is
critical, so I can't do it in a report.)

In the 2002 Developer's Handbook Desktop edition, I have the MultiPik
listbox thing working fine. (Even with a collection of queries).

Herein lies the rub, though... DAO would be *perfect* if I could use
the GetString method (but since it's an ADO method, I can't rightly
do that!), because I'm just converting the recordset into a delimited
list and then stuffing it into Word and converting it to a table.

Because ADO is funky about what it considers a Views and Stored
Procedures, how would I make the code work regardless (as if I were
dealing with a "catch-all" kind of QueryDef object)? I can get the
record-returning queries in DAO by using...

If CurrentDB.QueryDefs(strQueryName).Type = dbQSelect Or
CurrentDB.QueryDefs(strQueryName).Type = dbQCrosstab Then...

'--- Do something with the query

End If

Is there a way in ADO to treat the two the same way? Or are there
any other options? Otherwise, I get stuck with ... looking in the
wrong collection (Views/Sprocs) or whatever...
and that's the part I want to be transparent.

Thanks!
Pieter

I probably don't understand what you are asking, since opening
ADO recordsets based on anything I encounter is pretty stright
forward regardless of whether it's contained in the procedures
collection (queries with parameters, action queries, unions,
crosstabs...) or the views collection (select queries without
parameters).

qryTheSelect, qryTheUnion, qryTheCrosstab can all be opened,
and printed through the .GetString method of an ADO recordset
using something like the following

?CurrentProject.Connection.Execute("Query",,adCmdStoredProc).GetString

Just as I said in your previous thread called "Processing Crosstabs
in ADO", to which you haven't replied.

adCmdStoredProc as CommandTypeEnum, refers to stored query regardless
of whether the stored query can be found in the views or procedures
collection.

Am I just not understanding the problem? If you wish a "getstring"
method for DAO, it should be simple rolling your own, though string
concatenation is a bit demanding on recourses as number of records
increases.

Function MyGetString(ByRef rs As DAO.Recordset, _
Optional ByVal ColumnDelimiter As String = vbTab, _
Optional ByVal RowDelimiter As String = vbCrLf) As String

Dim s As String
Dim idx As Long
Dim numfields As Long

With rs

numfields = rs.Fields.Count
' Field names?
' For idx = 0 To numfields - 1
' s = s & .Fields(idx).Name & ColumnDelimiter
' Next idx
' s = s & RowDelimiter

Do While Not .EOF
For idx = 0 To numfields - 1
s = s & .Fields(idx).Value & ColumnDelimiter
Next idx
s = s & RowDelimiter
.MoveNext
Loop
End With
MyGetString = s

End Function

Or you could just mix a bit, use your DAO stuff to identify the
queries you wish to deal with, then open them through ADO.

If you have specific problems, I suggest you post the specifics in
a way that make us able to reproduce them (perhaps with errormessages
etc).