Pass-thru query via ADO connection in VBA

[3pmilk]

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.

 

[cmmrfrds]

Use the ADO Open method for "Select" queries.

rs.Open "your sql statement", yourconnection, cursortype, locktype

Example of bringing back a static recordset with optimistic lockinhg.
rs.Open "Select * from yourtable", yourconnection, 3, 3

 

[3pmilk]

Thanks cmmrfrds. Would this work for pass-thru query?

 

[cmmrfrds]

When you say "pass-thru" I assume that is just some PL SQL. Yes your Oracle SQL can be sent, in fact, this needs to be Oracle SQL when sending through ADO>

 

[3pmilk]

actually it's T-SQL (it has INNER JOIN clauses) plus oracle keywords like start with and connect by. I don't think it's PL/sql because it doesn't have keywords like begin and end

 

[cmmrfrds]

The keywords Start and Connect are part of Oracle standard SQL. Send your Oracle SQL throught the Open method. This is just a standard Oracle SQL statement, Jet is not involved at the point. I guess you could call it pass-thru SQL. Although in Access pass-thru queries are typically done through ODBC, which is different than what is being done through ADO.

 

[JerryKlmns]

I know that
2 Times = Double
3 Times = Triple
4 Times = Quad
BUT
6 Times = TROUBLE

thread705-1245798,-- This one
thread707-1245801,
thread181-1245800,
thread222-1245802,
thread701-1245799,
thread702-1245796

3pmilk I would kindly request to STUDY the faq222-2244 and see in #5 why I 've post this.

 

[BobRodes]

< a way to call a pass-thru query via ADO connection object?

Yes and no.

The concept of a "pass-through" query is to allow queries that are written in the syntax of the data server (as opposed to the syntax of the access object such as DAO) to execute properly. In ADO, this is pretty much a moot concept.

OLE DB has data consumers and data providers. ADO is an example of a data consumer. All of the providers in the "Provider=whatever" part of the connectionstring property in the ADO connection object are examples of OLE DB providers.

So, if you have "Provider=SQLOLEDB" in your connection string, SQL statements in Transact-SQL syntax will be understood, others will not. If you have "Provider=Jet 4.0", then you'll need to use Jet syntax, and so on.

In effect, all ADO queries are "pass-through" queries, since they are all passed through to the OLE DB provider and parsed at that end. There isn't a concept of parsing a SQL statement at the ADO end, as there is in the DAO world, so there isn't a concept of pass-through queries per se.

HTH

Bob