#Error in Memo Field - problem compacting

[Guest]

Hi

I'm getting a really strange error when viewing a record
on my database.

I have 3 memo fields, and two out of the three in one
record has '#Error' in its contents. When I attempt to
click into that field I get the following error:

The Microsoft Jet database engine stopped the process
because you and another user are attempting to change the
same data at the same time (error 3197).

After sifting through the knowledge base, I came up with
an article which I believe describes the problem, article
number 182867.

However, after upgrading Microsoft Jet to 3.51, and
running the compact/repair function as detailed in the
fix, I receive the following error when compacting:

Record(s) can't be read; no read permission on <database
path>.

I have encountered the same problem in another database I
have in the past, and I resolved the issue by simply
copying the record and pasting it into a new one. The
memo field was cleared but I could just add in what was
meant to be in there.

However, in this database copying and pasting the record
isn't as easy - I have an Autonumber field which is used
in the naming format for some word documents that come
from the system, and copying/pasting the record means
alocating every document the new number and reordering the
directory structure.

Does anyone know of any ways of fixing this? I don't mind
being able to clear the Memo field contents, I just don't
want to have to change the Autonumber.

Thanks for your help

Russell

 

[Allen Browne]

The error indicates that there is a corruption within the database.

Try creating a new (blank) database, and importing all the objects from the
old one into the new one. This will hopefully force Access to recreate all
the data.

Another alternative would be to delete the record from the table. Compact to
get rid of the problem. Then use an Append query to write the values back to
the fields in the table. With an Append query, you can assign an unused
number to the AutoNumber column, meaning you have effectively kept to same
number. (If there are records in related tables with Referential integrity,
you could delete the relations from the Relationship window, and recreate
them again after the Import.)

 

[Russell Farr]

Hi Allen

Thanks for your help.

First option I already tried - but it refused to import
the affected table stating that more than one user was
attempting to access it at the same time.

However, I have done the second option, which worked a
treat!

Russell