Oracle from VBA - can't attach

[pbackstrom]

I need to get information from an Oracle view.

I see it in the Link Tables list, but cannot attach it because it says
"There are several tables with that name. Please specify owner in the format 'owner.table'."
But there's no place for me to type in the owner name and it doesn't appear in the list -- when I hit Cancel the box goes away.

I tried to query datasources directly:
some dsn-less solutions from forums wherein I could open the connection ok but got "The connection cannot be used to perform this operation. It is either closed or invalid in this context." when I tried to open a recordset.

I thought OK I can attach in code and provide the owner name that way, but the examples I've found
use DAO which I'd rather not rely on.

Recommendations to either
a) allow me to attach the table,
b) attach it from code, or
c) query it without attaching it?

Thanks much.

 

[tledwards]

I think the owner name in this case is the database name. I use a pass through query to data from an Oracle database. Here part of my code. In the From portion I list the databasename.table.

SELECT SCHEDULES.PINS_AGMT_ID, SCHEDULES.ACRN_ID, SCHEDULES.ACCEPTED_QTY, SCHEDULES.ACO_CODE
FROM OLAP_OWNER.SCHEDULES
WHERE SCHEDULES.ADMIN_BY_CAO='XX'

Hope this helps.

 

[pbackstrom]

Yes, passthrough seems to be my only choice. Do I need to create a querydef first? All the doc I see on passthrough acts like it. Would you be willing to post more code?
I'd rather create the SQL on the fly but will keep poking around.
Thanks.

 

[tledwards]

I just use the Access standard query for this and have not tried to do it with SQL on the fly.