Pass-through Query from VBA Module in MS Access

B

BruceTompkins

I am trying to find out how to set up and execute a a pass-through SQL query
from VBA code in an MS Access database application. I have created a
pass-through query that executes an Oracle SQL query successfully when I open
the query in Access. Now I want to open the query in a VBA module using ADO.
I get an error when I try to use the pass-through query when opening a
recordset. I have done the following:

Dim MyConnect As New ADODB.Connection
MyConnect = CurrentProject.Connection
MyConnect.Open

'set up the recordset for pool inventory calculations
Set rstWellRecs = New ADODB.Recordset
rstWellRecs.Open "Oracle Countess Bow Island Well Data Query",
MyConnect, adOpenForwardOnly, adLockReadOnly, adCmdTable

Any help would be appreciated.

Regards, ...Bruce
 
B

BruceTompkins

I forgot to mention that when I run this VBA code, I get an error on the
rstWellRecs.Open statement - Syntax Error in FROM Clause. However, this
query, [Oracle Countess Bow Island Well Data Query], runs without error in
Microsoft Access.
 
B

BruceTompkins

OK, so I figured out how to execute an exising pass-through SQL statement
from VB, after looking at some examples of creating a pass-through SQL
statement. I used DAO instead of ADO (which I prefer) and did the following
(see code below). Note that I don't have to specify the ODBC database
connection info as this is defined in the SQL pass-through query itself.
THis works great and executes quite fast too.

'in order to execute our SQL pass-through query, we must use DAO
Dim MyDAODatabase As DAO.Database
Dim MyDAORecordSet As DAO.Recordset
Dim SQLPassThruQuery As String

'set the name of the SQL pass-through query already existing in the
Access db
SQLPassThruQuery = "MySQLPassThruQuery"

'set up the database reference
Set MyDAODatabase = CurrentDb

'settings for executing this query
MyDAODatabase.QueryDefs(SQLPassThruQuery).ReturnsRecords = True

'open the record set for the SQL pass-through query
Set MyDAORecordSet =
MyDAODatabase.QueryDefs(SQLPassThruQuery).OpenRecordset

'check for any data
if not (MyDAORecordSet.EOF) then

'get the first record to start with
MyDAORecordSet.MoveFirst

.... etc.

BruceTompkins said:
I forgot to mention that when I run this VBA code, I get an error on the
rstWellRecs.Open statement - Syntax Error in FROM Clause. However, this
query, [Oracle Countess Bow Island Well Data Query], runs without error in
Microsoft Access.

BruceTompkins said:
I am trying to find out how to set up and execute a a pass-through SQL query
from VBA code in an MS Access database application. I have created a
pass-through query that executes an Oracle SQL query successfully when I open
the query in Access. Now I want to open the query in a VBA module using ADO.
I get an error when I try to use the pass-through query when opening a
recordset. I have done the following:

Dim MyConnect As New ADODB.Connection
MyConnect = CurrentProject.Connection
MyConnect.Open

'set up the recordset for pool inventory calculations
Set rstWellRecs = New ADODB.Recordset
rstWellRecs.Open "Oracle Countess Bow Island Well Data Query",
MyConnect, adOpenForwardOnly, adLockReadOnly, adCmdTable

Any help would be appreciated.

Regards, ...Bruce
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top