Linked Oracle tables ... losing primary key

I sure hope I'm not the only one in the world with the following problem ...

I have an MS Access front end to Oracle via ODBC, with 40 or 50 linked
tables and views. When you link a table/view originally, there is a dialog
that asks you to select the field that represents the "unique record
identifier", i.e., a primary key. Without this, Access simply makes the
linked table read-only. Which makes sense.

Ok. But what happens is this ...

In the course of working with the Access database, at some point Access
randomly "loses" the primary key information that it had on a given table.
As a consequence, Access thinks the table is read-only and will not allow
you to edit it. When I say "working with", I simply mean modifying forms,
opening and closing tables, etc.

I don't know if Access is doing it, or ODBC is doing it, but you can see it
happen. There is an OLEDB property for recordsets called "unique rows". If
there's a primary index on the table, this property is set to TRUE. I can
monitor it as I repeatedly open and close a particular table. At some random
point, the property suddenly switches from TRUE to FALSE. And the index that
Access was using (it's called "__uniqueindex") suddenly disappears.

I was recently working with this particular database, doing a bunch of
modifications over the course of a couple of days. When I was ready to put
it back into service, lo and behold the primary key of every single table in
the database was gone. Very frustrating, since there does not appear to be a
way to restore the index programmatically. You have to manually re-link
every single table.

Has anyone else experienced this problem and found a solution?


.

Relevant Pages

  • Re: Asking for help on breaking down table to smaller tables
    ... I have a database with the same problem. ... because it generates a new Primary key for each new table and limits ... and each record in the new table will relate to its own unique record ... I have looked at the websites provided in the previous posts and ...
    (microsoft.public.access.tablesdbdesign)
  • Re: problem using identity column as primary key
    ... >> I am thinking of creating an identity column to use it as primary key ... More and more programmers who have absolutely no database training are ... the gap in the sequence is not filled in and the sequence ... vin CHARNOT NULL REFERENCES Motorpool); ...
    (microsoft.public.sqlserver.programming)
  • Re: Nameless CREATE
    ... The starting point is either an existing database or one that is ... "Describe" involves issuing an SQLDescribeCol function to ODBC ... essentially much the same as character data type apart from all 8 bits ... ALTER TABLE tablename ADD COLUMN BinaryColumn BINARY ...
    (comp.lang.forth)
  • Re: Updating the SQL key value
    ... before creating the database, by the time I've come to pick a primary key, ... articles, each of which must be issued under a particular licence (e.g. ... GPL GNU General Public Licence http://www.gnu ... ...
    (comp.lang.php)
  • Re: fields queries and utter disaster
    ... Unfortunately the \c ODBC DATABASE field switch is not supported in Word ... of course supports ODBC connections but Word ... Mac Word Test ... suggests that it should be possible to use query files (cf. .qry/.dqy ...
    (microsoft.public.mac.office.word)