Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    pass through query (access 97)

    I'm trying to pass a value to the parameter of a pass through query

    The pass through query looks like
    EXCE StoredProcedure Parameter1

    it works when I type the value of Parameter1 into the SQL screen
    but I want it to be able to retrieve this value from a form

    I've tried EXCE StoredProcedure [Forms]![FormName]![FieldName]
    but get an error that there is "incorrect systax near 'forms' "

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

    Re: pass through query (access 97)

    Unfortunately pass-through queries are exactly that - they simply pass the text string to the ODBC data source unmodified. In order to put the parameter in using a form, you will need to create the query on the fly and then execute it. To do that you need to work with the QueryDef collection - post back if you need more details.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pass through query (access 97)

    Hi Wendell--

    Just curious. I know you can do this with SQL server, but you mentioned ODBC...Does this mean you can do this with any ODBC data source? Can you suggest some reading material for sources other than SQL server?

    TIA!

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

    Re: pass through query (access 97)

    Yes, as I understand you can do it with any ODBC source that understands some sort of SQL. I don't really know of any documentation that deals with other sorts of databases such as Oracle or DB2 - you basically need to know the syntax used with that specific database, and whether and how parameters and results are passed back and forth. Sounds like the topic might be a good one to do a tutorial on.
    Wendell

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pass through query (access 97)

    Thank you Wendell for your reply,

    QueryDef is something that I don't know anything about, but thanks for showing me what I have to use so I can read up about it next week.
    Any help on the topic would be great, a tutorial would fantastic.

    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: pass through query (access 97)

    Unfortunately, the tutorial will take a while. In the meantime, you might find KB Article 131534 - ACC: How to Simulate Parameters in an SQL Pass-Through Query helpful. I'll put the tutorial in the queue with all the other topics we've identified for our website.
    Wendell

Posting Permissions

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