access 2016 pass through query fails but linked table works

0
votes

I am basically an Access novice, so apologies if any of this doesn't make sense:

I am attempting to set up an automated procedure in Access 2016 to run pass-through queries to an Oracle database and then put the results in Access tables. I know that my queries themselves work, because from Access 2007 (where I initially wrote them) they connect to the Oracle database just fine. I also believe that my ODBC connection is OK in Access 2016, because I can use the "External Data -> ODBC" button to link the Oracle tables via a Machine Data Source. However, in Access 2016 I cannot run the pass-through queries!

When I originally created the queries (using Access 2007) I figured out what connection string I needed by clicking the "ODBC Connect Str" box in the query Property Sheet. This opened a wizard that filled in the appropriate connection string for me, and after figuring out what was needed I set up my VBA process to supply the connection string (including my login info) within my automated process but then delete it when it finished.

However, when I use the "ODBC Connect Str" wizard in Access 2016, the queries will not run. The wizard fills in a connection string, but then when I attempt to run I get an error message that says "ODBC Call failed."

Does anyone know why these pass-through queries would work in one version of Access but not the other?

1
oraclems-accessodbc

1 Answers

0
votes

I expect that you use a SQL syntax which is supported by Microsoft Access, but not by Oracle itself. A pass through query, as its name says, will be passed to the server and not being interpreted by Microsoft Access.

Thats all I can answer right now.

To give us the possibility to give you a more detailled help, we also need more details.

So please post at least one not functioning query you try to run.