Vanishing Links (v2000)

Topic

New Reply

I wonder if anyone else has experienced this.

I have two tables linked with cascading updates selected. Somehow, one of my records corrupted (when I opened the table, every field in the record read “Error”). I couldn’t delete the record in the current state so I compacted/repaired the database. When completed, I could then delete and recreate the record. However, something odd happened. The primary keys in the table with the corrupted record vanished. The links also disappeared. As a result, the cascading update feature no longer functioned.

I caught on to the problem and fixed it but I am wondering:
– what could cause the record to corrupt like that and what steps could help prevent it?
– what steps can I take to ensure the primary keys and links don’t disappear?

Reply | Quote

Replies

Memo, hyperlink and OLE fields are risky, because the data for such fields are not stored in the table itself, only a pointer to the data. If possible, avoid these types of fields (or move the tables to SQL Server).

Do you mean that the primary key field was completely removed from the table? Or that its value was cleared in all records, not just the corrupted record? I can’t remember seeing such drastic effects of repairing a corrupt record. In general, it’s always wise to make a backup copy before repairing a damaged database. That way, you can (hopefully) still get at the undamaged data in the corrupt database if something goes wrong.

Reply | Quote

Hi Hans,
Sorry about the confusion. To clarify myself, after I deleted the corrupt record:
* The primary key and data were fine but it removed the primary key index and converted the field to a normal field (not primary)
* In the relationships section, it deleted all the relationships I had created earlier from the table with the corrupt record
I hope that better explains my situation.

Reply | Quote

AFAIK, it is not “normal” that and index and relationship are removed. I can only guess that the index had become corrupt too (an index is a database object too, if invisible).

Reply | Quote

Cheers…thanks Hans!!

Reply | Quote

This same exact thing happened to me, though we’re using Access 2002/Win XP. From what my users said, two people were looking at (editing) the same record at the same time. The database has several tables, but there is only one table with a memo field. The users were working with data in that particular table. One user got completely locked out, the other was still able to work, but nobody could access that particular record. So I closed everyone down, deleted the .ldb file, compacted/repaired the database and everything seemed to be OK.Then same thing happened again about a month later. Did the same thing and thought everything was OK. At that point, the setup was 3 machines connected to a switch, with the database split into FE/BE on one machine and shared. Shortcuts to the FE were on the two other workstations.

A few weeks later, I happened to look at the database relationships and noticed some were missing! Tried to restore them and got the message that I couldn’t due to referential integrity rules violations. Yikes! Went into the affected tables and found a handful of records without parents, and records close by with the same data that did have parents. My guess is that some of the records were just left over when a parent record was deleted and the cascade didn’t occur and that the other records had been re-entered. So I deleted the orphans and restored the relationships. I also had to reset the primary keys in the affected tables, as they’d changed to regular keys.

Did a little more research on this great board and decided to put the front ends on each workstation, as well as changing the default record locking behaviour from “no locks” to “edited record”. Tried to duplicate the error and got a warning message about another user making changes to the record, which makes me feel a little more confident about the stability now. I am stuck with the memo field, I’m afraid, as it’s not possible to use SQL server and the users need a lot of space to record case information.

If anyone has other ideas about how to avoid this problem, I’m all ears. Thanks for listening!

-John

Reply | Quote

Laba diena,

Judging by the number of threads about database corruption, you’ll never be able to avoid it completely. But putting frontends on each workstation should go a long way towards it – there are all kinds of issues if users share objects in the same database.

Reply | Quote