Exporting the results of a SQL statement made on a form

[Scott]

Hello. I have a form that dynamically creates a SQL
statement. The SQL is compiled into a hidden text box
object. That object is referenced when opening other
reports, which can then set their recordsource to the SQL
statement. The SQL statement would normally create a
crosstab query.

I would like to export the results of that SQl statement
to Excel so that the end user can play with the data from
there. I have had a hard time coming up with a way to
export the "RESULTS" of the statement. Some of the issues
are because it's a crosstab rather than a select query.
Any thoughts as to how I can "grab" the recordset of the
SQl statement and throw it into Excel?

 

[Glen Appleton]

Hi Scott,

You can try something like this... First, create an empty query in Access
which can be used as a reusable temp query. For the sake of demonstration,
I named my "ctqTemp". Then, use the example code below:

--- Begin Code ---
Dim qryTemp As QueryDef
Dim strSQL As String

Set qryTemp = CurrentDb.QueryDefs("ctqTemp")
strSQL = "your sql statement goes here..."
qryTemp.SQL = strSQL
DoCmd.OutputTo acOutputQuery, "ctqTemp", acFormatXLS, "CrossTab.xls",
True
--- End Code ---

The qryTemp object is assigned to the temp query you created, and then the
SQL property of the object is updated. You can then use the OutputTo method
of the DoCmd object to output the results much the same way you would by
choosing the "Export to Excel" button from the toolbar in Access.

Hope this helps,
- Glen

 

[Glen Appleton]

Oops, I almost forgot. Make sure to release your objects so that memory can
be reused. Add this line to the end of your routine:

Set qryTemp = Nothing