Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling SQL Stored Procedures (Access 2000)

    Hi All,

    I'm trying to work out how to call a Stored Procedure in a Sybase database.

    I've set up a pass-through query using the ODBC connection and calling the stored procedure, and this works. BUT how do I pass a variable into the query?

    eg I have a form where someone enters an account number
    The stored procedure should then be run using the account number entered on the form to return other details.

    I cant work out the code to do this as it seems that when you use the 'docmd.OpenStoredProcedure' command or the 'docmd.OpenQuery' command, there is no option for any variables to be passed in - can anyone help?

    Thanks
    JB

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Calling SQL Stored Procedures (Access 2000)

    Unfortunately, there isn't a method for passing a variable directly using a pass-through query. So the trick is to create a querydef and actually save the query before you execute the pass-through query. That of course requires that you execute the query from VBA behind your form, and that you understand the object model as it pertains to queries. Post back if you need further guidance.
    Wendell

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling SQL Stored Procedures (Access 2000)

    Hi Wendell,

    can you please give me some more information?

    From what I've just been reading of QueryDef's - they are a collection that allows you to return the results of a query into a recordset so that you can loop through the results... how does this help me passing a variable into the query???

    Thanks
    JB

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Calling SQL Stored Procedures (Access 2000)

    Well, you are right in one sense - QueryDefs are often used that way. But a QueryDef is really a description of a stored query, or a query that is about to be stored. So you can actually create a SQL string and assign it to a QueryDef that is your Pass-Through query, and in that fashion have a "parameterized" query which is then executed. Note that the SQL string must be in the format of the backend - on most cases similar to ANIS-92 SQL. Take a look at <!mskb=232493>Microsoft Knowledge Base Article 232493<!/mskb> for further details and sample code for doing what I attempted to describe.
    Wendell

  5. #5
    New Lounger
    Join Date
    Jan 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling SQL Stored Procedures (Access 2000)

    Excellent! Thankyou very much!!

    Cheers
    JB

Posting Permissions

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