How do I overcome / work around this difference between local and linked Access Tables

Question

Ok. Quick background: MS Access 2003 with 2003/2003 format MDB file upgraded from Access 97.

For the purposes of this example, there are two tables.

Table 1
Asset
   ID - (text 20)
   ParentID - (text 20)
   Other fields

AssetRels
   ID - (text 20)

When a record is added to Asset, the ID is added to AssetRels.

From Asset.ID to AssetRels.ID there is a relationship that exists that enforces referential integrity with cascade update and cascade delete.

From AssetRels.ID to Asset.ParentID there is a relationship that enforces referential integrity with cascade update only.

I have 2 records in Asset

VACU0703200, NULL
VACU0703250, VACU0703200

In the data DB, I can go into the table and change VACU0703200 to VACU0704500 and the changes propogate as expected.

If I open the front end DB that links to the data DB, and try the same change (in the table directly) I get "Could not update; currently locked" (no, nothing about 'another session', that's the whole error message)

Both databases are set to "no lock" for the "default record locking"

Obviously, there is some difference in row/page/table level locks that is preventing the cascade update from working.

Does anyone know of some property settings that I can use somewhere to stop this error? I would prefer not to have to remove the relationship, but otherwise I might have to, and handle it in code.

Edit: Cause is that the table contains a Memo field. Apparently via the linked table, having the Memo field overflow the 4K row size escalates to a table lock. which in turn triggers the problem.

Solution (hackish) is to prevent edits to the ID field on the form, and add a new form to rename. Save changes before opening the new form, and performing the update via an update query works.

Answer 1

I don't see why moving to the front end and editing this from a linked table should make any difference. I would however delete the table links, and then RE link. Often sometimes some properties are set up and read a time of linking, if you go to the back end database and change some of the table properties, often it's a very good idea to RE link the tables.

As a general rule, you more often have to do the above when you add new columns with linked tables to SQL server. However, I would suggest you delete your linked tables in the front end, and try RE linking, this should fix this problem.