BorlandTalk.com

John Provine · Guest

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?