|
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
|
