#Error error corrupts records in a table

[Suyash]

Hi,

I have a database in MS Access 97 which has a form consisting mainly
of "Memo" fields. It is accessed by a number of people. Every now and
then, if the database is left open or sometimes upon closing it and
reopening it, it corrupts some of the fields on the form and displays
#Error in the field instead of the actual text.

You can then no longer edit any field on the form nor can you edit any
field from the back-end in the table linked to the form. You cannot
even copy the record directly from the table and paste it back in
after removing the #Error message.

As a result, you have to copy each field manually to MS Excel - delete
the #Error message and paste it back into the table. The previous
record is corrupted and hence has to be deleted. Is there any way
round this problem i.e how to make sure we don't get this #Error
message as trying to resolve it by copying to Excel etc. is quite a
tedious task.

Thanks !!

 

[Larry Daugherty]

Once corruptions has occurred your recovery options may be limited.

The best approach is to do all that you can to reduce the possibility
of errors.

1. Is your application split into Back End (just the tables) and
Front End (everything except the tables)? If not, it should be.
2. Is there a separate copy of the Front End on each User's machine?
There should be.
3. Once the above is true, your most common cause of corruption is
network issues. One very common cause of network problems is faulty
Network Interface Cards.

HTH

 

[Suyash]

Hi Larry,

Thanks for your reply !!

Yes, the database is split into front-ends and back-ends with the
front-end containing forms, reports, queries and Linked tables to the
back-end.

Also, we have ensured that each user has a separate front-end as when
he double-clicks to open the database, it copies it from the network
onto his local C:\.

However, in spite of this, whenever the #Error occurs in any field, it
entirely locks up the database - Cannot click on any button on the
front end, cannot edit any field & it pops with an error message
saying something like "Microsoft jet engine cannot make any changes as
it is locked by another user". I am sorry I do not have the entire
message as I have only experienced this once I guess. However, I
support it and this is what I have been told.

Does this shed any more light at all ? Would it be posing a problem if
more than one person is looking at the form at the same time.. I
realise the front-ends are local, but they are still accessing the
back end (the table in the data file which is on the network) at the
same time, aren't they !

Thanks!!

Suyash

 

[Larry Daugherty]

If the users each have their own copy of the front end then they're
never looking at the same form, just their copy. However, the way
you've described it is ambiguous - maybe everyone is opening a single
copy which resides on the server??? The answer is to be found in
where the shortcut/icon on their desktop points.

Yes, the corruption is occurring in the BackEnd, which is common to
everyone. That's why it's so important to prevent problems from
occurring in the first place.

Good luck isolating the problem.