none
Button click + code + run time error 3265 + Item not found in this collection RRS feed

  • Question

  • Hi,

    What is wrong with this code

    Dim cdb As DAO.Database, qdf As DAO.QueryDef
        Set cdb = CurrentDb
        Set qdf = cdb.CreateQueryDef("")
       
        ' get .Connect property from existing ODBC linked table
        qdf.Connect = cdb.TableDefs("ABC Transactions").Connect
        qdf.SQL = "EXEC uspRunStoredProcedure"
        qdf.Parameters(0) = "C:\abc\def\"
        qdf.Parameters(1) = "xxx.dtsx"
        qdf.Parameters(2) = "05/30/2017"
        qdf.Parameters(3) = "05/30/2017"
        qdf.Parameters(4) = "MANUAL"
        qdf.Parameters(5).Value = "C:\UP\"
        qdf.Parameters(5).Value = "TestAccess.txt"
        qdf.ReturnsRecords = False
        qdf.Execute dbFailOnError
        Set qdf = Nothing
        Set cdb = Nothing

    It errors when I click debug, yellow highlight sits on line

    qdf.Parameters(0) = "C:\abc\def\"

    what is wrong with specifying paramters.

    Thank You

    Wednesday, May 31, 2017 3:18 AM

Answers

  • If you want to call a stored procedure (presumably in SQL Server or another database server) you need to use a Passthrough query. There is plenty of information online on how to do that.

    Then your .SQL property would be set to the entire expression:
    "EXEC uspRunStoredProcedure 'C:\abc\def\', 'xxx.dtsx', '2017-05-30', etc."
    Just like how you would run it in SQL Server Management Studio.

    Note the use of T-SQL syntax.


    -Tom. Microsoft Access MVP

    • Proposed as answer by Chenchen LiModerator Wednesday, May 31, 2017 9:51 AM
    • Marked as answer by Spunny Thursday, June 1, 2017 1:59 PM
    Wednesday, May 31, 2017 3:52 AM

All replies

  • If you want to call a stored procedure (presumably in SQL Server or another database server) you need to use a Passthrough query. There is plenty of information online on how to do that.

    Then your .SQL property would be set to the entire expression:
    "EXEC uspRunStoredProcedure 'C:\abc\def\', 'xxx.dtsx', '2017-05-30', etc."
    Just like how you would run it in SQL Server Management Studio.

    Note the use of T-SQL syntax.


    -Tom. Microsoft Access MVP

    • Proposed as answer by Chenchen LiModerator Wednesday, May 31, 2017 9:51 AM
    • Marked as answer by Spunny Thursday, June 1, 2017 1:59 PM
    Wednesday, May 31, 2017 3:52 AM
  • Thank you Tom.

    I did as you suggested and created PassThrough query like this:

    create - > Query Design -> cancel show table window -> click on Pass Through - > Properties -> ODBC connection str: ODBC;DRIVER=SQL Server;SERVER=Server\Instance;Trusted_Connection=Yes;DATABASE=dbname

    In pass through query window, some text like exce sp1.

    I save this PassThrough query and coded like this in button click event:

    Dim qdf As QueryDef
       
        Set qdf = CurrentDb.QueryDefs("PassThroughTest")
        qdf.SQL = "EXEC uspRunStoredProcedure 'C:\abc\def\', 'xxx.dtsx', '05/30/2017, '05/30/2017', 'xxxxx', 'C:\UP\', 'TestAccess.txt'"  
       
        qdf.Execute   dbFailOnError  Thank You                                                                                                                                                    

    Wednesday, May 31, 2017 4:13 AM
  • Hello,

    It seems your issue has been resolved, i suggest you mark helpful post or your solution as answer to close this 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.

    Wednesday, May 31, 2017 9:53 AM
    Moderator
  • Sorry Celeste. It was not solved. I am still working on it.

    Thank You

    Wednesday, May 31, 2017 2:27 PM
  • Dim qdf As QueryDef
       
        Set qdf = CurrentDb.QueryDefs("PassThroughTest")
        qdf.SQL = "EXEC uspRunStoredProcedure 'C:\abc\def\', 'xxx.dtsx', '05/30/2017, '05/30/2017', 'xxxxx', 'C:\UP\', 'TestAccess.txt'"  
       
        qdf.Execute   dbFailOnError  Thank You                                                                                                                                                    

    Hello,

    Would you get any error when you use the code above? What is the current and expected result after you call the stored procedure?


    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.

    Thursday, June 1, 2017 8:56 AM
    Moderator
  • Hi,

    I solved the problem. This is how I did

    Created a PassThrough query with DSN for ODBC Connection string. I had issues when I created this query as DSN less. Then I added below code in button click event:

    Set qdf = CurrentDb.QueryDefs("PassThroughTest")
    qdf.SQL = "EXEC uspRunStoredProcedure" & " " & "'C:\abc\def\'" & " , " & "'xxx.dtsx'" & " , " & "'05/31/2017'" & ", " & "'05/31/2017'" & ", " & "'xxxxx'" & " ," & "'C:\UP\'" & " ," & "'TestAccess.txt'"
        qdf.ReturnsRecords = False
        qdf.Execute dbFailOnError

    Thank You

    Thursday, June 1, 2017 1:58 PM
  • Actually stored procedure return single value which I should use to let access form user know that process ran successfully or not based on value. I am not sure if it can be part of this issue or open a new ticket.

    Thank You

    Thursday, June 1, 2017 2:34 PM
  • Actually stored procedure return single value which I should use to let access form user know that process ran successfully or not based on value. I am not sure if it can be part of this issue or open a new ticket.

    Thank You

    Thursday, June 1, 2017 2:35 PM