Re: How to use OutputTo with named query?

DoCmd.OutputTo objecttype[, objectname][, outputformat][, OUTPUTFILE]

I use a file save dialog to allow the user to change the name
and location of OUTPUTFILE.


But I already use file dialogs anyway. And I normally use
a create table query to export queries to text or XLS, rather
than OutPutTo.

My main objection to OutputTo is that it requires the query
and tables to be in CurrentDB, and most of my work is done
in CodeDB.

(david)


"deko" <deko@xxxxxxxxxx> wrote in message
news:C42dnaFTgZvTRSPeRVn-uQ@xxxxxxxxxxxxxx
> When using OutputTo with a query, the 'File name' window in the 'Output
> To' dialog gets populated with the name of the query by default. This
> makes the exported file self-describing if the query is named on the fly.
>
> The benefit is consistent file naming, and users don't have to enter a
> file name when exporting.
>
> The problem is the query has to be renamed every time, and I don't know
> how to find it in the QueryDefs collection other than by name. It seems
> unreliable to reference by index number.
>
> For example:
>
> Private Sub ExportQuery
> Dim strQryName As String
> strQryName = "based on various criteria"
> Call GetQry(strQryName)
> DoCmd.OutputTo acOutputQuery, strQryName, , , True
> DoEvents
> DoCmd.DeleteObject acQuery, strQryName
> End Sub
>
> Private Function GetQry(strQryName As String)
> Dim strSql As String
> Dim qdf As QueryDef
> Dim db As DAO.Database
> strSql = "big long ugly dynamically-built sql statement"
> Set db = CurrentDb
> Set qdf = db.CreateQueryDef(strQryName, strSql)
> Set qdf = Nothing
> Set db = Nothing
> End Sub
>
> There are several subform datasheets that get filtered in any number of
> ways that use this routine. I'm trying to find a way to avoid having to
> create and delete the query every time.
>
> How can I use the same QueryDef and just rename it each time? If I don't
> know the name of the query, how can I reference it in the QueryDefs
> collection?
>
> Thanks in advance.
>


.

Loading