locked
Pass-Through Action Query RRS feed

  • Question

  • Pass-Through always has issues i can't figure out. Now I'm trying to pass stored procedure in SQL Server by calling procedure from Access 2010 VBA as:
    Call RunPassThroughACTION("EXEC acc_UpdateUserName")

    i'm getting error '3146, ODBC --Call failed' when turning .ReturnRecords = False, and error '3065, Cannot execute a select Query' when .ReturnRecords = True. Procedure is an UPDATE procedure that fills the full name column by concatenating firstname and lastname.

    What am i doing wrong here please??

    Pasting the Pass-Through function below:

    Thank you,
    K

    Function RunPassThroughACTION(strSQL As String)

        Dim qdfPassThrough As QueryDef, MyDatabase As Database
        Dim strConnect As String

        If Not IsNull(CurrentDb.QueryDefs("qrySQLPassA").Sql) Then CurrentDb.QueryDefs.Delete "qrySQLPassA"

        Set MyDatabase = CurrentDb()

        Set qdfPassThrough = MyDatabase.CreateQueryDef("qrySQLPassA")

        strConnect = "Driver=SQL Server;Server=" & TempVars("ServerName").Value & ";Database=PCMS_T1;Trusted_Connection=Yes;"

        With qdfPassThrough
            'Debug.Print .Name
            .Connect = "ODBC;" & strConnect
            .Sql = strSQL
            .ReturnsRecords = False
            .Execute
            .Close
        End With

    End Function

                       
    Friday, February 3, 2017 12:35 PM

Answers

  • Well, got the issue. It was permission. I instead make a query, and set it to PASS THRU. It popped up with correct message.
    Thanks for the suggestion though. I didn't know about Errors(0)description thing. Learned something. Now I need to find out how to take benefit from it )
    • Edited by KhurramKZ Friday, February 3, 2017 2:02 PM
    • Proposed as answer by Chenchen Li Monday, February 6, 2017 1:56 AM
    • Marked as answer by KhurramKZ Monday, February 6, 2017 11:01 AM
    Friday, February 3, 2017 1:59 PM

All replies

  • Since the stored procedure does not return records, setting .RetUrnsRecords = False is correct.

    To understand the true reason for the error, you must look at the first error that is raised:

      Debug.Print Errors(0).Description

    (Google for "VBA Errors Collection" for more on this.)

    I suspect that you should pass parameters to the procedure, perhaps something like this:

      Call RunPassThroughACTION("EXEC acc_UpdateUserName " & UserID & ", N'" & NewName & "'" )

    Matthias Kläy, Kläy Computing AG


    • Edited by mklaey Friday, February 3, 2017 1:47 PM
    Friday, February 3, 2017 1:47 PM
  • Well, got the issue. It was permission. I instead make a query, and set it to PASS THRU. It popped up with correct message.
    Thanks for the suggestion though. I didn't know about Errors(0)description thing. Learned something. Now I need to find out how to take benefit from it )
    • Edited by KhurramKZ Friday, February 3, 2017 2:02 PM
    • Proposed as answer by Chenchen Li Monday, February 6, 2017 1:56 AM
    • Marked as answer by KhurramKZ Monday, February 6, 2017 11:01 AM
    Friday, February 3, 2017 1:59 PM
  • Hi,

    It seems that your issue has been resolved, I suggest you mark helpful post or your post as answer to close the thread.

    Thanks for your understanding.

    Regards,

    Celeste 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 6, 2017 1:56 AM