Edit rights to linked Oracle databases

[trein.walsh]

I have ODBC connections established to a Production Oracle
9.2 database and a Test Oracle 9.2 Database - using
Microsoft Access 2000 to view and edit the database
tables. Test is a database import of a Production database
export. I use the same user to access both environments.
Using the ODBC channel to Prod in Microsoft Access 2000, I
have no problem getting write access to the tables. Using
an ODBC channel (set up exactly as the Prod one) and the
same userid (set up exactly wih the same rights as in
Prod) I have no write rights using Microsoft Access to the
same tables. I also checked that the "Security" options
under "Tools" are set up the same for the 2 Microsoft
Access "views/databases". What is preventing my write
access to the tables in the Test environment?
..

 

[Joe Fallon]

The lack of a Primary key index on the tables in the Test environment will
cause this.
When you link to the tables you should be prompted to select a Unique Record
Identifier. If you cancel these dialogs you are stuck with read only tables.
You should choose the combination of fields in each dialog that uniquely
identifies each row.

You can do this "after the fact" by running queries:

You can create a "Fake" index in Access that tells Access which columns make
a Unique record in the linked table. The index is only used by Access is
completely unknown to the Server.

The following example creates an index on an ODBC linked table. The table's
remote database is unaware of and unaffected by the new index. The following
example won't work if you simply paste it into Microsoft Access; you first
need to create an ODBC linked table called OrderDetailsODBC.

(CREATE INDEX Statement in Help has some examples)

CREATE UNIQUE INDEX OrderID ON OrderDetailsODBC (OrderID);