locked
VBA- Pass through query RRS feed

  • Question

  • Hi


    I am setting form recordsource via passthrough query

    PassThrough Query:
    EXEC sp_order'2009/11/16 00:00:00','66'

    Calling Pass Through Query  and setting parameters when form is opening:
    CurrentDb.QueryDefs("sp_Order").SQL = "EXEC sp_order" & "'" & Format(Me!DateSearch, "yyyy/mm/dd hh:mm:ss") & "','" & CUser() & "'"

    Stored Procedure in SQL:

    ALTER PROCEDURE [dbo].[sp_Order]
        @SDATE VARCHAR(200),
        @CUSER VARCHAR(10)
    AS
        DECLARE @STRING VARCHAR(8000)
           SET @STRING = [DBO].[fn_GetCondition_Test]('REPS','REP',@CUSER)
    EXEC ('SELECT    * FROM dbo.OBook WITH (NOLOCK) WHERE (dbo.fn_GetDate(SDate) = CONVERT(DATETIME, '''+ @SDATE +''', 102))AND REP IN (' + @STRING +')')


    Issue:
    All this works fine when I run it on my PC but run it on any other user can't open form and display error can't open handler

    _____________________________________________________________________

    Then i tried most usual approch to solve this issue 

    just for info! database is mdb  not adp

    Dim cmd As New ADODB.Command
    Dim prm As ADODB.Parameter
    Dim r As New ADODB.Recordset

    cmd.ActiveConnection = SQLcon
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "sp_Order"

    Set prm = cmd.CreateParameter("@SDATE", adDBTimeStamp, adParamInput)
    cmd.Parameters.Append prm
    cmd.Parameters("@SDATE").Value = Format(Me!DateSearch, "yyyy/mm/dd hh:mm:ss")

    Set prm = cmd.CreateParameter("@CUSER", adInteger, adParamInput)
    cmd.Parameters.Append prm
    cmd.Parameters("@CUSER").Value = CUser()
    Set r = cmd.Execute()
     
    Set Me.Recordset = r

    While Not r.EOF
    Debug.Print r!Rep & " , " & r!MID & " , " & r!TDate & " , " & r!Value & " , " & r!SDATE & " , " & r!ElecOrMach
    r.MoveNext
    Wend

    Issue but I can't set form record source to r although i can able to print value on debug screen

    Any idea or solution

    Thanks

    Ayaz




    ayaz
    • Moved by Jeff Shan Wednesday, November 18, 2009 2:09 AM vba question (From:Visual Basic General)
    Monday, November 16, 2009 9:37 AM

Answers

All replies