PASS THROUGH QUERY WITH ORACLE BACK END

[Guest]

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?

 

[Lynn Trapp]

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?

 

[Van T. Dinh]

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)

 

[Van T. Dinh]

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.

 

[Duane Hookom]

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.

 

[Lynn Trapp]

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.