Hi, Bill.
I don't see any way to do this in the gui/link wizard.
You can't. It's a problem with the Oracle database schemas. I've only ever
seen this problem once with an Oracle database I inherited years ago that was
built by <gasp!> non-DBA's. After I did an extensive redesign of the
schemas, this problem and many others disappeared. I believe what caused
this problem is too many public synonyms in the database for the same table
name. Let me explain.
When referencing a table in an Oracle database, the correct syntax is
SchemaName.TableName. This can get pretty tedius typing the whole name out,
so synonyms were created to be able to reference the table without the
schema's prefix. For example, if Joe's schema name is Joe and he creates a
table named Foo, the proper way to reference it in SQL is like this:
SELECT * FROM Joe.Foo;
A synonym is created and proper permissions are granted to the role assigned
to Joe, so Joe can write queries on this table without referencing his schema
name:
SELECT * FROM Foo;
And Sam can do the same thing with his table named Foo. Before creating a
synonym, Sam would need to write:
SELECT * FROM Sam.Foo;
After a synonym is created, and proper permissions are granted to the role
assigned to Sam, Sam can write queries on this table without referencing his
schema name, too:
SELECT * FROM Foo;
And you ask, "How does Oracle know which Foo Sam is referencing and which
Foo Joe is referencing if the synonym uses the same table name?" Good
question. Through the role assigned to Sam, Sam only has permissions granted
for the objects in his own schema, and Joe only has permissions for objects
in his own schema through the role assigned to him. So Sam can't use a
synonym from another schema, because he has no permissions for it. Likewise
with Joe. So in each case, based upon which user is executing the following
SQL query,
SELECT * FROM Foo;
.. . . Oracle knows exactly which table to use.
Along comes Dave and George, who need access to Joe's Foo table, and of
course, they don't want to have to spell out the full SchemaName.TableName
either, so the DBA grants them a role that has permissions to use the synonym
for the Joe.Foo table. Now Dave and George can write queries that reference
the Joe.Foo table by using the synonym:
SELECT * FROM Foo;
Another option is to create a public synonym so that no one needs specific
permissions to use the synonym. The severe downsides to this are that it's a
huge security risk for everyone to have permissions to an object, and that a
public synonym can only be created for one of the many tables of the same
name. For example, if a public synonym were also created for the Sam.Foo
table, which table would Oracle use for the following query?
SELECT * FROM Foo;
Hence, why I believe there's confusion when Access is trying to link to a
table that has multiple public synonyms of the same table name: "Which Foo
do you want?" So contact your Oracle DBA so he can look into this for you
and resolve it.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
