Schema names in ODBC queries

We are using Oracle ODBC drivers (71.11.1.5), Oracle 7.2 and Access 2.0.

When attaching tables with ODBC the schema name is included in spite of the existence of public synonyms (steve.emp appears in the attach table list rather than the public synonym emp).

Queries employing the same table twice return a ORA-00960 ambiguous column naming in select list error when the attached table is renamed in the Access app (the attached table property still includes the schema of the table).

The problem goes away when the table is not renamed in the Access application, and the schema name is included.

Question: how can we overcome this problem without employing the schema name of each individual developer in queries (steve.emp, mary.dept etc.)?

I have seen Sybase environments set up to have the dbo role show up as owner regardless of which developer created the table (dbo.emp, dbo.dept). Can something similar be done in Oracle?

Thanks very much for your time and help.

Steven Moyano
Northwestern University, Evanston, IL. USA stevem_at_nwu.edu Received on Tue Apr 22 1997 - 00:00:00 CDT