Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    2

    Question Unanswered: How do you pass parameters to a stored procedure when executed from an Access macro?

    While converting an existing Access 2000 application to ADP using a SQL 2000 back end, I came across multiple macros that were attempting to execute queries. The queries have been converted to stored procedures in the SQL database but when you change the macro to execute the stored procedure, there is no place to enter the required parameters (i.e. Input Parameters). What's the best way to resolve this without recoding all the macro logic?
    Last edited by rjulia; 07-23-02 at 14:43.

  2. #2
    Join Date
    Jul 2002
    Posts
    9
    Not 100% but can't you put the variables in in comma seperated form after the sp-name?

    e.g

    SP_YourSP, var1, var2, var3



    Best approach is to remove the macros and recode with VBA which will give you better control and error handling etc.

    Bit more time consuming initially but will save you time in the long run probably

    -Aav

  3. #3
    Join Date
    Jul 2002
    Posts
    2

    Macro variables for stored procedure calls

    Originally posted by Aavarn
    Not 100% but can't you put the variables in in comma seperated form after the sp-name?
    e.g
    SP_YourSP, var1, var2, var3
    -Aav
    Unfortunately, Access 2K will not allow arguments on the same line as the procedure name in a macro. It replies with an error that it "can't find the object". I've tried different variations on the syntax with no success.

    This seems like an Access ADP limitation which will probably be corrected in the next release or service pack. I can replace the macros with VBA code but that may take too long given the number of macros involved (approx. 112) and my impending deadlines.

    Rick

Posting Permissions

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