Hi all,
Any help would be much appreciated. I would like to know if there is a way to call a pass-thru query via ADO connection object?
for start, I have a pass-thru query (with oracle reserved key words) in Access 2003, so I can't covert the pass-thru query into sql statements in VBA programmatically, because there's no direct equivalent in T-SQL for Oracle's START WITH...CONNECT BY.
My first problem is that I can't save the ODBC connection for the pass-thru query permanently. I may need to save it via VBA programmatically but don't know how.
I managed to save the ODBC connection via Access "View" -> "properties." It would work at the time it was saved. However, when executing the same query at later times, it would generate an "ODBC failure error" and wrould prompt me to re-set the property.
My second problem is that how can I execute the pass-thru query through ado connection?
here's the sample code that's failing saying "execute method fail"
set rs = conn.execute("pass-thru query name", , adCmdText)
I came up with an alternative half-baked soultion to my problem. I was thinking maybe I can use docmd.openquery in Access to open my pass-thru query and somehow save the result set as a sql statements and pass it to a variable...once I have the
variable containing the sql statements then I can
use the connection object to execute it..but i'm not sure how i can convert the result of the openquery to a variable either.
