Re: Oracle Passthrough Query Problem

From: Jonathan Argue <sovinfo_at_access.victoria.bc.ca>
Date: 1995/08/28
Message-ID: <41tjlm$ftr_at_holly.softwords.bc.ca>#1/1

jbarrios_at_tp.dsccc.com (Jorge Barrios) wrote:
>I am using MS Access to connect to Oracle7 using ODBC. When I try to run a Passthrough SQL Select statement that contains the variable & in a BETWEEN operator (to prompt the user to enter two values) Oracle returns the error message "ORA-01008 not all variables bound."
>
>If I remove the & variables and tnter the values in the BETWEEN operator, the query runs fine.
>
>I would like to have the users to be able to be prompted to enter the respective values. Does anyone knows a solution to this & variable problem?
>
>Your help will be greatly appreciated.
>

We have done this type of thing before on our own MS-Access application. Here are the steps we used to accomplish a "user defined" query:

1. Create a querydef of type 'passthru'
2. use a form of some type to get user input for the scope of your select.
3. Build your SQL string and substitute your user parameters. For example:

    SQLString = "SELECT * FROM emp WHERE empid = " &EmpID&

                                                                                             ^^^^ this is an access variable
                                                                                                      that you fill out in your form.

4) run the query.

For more information, see the MS-Access online help on QueryDefs/ODBC/Passthru queries. This type of stuff has to be done through code...you can't use the Query builder.

Hope this helps.

Jonathan.
Sovereign Information Systems
Victoria, Canada.

---

Jonathan S. Argue                     Internet: sovinfo_at_access.victoria.bc.ca

Sovereign Information Systems Inc. (after Aug 25/95 - jargue_at_sovinfo.com) Victoria, B.C. Canada Received on Mon Aug 28 1995 - 00:00:00 CEST