TransferSpreadsheet function workaround using Dynamic Query 1

[GarHeard]

I built a dynamic SQL statement in a string named strSQLFS and passed it along as a parameterized stored procedure as follows:

I wanted to perform a Transferspreadsheet to output the result of this stored procedure to an Excel file. However I believe you cannot pass a dynamic SQL statement to the Transferspreadsheet function. Is there a way I could pass the result of this stored procedure to the Transferspreasheet function if I want to export the result to an Excel spreadsheet.

Is there a way to convert the result of the dynamic query to a table since Transferspreadsheet can output a table to an Excel file.

Or else could I somehow convert the result of the strored procedure, a recordset to a table and then use the Transferspreadsheet with the newly created table ?
-----------------------------------------------------------
With com
Set .ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "dbo.procUDFl"
.Parameters.Append .CreateParameter("@prmSQL", adVarChar, adParamInput, 8000, strSQLFS)
.Parameters.Append .CreateParameter("@RptYearF", adInteger, adParamInput, 4, intYearSP)
Set rstQueryFS = .Execute
End With
-----------------------------------------------------------
CREATE PROCEDURE dbo.procUDFl

@prmSQL varchar (8000),
@RptYearF int

AS

SET @prmSQL = REPLACE(@prmSQL,'intYearSP',CAST(@RptYearF AS CHAR(4)))

EXEC( @prmSQL)
GO

 

[rorrosh]

I use this function to export the report to HTML formate you can use it excel format it will work, i'm sure It will.
Good Luck


Private Sub cmdExportCustomers_Click()
Dim strLocalPath As String

strLocalPath = CurrentProject.Path & "\customers.xls"

DoCmd.OutputTo acOutputForm, "frmCustomers", acFormatHTML, strLocalPath

With Me.lblHyperlink
.Visible = True
.Caption = strLocalPath
.HyperlinkAddress = strLocalPath
End With

Me.lblPublished.Visible = True
Me.lblWarning.Visible = True
Me.cmdClose.Enabled = True


End Sub

 

[GarHeard]

I'm not sure how to set up the 2nd parameter in your function. I run a stored proc and once it has executed
I get a result set in the form of a record set.
I don't think I can simply place the recordset into the 2nd parameter. I think the 2nd parameter is looking for a table.

 

[GarHeard]

Do you know how I can output a dynamic sql string to an Exce file ?

I tried the following and got an err.number of 91 on the CopyFromRecordset Function

strSQLFS = "SELECT * FROM tblTest"

With com
Set .ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "dbo.procUDTest"
.Parameters.Append .CreateParameter("@prmSQL", adVarChar, adParamInput, 8000, strSQLFS)
Set rstQueryFS = .Execute
End With

objWS.Range("A1").CopyFromRecordset rstQueryFS
-----------------------------------------------------------
CREATE PROCEDURE dbo.procUDTest
@prmSQL varchar (8000)
AS
EXEC( @prmSQL)
GO
-----------------------------------------------------------
If I try:

DoCmd.TransferSpreadsheet acExport, 8, "strSQLFS", "C:\SPBRANCH1.XLS", True, ""

I get err.number = 7874

Thus, I beleive you can't use TransferSreadsheet and CopyFromRecordset with Dynamic SQL.

Which leads me to ask what other means is there to get the result set of a dynamic sql string into an Excel file ?

 

[RoyVidar]

Do you get a recordset?

What happens if you do a

[tt]debug.print rstQueryFS.getstring[/tt]

just after the .execute (hit ctrl+g to check)?

I think perhaps the 2000 version (Excel), may have some diffuculties, though I'm not 100% sure what - perhaps cursor location, cursor type/lock type? But first check whether you have a recordset or not.

As stated in faq181-2886 #14, please state the complete errormessage, else it is a bit difficult. For some errormessages, like the 7874, there might be a bit more additional information that may help assist (though in this case, I think you are right, you can't use anything but a stored query or table in the Transfer thingie).

Roy-Vidar