Relationships lost and record deleted

[trandel]

I have a peculiar problem.

The sequence of events is:

1. The user reports that on clearing (deleting) data in a memo field within a table an error (sometimes) occurs which results in all fields for that record containing the word #deleted. The table can no longer be opened for update via a form. (Prior to deleting the data in the memo field the user had been using copy/paste data from the memo field into other fields within the record)

2. When I open the table in datasheet view, there is a record filled with # signs usually at the beginning of the table. I delete that ( I believe that is all that is left of the original record - but there is no other identifying data).

3. The primary key (autonumber) of that table is reset i.e. it is no longer designated as the primary key. I recreate it in table design.

4. All relationships with that Primary key are deleted.

5. I recreate the relationships, run a compact and repair and all is well except that the original record is lost and has to be recreated as well.

All this because a user deleted some data in a memo field!

The DB is split with the BE on a Windows 2003 Server and the users running their FEs on a Lan. Only Access 2003 is used.

I cant replicate the problem on my development system.

Any ideas very welcome

 

[burrcm]

Memo fields are prone to corruption and of course so is Access generally. I would import the backend data into a new database and replace the current backend, also try to avoid the use of memo fields entirely.

Chris B

 

[burrcm]

Also, are you placing the relationships in the backend or the fronts? Better in the front I suspect.

Chris B

 

[trandel]

Ok -I will try out importing to a new db for the BE - but wont it simply copy the 'problem' whatever it is?

I will try and get rid of memo fields - I used them way back when I didnt realise they were a problem!! Conversion to text might be interesting............

I think relationships have to be in the BE where the tables are - the FE inherit them. I tried to build one recently in a FE and it would not allow me to enforce relationship integrity. Imagine the possibilties if one was llaoed to have differing relationships amongst the FEs?

 

[burrcm]

Importing into a new db is a tried and true method of repairing corruption. It will work pretty well in many cases. You are correct re the relationships, my mistake.

Chris B

 

[Pat Hartman]

Sometimes you need to export all the records prior to the corrupted one and then all the records subsequent to the corrupted one to repair the table.