SQL Pass-Through Query to Oracle fails

[Guest]

Hi,
I am having a problem with a SQL Pass-Through Query. The SQL is SELECT
TABS.* FROM TABS; The error is ODBC--call failed. [Microsoft][ODBC driver for
Oracle][Oracle]ORA-00911; invalid character (#911). Access 2003, Microsoft
ODBC for Oracle 2.575.1117.00. I hope there is just a setting I am missing as
the Oracle driver works for the Pass-Through Query but is so slow when
working with tables I would prefer using the Microsoft driver.

Thank you,
Dave

 

[Guest]

doh, I deleted the semi-colon and it works fine.

 

[blah]

Just wanted to chime in (5 years hence) because I too had the same problem. This thread reminded me that the semicolon was the likely culprit.

In this case, A2007 vs. Oracle of some vintage unknown.

Creating a pass-through in VBA with a strSQL stored in a table -- all is well so far. There is no ";" in the table. However, using

qdf = db.createquerydef( "blah" , strSQL)

automatically appends the qdf.sql with the dreaded ";", so I simply removed it via the helpful

qdf.sql = replace(qdf.sql, ";","").

(I create the qdef because it is nested inside a make-table query. Perhaps this doesn't need to be done, but it helps demystify the code for later reviewing).

Now I am happy to report that, for the moment, all is sweetness and light.

 

[blah]

I should also point out that another thing db.createquerydef did to the strSQL, which was minding its own business in the table, was that it took the part that said, "select * from blah.yadda" and twisted it into "select * from (blah) yadda", so that I had to change the string in the table to "select * from [blah.yadda]" to get around that. A pain in the @@@ to be sure.

[edit]

Now, as it turns out, both these problems can be solved by first creating an empty query, i.e.

qdf = db.createquerydy("blah") --- note no strSQL;

and then assigning the sql property afterwards, i.e.:

qdf.sql = strSQL; in which case it does not get reformatted by Access and can be accepted as-is by Orca.

I'm sure this is Access 101 for most but some of us are just tryin' to get by.

Peaceout.