Home  |   French  |   About  |   Search  | mvps.org  

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

In Memoriam

Terms of Use


VB Petition

Tables: Oracle Synonyms

Author(s)
Dev Ashish &
Steve Jorgensen

    With a linked ODBC table, as you know, we need to have an index field defined to be able to update the data. When manually creating a link to an Oracle table's synonym, we are prompted to pick an index. If we bypass this, no further errors are generated, except the table now becomes read only.

    The problem is further complicated when one creates/manipulates the ODBC link from code. VBA will create the link, without prompting for the index. Result: All ODBC tables you just linked from code are now read only.

    The only solution I found.... Don't use synonyms, connect directly to the actual table.

    For what it's worth, I think this is happening because the ODBC driver, when returning viewable table & synonym info from Oracle, does not distinguish between a table and a synonym. When Access tries to link to a synonym, it thinks it's linking to a table and it cannot find an index; and rightfully so, since the index is present in the actual table. So manually, one's prompted to pick an Index, but from code, this does not take place.

Alternative:

You can use a DDL query to define the unique key of an attachment/link to a view in Access 2, 95, or 97. Access 97 is the first version that also allows key fields to be defined manually when the link is first created.

The DDL query method still works under Access 97. Simply run a DDL query that contains a command to define a primary key on the link. Since it is being applied to a link, JET interprets this statement as specifying which fields uniquely identify a single row. I know this method works because I use it regularly.

Example:

create index ProdBatchID on vwInventory(ProdBatchID) with primary


© 1998-2010, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer