Problems with returning records using ODBCDirect recordset

[rickj65]

I have an Oracle database I'm accessing in VBA using an ODBCDirect Workspace/Connection. I set up the Workspace object using the dbUseODBC option and then create a Connection object using the typical ODBC connection parameters for Oracle. I connect without errors.

I create the recordset using a SQL statement as the recordset source (SourceSQL) as follows:

Set rstSource = conODBC.OpenRecordset(SourceSQL, dbOpenSnapshot)

and I don't receive any errors.

However, the recordset does not return any records. Thinking that there was a problem in my SQL statement, I copied it into the SQL Pass-through query builder, entered the same login parameters as my connection object, and it returned the records that I was expecting.

Taking it one step further to make sure my Connection object is correct in VBA, I set up my ODBC connection to prompt me for the ODBC parameters (rather than accepting my coded Connection string) using the dbDriverPrompt option in the Connection object. This did not yield any records either.

So my query and ODBC data access works when I enter through the MS Access front-end, but the VBA module doesn't seem to like it even though I'm using the same SQL and (appear to be using) the same connection parameters.

Any thoughts?

Rick