Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2002
    Location
    London
    Posts
    10

    Angry Unanswered: OutputTo and Stored Procedure problem

    Hi, I'm new to forums in general so be gentle,

    I'd like to know if there's anyway to pass a parameter to a SQL stored Procedure within the DoCmd.OutputTo method (I'm doing this from an Access 2000 Project (*.adp file). It seems to only accept the object name itself (i.e. the stored procedure name) and not any input parameters. In fact when you run the code, you're prompted to enter the parameters directly, it's just that I want to pass the parameters programatically in the code.

    Any advice would be much appreciated

    P.S. I know how to pass parameters to a SQL Proc using ADO and how to get the results back into a ADO recordset using the command.execute method, but you can't pass a recordset to the DoCmd.OutputTo method in VBA.

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I assume you are saving the stored procedure in a named, pass-through query and using the name of that pass-through query in your OutputTo command?

    If so, then simply open up a QueryDef pointing to that pass-through query and modify it's .SQL property immediately before executing the OutpuTo command.

  3. #3
    Join Date
    Apr 2002
    Location
    London
    Posts
    10
    Thanks PracticalProgram

    Sorry but no, I'm not doing it that way.

    I'm connected to a SQL Database using an ADP file. This means that I'm referencing the Stored Proc directly, which is....

    /** this is the Stored Proc on SQL Server **/

    Create Procedure sp_MailingList
    (@UserID as int)
    As
    Select * from tblAddress
    Where AmmendedUserID=@UserID
    Return
    /************************************?/

    And it is called from this line of VBA code via an on_click event on a form. (there is lots more code in reality but this is the bit that matters)

    DoCmd.OutputTo OutputStoredProcedure, "sp_MailingList",acFormatXLS

    The trouble is as you can see there appears to be nowhere to pass the input parameter (@UserID) to. If you run it Access prompts you for the parameter.

    Cheers

    Dave

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I have not used ADP, so I can't help you if the problem is related to ADP.

    I don't think this will work, but have you tried this?

    If UserID is numeric:

    DoCmd.OutputTo OutputStoredProcedure, "sp_MailingList " & UserID,acFormatXLS

    If UserID is string:

    DoCmd.OutputTo OutputStoredProcedure, "sp_MailingList '" & UserID & "'",acFormatXLS

    If this does not work, can you use a pass-through query w/ADP? If so, then you can pass parameters.

  5. #5
    Join Date
    Apr 2002
    Location
    London
    Posts
    10

    Red face

    Thanks

    I tried this but unfortunately no joy, I got a "Error 2757, there was a problem accessing a property or method of the OLE object."

    Ce la vie... I guess I'll have to go down another route.

    Cheers

    Dave

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Can you use a pass-through query w/ADP?

  7. #7
    Join Date
    Apr 2002
    Location
    London
    Posts
    10

    Smile

    No you can't, but I can see what you're getting at.

    You don't need pass throughs as the client (Access 2000 ADP) is connected to the server through OLE DB, which means you can directly call/run stored procs, open views and generally mess with SQL Server objects etc. I'm going to have to pull my finger out and write some T-SQL.....

    It's a shame though as the DoCmd.OutputTo method is a great command to use for the lazy programmer who wants instant results like myself!

    Cheers for the input though mate

  8. #8
    Join Date
    Feb 2003
    Posts
    109

    Post RunSql

    are you sure you're not having a Timeout problem?

    I would also make sure that you cant do this by using the EXEC sql keyword-- becuase you need that in a bunch of other places in order to do this type of thing

    DoCmd.OutputTo OutputStoredProcedure, "sp_MailingList " & UserID,acFormatXLS

    vs

    DoCmd.OutputTo OutputStoredProcedure, "EXEC sp_MailingList " & UserID,acFormatXLS

    (and of course, if it is a character argument, you need to include the singlequote (CHR(39))

    in other words i would try this before giving up hope

    dim sqlString as string
    sqlString = "EXEC sp_MailnigList " & UserID
    Docmd.OutputTo outputStoredProcedure, strSql, acFormatXls


    '------------------------------------------

    I generally encapsulate my Docmd.Runsql method with a new function 'runSql'

    that does things like security, logging, etc--

    im just having a problem with commandTimeout myself, and it is driving me crazy.

    '----------------------
    Public Sub RunSQL(sql As String)
    On Error GoTo errhandler

    Dim strSql As String

    Dim ReturnErrDescription


    'Use the above connection for a command object
    Dim cmd As New ADODB.Command
    Dim prp As New ADODB.Parameter
    Dim prpReturn As New ADODB.Parameter
    Dim rstGetError As New ADODB.Recordset
    Dim rstGetErrorReturn As New ADODB.Recordset

    Dim cmdSql As String

    cmdSql = "'" & Replace(sql, Chr(39), Chr(39) & Chr(39)) & "'"

    cmd.CommandType = adCmdUnknown
    cmd.CommandText = "dbo.uspRunSql"
    cmd.ActiveConnection = CurrentProject.BaseConnectionString
    cmd.CommandTimeout = 20000 'allow 20000 seconds for command to operate
    'create and append the @strSql parameter
    Set prp = cmd.CreateParameter("@strSQL", adBSTR, adParamInput, 128)
    prp.Value = "'" & Replace(sql, Chr(39), Chr(39) & Chr(39)) & "'"
    cmd.Parameters.Append prp
    'handle the return value from this
    Set prpReturn = cmd.CreateParameter("@ERROR", adInteger, adParamReturnValue, Null)
    cmd.Parameters.Append prpReturn

    Set rstGetErrorReturn = cmd.Execute

    rstGetError.Open "Select @@ERROR", CurrentProject.Connection

    If cmd.Parameters("RETURN_VALUE") <> 0 Then
    MsgBox "SQL ERROR DETECTED (Return Parameter).", vbOKOnly
    Stop
    End If

    If rstGetErrorReturn.Fields(0).Value <> 0 Then
    MsgBox "SQL ERROR DETECTED (Recordset From Command Object).", vbOKOnly
    Stop
    End If

    If rstGetError.Fields(0).Value <> 0 Then
    MsgBox "SQL ERROR DETECTED (@@ERROR).", vbOKOnly
    Stop
    End If

    cleanexit:
    Exit Sub
    errhandler:
    Select Case err.Number
    Case Else
    MsgBox err.Number & " - " & err.Description & vbCrLf & "strSql: " & strSql, vbOKOnly
    Resume Next
    Resume
    Resume cleanexit
    End Select
    End Sub
    '-------------------------------------

    Alter Procedure uspRunSql

    (
    @strSQL nVarChar(4000)
    )

    As

    DECLARE @err int
    SELECT @Err = 0


    EXEC sp_executeSql @strSql

    Insert Into tblSQLLOG
    (sql)
    Values
    (@strSQL)

    IF @@error <> 0
    BEGIN
    SELECT @err = 1
    GOTO Err
    END


    COMMIT TRAN
    RETURN @err

    Err:
    ROLLBACK TRAN
    RETURN @err


    /* set nocount on */
    return
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  9. #9
    Join Date
    Feb 2003
    Posts
    109

    re: diagnosis = Timeout

    like when you Docmd.RunSql certain SQL Stored Procs, and these take a long time to run, then you have to make a custom command object to fire these things--

    thats what i forgot to say...

    since i am working for a large company, i also need to do a better job of security and logging-- so i encapsulate RunSql (to get around this timeout error, and to enable logging and other security checks)
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •