Re: Synonym tables queries for ORACLE through ACCESS

There is an option in Access 2.0 and Access 97 (and probably Access 95) to allow attached things to include 'TABLE','VIEW','SYSTEM TABLE','ALIAS','SYNONYM', in Access 2.0 this was done in an INI file, for Access 97 this is a registry setting. For more information try Access Help andSearch for INI/ODBC and look for the ODBC settings information.

Stewart Burnett

Please remove 'nospam' from address when replying direct.

Garth Martin <gamartin_at_sympatico.ca> wrote in message news:368FB3BD.167ABFEC_at_sympatico.ca...
>In my experience with Access and Oracle, you should be able to see the
referenced
>table as 'owner.table_name' in the link/import table tab in Access. While
you can
>see both the Synonym and the original table, Access generates an error
saying that
>there are multiple tables with the same name. Choose the fully qualified
table to
>link to and it should work.
>
>HTH
>
>Garth Martin
>
>Jonathan Gennick wrote:
>
>> On Thu, 31 Dec 1998 20:48:25 GMT, muhammadb_at_epenergy.com
>> wrote:
>>
>> >Oracle has a security feature that you can refernce original tables in
the
>> >databse through synonym tables in your query. This enables the secrecy
of the
>> >original table .
>>
>> Synonyms are not a security feature. They exist as a
>> convenience, and to insulate you from having to code a
>> specific schema.tablename in your queries. It's quite easy
>> to find out the table to which a synonym points.
>>
>> <snip>
>> >The trouble
>> >I'm having is that when i hook ACCESS to ORACLE . here is the catch 22
. I
>> >can only run the query for the tables I see in my tables tab in ACCESS.
But
>> >if the original tables are hidden in oracle , they won't be visible in
>> >access. If they are not visible I can't run the query. How can i
refernce to
>> >the synonym tables in ORACLE through my ACCESS query .
>>
>> I understand this problem. The same issue exists when we use
>> Powerbuilder to connect to Oracle. Is it possible for you
>> to just type in the SQL query directly, instead of using the
>> GUI query builder provided by Access? That way you could
>> just type in the synonym name.
>>
>> I wish I had a better answer for you. With our PB app, we
>> just go straight to the "Show Query as Syntax" screen, and
>> type in the SQL as we want it.
>>
>> regards,
>>
>> Jonathan
>
>
>
Received on Tue Jan 05 1999 - 08:48:23 CST