[AccessD] Sql 7/Access 2K - Stored Procedure Problem ...

I'm trying to run the code below in a form sub after inputting the parameter values in text boxes.
I keep getting the error message:  "Parameter object improperly defined, Inconsistent or Incomplete info provided."

1.  I can double click on the SP in an Access 2K ADP and it works
2.. The docmd action works
3.  The command object doesn't work.

I've fought this beast for hours.  Any help greatly appreciated.

Thanks - Tom


Here's my code ------------------------------
Dim cmd As ADODB.Command

If 1 = 2 Then
   DoCmd.OpenStoredProcedure "tsp_Test3_Lev1_sp", acViewNormal, acReadOnly
Else
   Set cmd = New ADODB.Command
    With cmd
        .CommandText = "tsp_Test3_Lev1_sp"
        .CommandType = adCmdStoredProc
        .ActiveConnection = CurrentProject.Connection

        .Parameters.Append .CreateParameter("@ViewTable", adVarChar, adParamInput)
        .Parameters.Append .CreateParameter("@Where", adVarChar, adParamInput)
        .Parameters.Append .CreateParameter("@Order", adVarChar, adParamInput)

        .Parameters("@ViewTable") = Trim(Me.scnViewTableName)
        .Parameters("@Where") = Trim(Me.scnWhere)
        .Parameters("@Order") = Trim(Me.scnOrder)

        .Execute
        .ActiveConnection = Nothing
    End With
    Set cmd = Nothing
End If

'--------------------------------------- here's the stored procedure
CREATE Procedure tsp_Test3_Lev1_sp
@ViewTable  nvarchar(200),
@Where  nvarchar(200),
@Order  nvarchar(200)

output As

DECLARE @SQLString NVARCHAR(200)

-- -----------------------------------------------------------

set @SqlString = N'Select * from ' + @ViewTable + N' ' + @Where + ' ' + @Order
--  test 1 -----------------------------------------
if 1 = 2
if  @Where    > ''
   if @Order > ''
      SET @SQLString = N'SELECT * from W01_03a1_CostOfRevenue_Detail_vw ' +  @Where + N'  ' + @Order
   else
      SET @SQLString = N'SELECT * from W01_03a1_CostOfRevenue_Detail_vw  ' +  @Where + N'  order by summary_code,  seg1_code,
seg3_code Desc,  seg4_code'
   -- endif order > '' --------------------------------

else  -- sum cd = '' --------------------------
   SET @SQLString = N'SELECT * from W01_03a1_CostOfRevenue_Detail_vw  ' + N' order by  summary_code Desc, seg1_code,  seg2_code,
seg3_code,  seg4_code'
--  endif  @Where = ''  --------------------------

Exec sp_ExecuteSQL @SQLString

return
'------------------------------------------------------------------------







----- Original Message -----
From: "Mike and Doris Manning" <mikedorism at ntelos.net>
To: <AccessD at databaseadvisors.com>
Sent: Tuesday, January 21, 2003 8:58 AM
Subject: RE: [AccessD] Calling A SQL Server Stored Procedure from VB6


You need to work with an ADO Connection object and an ADO Command object.
Here is a sample of how I call a stored procedure from Access using ADO 2.7:

    Set cmd = New ADODB.Command
    With cmd
        .CommandType = adCmdStoredProc
        .CommandText = "ap_TM_TaskRead"
        .Parameters.Append .CreateParameter("@TaskID", adInteger,
adParamInput)
        .Parameters.Append .CreateParameter("@TaskRead", adBoolean,
adParamInput)
        .Parameters("@TaskID") = oTM.TaskID
        .Parameters("@TaskRead") = True
        .ActiveConnection = CurrentProject.Connection
        .Execute
        .ActiveConnection = Nothing
    End With
    Set cmd = Nothing

Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com


-----Original Message-----
From: AccessD-owner at databaseadvisors.com
[mailto:AccessD-owner at databaseadvisors.com] On Behalf Of
paul.hartland at fsmail.net
Sent: Tuesday, January 21, 2003 09:37 AM
To: accessd at databaseadvisors.com; dba-VB at databaseadvisors.com
Subject: [AccessD] Calling A SQL Server Stored Procedure from VB6


To all,

Please forgive me not cross-posting this to the SQL Group,but I keep getting
rejected from it and can't seem to log onto www.databaseadvisors.com, to
re-enlist.

I have a (INSERT) Stored Procedure in SQL Server 7.0 which requires about 5
or 6 parameters to be passed to it.  As I have never called a Stored
Procedure before from Visual Basic 6.0, I wonder if someone had some sample
code on how to Call and pass the parameters to the Stored Procedure....

Thanks in advance.....

Paul Hartland

__________________________________________________________________________
Freeserve AnyTime - Go online whenever you want for just £6.99 a month for
your first 3 months, that's HALF PRICE! And then it's just £13.99 a month
after that.

For more information visit http://www.freeserve.com/time/ or call free on
0800 970 8890


This list sponsored by Database Advisors Inc., a worldwide association of
database developers. Visit http://www.DatabaseAdvisors.com, the database
developers' list portal and support site.

This list sponsored by Database Advisors Inc., a worldwide association of database developers.
Visit http://www.DatabaseAdvisors.com, the database developers' list portal and support site.