PASS THROUGH QUERY WITH ORACLE BACK END

Hello, having problems using the pass through query to pull data off of an
oracle back end. I have created an odbc user data source but I still get an
error retrieving data but I always get the odbc call failed error message. I
have all the appropriate rights to retrieve data. I can use Access SQL to
pull data but I get errors using Pass Through.

Can someone help me out on this?

Could you post the SQL for your query? Which ODBC driver are you using? Have
you tried running the same query in some other too, like SQL Plus?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"nique" wrote in message
...
Hello, having problems using the pass through query to pull data off of an
oracle back end. I have created an odbc user data source but I still get
an
error retrieving data but I always get the odbc call failed error message.
I
have all the appropriate rights to retrieve data. I can use Access SQL to
pull data but I get errors using Pass Through.

Can someone help me out on this?

1. Check your DSN set-up. In the configuration dialog, there is a "Test
Connection" command you can use to test to ensure the DSN is correctly set.

2. I don't use Oracle but there must be a way that you can run the SQL
String in Oracle (i.e. not via Access Pass-Through Query) to test the SQL
String and make sure the SQL String works correctly. I hope you are aware
that you need to use Oracle SQL syntax and not JET syntax.

3. Once 1 & 2 above work correctly, paste the tested SQL String into an
Access Pass-Through Query and set appropriate Connection String.

HTH
Van T. Dinh
MVP (Access)



"nique" wrote in message
...
Hello, having problems using the pass through query to pull data off of an
oracle back end. I have created an odbc user data source but I still get
an
error retrieving data but I always get the odbc call failed error message.
I
have all the appropriate rights to retrieve data. I can use Access SQL to
pull data but I get errors using Pass Through.

Can someone help me out on this?

Hi Lynn,

Thanks for your valuable response! I have not tried to run the query
with SQL Plus. I actually do not have that tool.

I am most familiar with MS Access and never had the oppurtunity to use the
pass through query.

The odbc driver I am using is the Microsoft ODBC Driver for ORACLE.

The query is complex that involves several tables.

The query runs using the MS Access native query builder, but runs for about
an hour.

I get the odbc error when using the pass through.

I use the same odbc set up I have for the pass through as the query in the
builder.

What could cause the error that doesn't come up through native Access SQL?

"Lynn Trapp" wrote:

Could you post the SQL for your query? Which ODBC driver are you using? Have
you tried running the same query in some other too, like SQL Plus?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"nique" wrote in message
...
Hello, having problems using the pass through query to pull data off of an
oracle back end. I have created an odbc user data source but I still get
an
error retrieving data but I always get the odbc call failed error message.
I
have all the appropriate rights to retrieve data. I can use Access SQL to
pull data but I get errors using Pass Through.

Can someone help me out on this?

Hi Van, thanks for your valuable response!

I don't see the "Test Connection" command you mentioned in the Microsoft
ODBC For Oracle setup in the ODBC Data Source Administrator property page.

Am I using the right source?

"Van T. Dinh" wrote:

1. Check your DSN set-up. In the configuration dialog, there is a "Test
Connection" command you can use to test to ensure the DSN is correctly set.

2. I don't use Oracle but there must be a way that you can run the SQL
String in Oracle (i.e. not via Access Pass-Through Query) to test the SQL
String and make sure the SQL String works correctly. I hope you are aware
that you need to use Oracle SQL syntax and not JET syntax.

3. Once 1 & 2 above work correctly, paste the tested SQL String into an
Access Pass-Through Query and set appropriate Connection String.

HTH
Van T. Dinh
MVP (Access)



"nique" wrote in message
...
Hello, having problems using the pass through query to pull data off of an
oracle back end. I have created an odbc user data source but I still get
an
error retrieving data but I always get the odbc call failed error message.
I
have all the appropriate rights to retrieve data. I can use Access SQL to
pull data but I get errors using Pass Through.

Can someone help me out on this?

Open the (Windows OS) ODBC Data Source Administrator. Select your DSN then
click "Configure...". The Configuration dialog will appear. You can step
through to check the settings or simply click "Finish" and the last Dialog
will appear with the "Test Data Source ..." button.

--
HTH
Van T. Dinh
MVP (Access)



"nique" wrote in message
...
Hi Van, thanks for your valuable response!

I don't see the "Test Connection" command you mentioned in the Microsoft
ODBC For Oracle setup in the ODBC Data Source Administrator property page.

Am I using the right source?

Access SQL is not Oracle SQL is not SQL Server SQL ...
Lynn or someone else might be able to help if you post your SQL. This is a
queries NG and you are the only one who can see your query.

--
Duane Hookom
MS Access MVP


"nique" wrote in message
...
Hi Lynn,

Thanks for your valuable response! I have not tried to run the query
with SQL Plus. I actually do not have that tool.

I am most familiar with MS Access and never had the oppurtunity to use the
pass through query.

The odbc driver I am using is the Microsoft ODBC Driver for ORACLE.

The query is complex that involves several tables.

The query runs using the MS Access native query builder, but runs for
about
an hour.

I get the odbc error when using the pass through.

I use the same odbc set up I have for the pass through as the query in the
builder.

What could cause the error that doesn't come up through native Access SQL?

"Lynn Trapp" wrote:

Could you post the SQL for your query? Which ODBC driver are you using?
Have
you tried running the same query in some other too, like SQL Plus?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"nique" wrote in message
...
Hello, having problems using the pass through query to pull data off
of an
oracle back end. I have created an odbc user data source but I still
get
an
error retrieving data but I always get the odbc call failed error
message.
I
have all the appropriate rights to retrieve data. I can use Access SQL
to
pull data but I get errors using Pass Through.

Can someone help me out on this?

Thanks for your valuable response! I have not tried to run the query
with SQL Plus. I actually do not have that tool.

If your company has the Oracle database, then they are also very likely to
have licenses for SQL Plus. You should check with your IT department, unless
that's you, to see if it's available for you to test your queries with. If
it will run there, then you know you have the syntax correct.

[snip]

The query runs using the MS Access native query builder, but runs for
about
an hour.

I get the odbc error when using the pass through.

I use the same odbc set up I have for the pass through as the query in the
builder.

What could cause the error that doesn't come up through native Access SQL?

Most likely there is either something wrong with your connection string or,
else, there is some kind of permissions problem. Try, as Van Dinh suggested,
using the Test Connection button when you find it. Also, if you didn't do
so, you might try copying and pasting the connecting string from your linked
tables into the properties of your Pass-Through query.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm