Corruption?

L

Lars Brownies

When trying to update a field in a form the field suddenly said #deleted#
while the other fields were in tact. The user wasn't able to change the
specific field anymore. I did a compact/repair on the backend and now
everything is (or at least seems to be) fine.

I now do see a table in the backend called MSysCompactError which indicates
error -1017 (Can't find field Description).

Can I safely proceed and delete the Error table, or should I do something
else, maybe delete the whole record and enter it again?

Thanks,

Lars
 
G

Gigamite

Lars Brownies said:
When trying to update a field in a form the field suddenly said #deleted#
while the other fields were in tact. The user wasn't able to change the
specific field anymore. I did a compact/repair on the backend and now
everything is (or at least seems to be) fine.

I now do see a table in the backend called MSysCompactError which
indicates error -1017 (Can't find field Description).

Can I safely proceed and delete the Error table, or should I do something
else, maybe delete the whole record and enter it again?

The presence of the system table, MSysCompactError, means at least one of
your table definitions is now unreliable. To be on the safe side, I'd
restore the most recent backup, then I'd create a new mdb file and import
all the objects from the corrupted database file into it. Then I'd add the
most recent records from the new mdb file to the restored database, remove
any deleted records, and update any updated records since the backup was
taken to make the restored database current.
 
J

John W. Vinson

When trying to update a field in a form the field suddenly said #deleted#
while the other fields were in tact. The user wasn't able to change the
specific field anymore. I did a compact/repair on the backend and now
everything is (or at least seems to be) fine.

I now do see a table in the backend called MSysCompactError which indicates
error -1017 (Can't find field Description).

Can I safely proceed and delete the Error table, or should I do something
else, maybe delete the whole record and enter it again?

Thanks,

Lars

Sounds like corrupt systems tables! See
http://www.granite.ab.ca/access/corruptmdbs.htm
for suggestions. Gigamite's suggestion of creating a new database and
importing objects would be a very good start.
 
T

Tony Toews [MVP]

Lars Brownies said:
When trying to update a field in a form the field suddenly said #deleted#
while the other fields were in tact. The user wasn't able to change the
specific field anymore. I did a compact/repair on the backend and now
everything is (or at least seems to be) fine.

I now do see a table in the backend called MSysCompactError which indicates
error -1017 (Can't find field Description).

Note that you should *ALWAYS* make a copy of the MDB before compacting
if you suspect corruption.

1017 - "Could not find field "Description" It's possible the problem
is with a table that has a self-join and Memo fields, and has
referential integrity enforced. ACC2000: Table Corruption After
Referential Integrity Checks - 296389
http://support.microsoft.com/?kbid=296389 But it's more likely to be a
sign of corruption

Now I'm not at all sure I agree with the advice of going to yesterdays
backup and making the required changes to it. The problem is that
your users may not recall what changes were made.

Instead I'd suggest importing this MDB into a new one which should
clean up the corruption. Then compare the number of records in the
new MDB with the current MDB in each table.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
L

Lars Brownies

Thanks Gigamite. I imported the compacted mdb in a new mdb. Everything seems
to be fine. The further steps you mentioned was not possible since I'd be
spending days figuring out what values were edited.

Lars
 
L

Lars Brownies

Thanks Tony.
As far as I know there is no self-join (I assume by self join is meant that
you join a table to itself?) The field where the error occured was indeed a
memo field.
Instead I'd suggest importing this MDB into a new one which should
clean up the corruption. Then compare the number of records in the
new MDB with the current MDB in each table.

This is what I've finally done and results seem OK.

Pretty scary stuff, these corruptions.

Lars
 
T

Tony Toews [MVP]

Lars Brownies said:
Pretty scary stuff, these corruptions.

Yes but they have two good uses.

1) They force you to think about your backup.

2) They are a good stress test for your heart. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
D

David W. Fenton

Pretty scary stuff, these corruptions.

Gee, I haven't had one at any my clients in at least 5 years.

Corruption is usually an indication that you're doing something
wrong somewhere along the line.
 
L

Lars Brownies

This was my first corruption in the 2 years this app exists. Lately the app
is being used by more users, 18 total of which about 8 are "heavy users".
Maybe that's the reason why this issue comes to light.

"something, somewhere, along the line..."

At least now I know where to start looking :)

Though I've implemented anti corruption measures I will read up again on the
corruption pages. Maybe I have to separate the memo fields in a separate
table. Ouch, that will take a lot design changed.

Lars
 
D

David W. Fenton

Though I've implemented anti corruption measures I will read up
again on the corruption pages. Maybe I have to separate the memo
fields in a separate table. Ouch, that will take a lot design
changed.

The first thing to do is just to change memo editing to use unbound
controls. You leave the memo field in the form's recordsource, but
don't bind it to a textbox. Instead, in the OnCurrent event, you
assign the memo field(s)' value(s) to the textbox(es). In the
AfterUpdate event of each unbound textbox, you write the value of
the unbound textbox back to the field in the form's recordsource.
There are a few downsides to that (memos are no longer searchable
with the built-in FIND function, for instance), but they are minor
in comparison to the dangers of memo corruption.

If you want bullet-proof memo fields, well, that's not entirely
possible because there's always the possibility even with unbound
editing of something happening. However, moving the memo fields to a
separate table certainly protects the non-memo data, as a corruption
of a memo field will never lead to a loss of any non-memo data.
There are two choices:

1. move multiple memos to a single 1:1 record in your memo side
table, OR

2. normalize 1:N and have one row per memo, with a TYPE field that
indicates which kind of memo it is.

It might be tempting to implement #1 with a join in the form's
recordsource, but then you have to use a left join (otherwise
records with no memos yet won't show up in your recordset). This can
lead to interesting UI issues.

I think it's better, even with option 1, to use subforms for the
memos. The problem with #1 is that you might not want all your memos
in one place on your form, and you really don't want multiple
subforms based on the same table, as you can end up with write
conflicts (which are problematic for memo fields because of the
amount of data involved). Sure, you can work around it, but I just
think it's better to go the normalized route, as it makes things
much simpler.

All that said, I've never done either of these. I just use unbound
memo fields in apps where there's danger.
 
L

Lars Brownies

Thanks David! I already have a global search facility with which the user
can search through one or more of the important memo-fields in the app. So
I'm going to implement the unboud controls method.

Just to understand a bit more about memo corruption, I assume the following
actions can cause memo corruption:

- Editing a memo field simultaniously by more than one user
- Pasting text with all kinds of text formatting in it
- Network breakdown

Are there more causes?

Lars
 
D

David W. Fenton

Just to understand a bit more about memo corruption, I assume the
following actions can cause memo corruption:

- Editing a memo field simultaniously by more than one user
- Pasting text with all kinds of text formatting in it
- Network breakdown

Are there more causes?

I think the most important cause of problems is the first, and
that's why unbound memo editing basically solves most of the
problems. I'm not sure the second one is an issue at all, though.

One thing I forgot to mention:

To get the benefit of the unbound memo field as I described it, you
really need to save the record in the AfterUpdate event of the
unbound memo textbox. Otherwise, you haven't gained anything at all,
as you have a pending edit in the form's recordsource.

In most cases, this is not an issue (i.e., saving the record each
time you edit a memo), but if you are doing anything complicated in
the update events of the form, you could have issues. Also, if your
data file is replicated, it could cause issues, too -- not with the
memo fields (which are tracked by Jet replication separately from
the main record, naturally, since they are not stored with the
record), but with the other fields. This is less of an issue if
you've got field-level conflict resolution going on, but there are a
number of very easy ways to end up with row-level conflict
resolution (such as upgrading your replicated app from Jet 3.5).

But those are special considerations that aren't relevant to most
apps.
 
L

Lars Brownies

To get the benefit of the unbound memo field as I described it, you
really need to save the record in the AfterUpdate event of the
unbound memo textbox. Otherwise, you haven't gained anything at all,
as you have a pending edit in the form's recordsource.

I added it and it works good. I captured the Ctr-F and redirect them to the
global search facility.

Thanks again.

Lars
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top