CrossTab Stored Procedure

[GammelNok]

I have a Stored procedure, which produces a fine Crosstab result when Doubleclicking on the procedure, and supplying the required parameters.
However, when running from a button it runs, and then closes down without showing the result

Set conn = CurrentProject.Connection
conn.Execute ("sp_CrossTab " & "'qtabTIMEData01'," _
& "'WeekText', " _
& "'SortName', " _
& "'WeekNum'&quot

I have tried both with Paranthesis and without

Any idea how to view the resultset

Regards

Hans

 

[cmmrfrds]

I usually use the Command object for executing stored procedures. Here is an example. Note that unless you are the database owner then dbo. is necessary in front of the name - it will always work that way.

dbo.sp_CrossTab


Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter, param4 As Parameter
begDate = #10/1/2001#
endDate = #10/31/2001#

' Connect
Set cnn = CurrentProject.Connection

Set cmd.ActiveConnection = cnn
Set param1 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param1
param1.Value = begDate
Set param2 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = endDate
Set param3 = cmd.CreateParameter("Input", adSmallInt, adParamInput)
cmd.Parameters.Append param3
param3.Value = 3
Set param4 = cmd.CreateParameter("Input", adSmallInt, adParamInput)
cmd.Parameters.Append param4
param4.Value = 3

' Set up a command object for the stored procedure.
cmd.CommandText = "dbo.sp_employeeRevenueProgress"
cmd.CommandType = adCmdStoredProc

' Execute command to run stored procedure
''cmd.Execute

Set rst = cmd.Execute
Debug.Print "first field = "; rst(0)

Set cnn = Nothing


Note:
If you are using an adVarChar data type then a size is necessary in the parm.
Set param4 = cmd.CreateParameter("Input", adVarChar, adParamInput, 30) '- i.e. 30 bytes of characters