A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Record Corruption



 
 
Thread Tools Display Modes
  #11  
Old December 15th, 2009, 11:50 PM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Record Corruption

"JamesJ" wrote in
:

I'm considereing changing the field(s) back to plain text but need
to remove any formatting or the formatting "characters" will carry
over in the plain text.


I don't believe that increases the risks involved with memo fields
at all.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #12  
Old December 16th, 2009, 12:22 AM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Record Corruption

James

Any chance there's a wireless LAN involved here somewhere? That can also
cause problems...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"JamesJ" wrote in message
...
No multiple users and the db is split.
Maybe a short loss of power might have shut my computer off but I'm not
sure.

Next time it occurs I'll delete the memo field data at the table level and
see if that
will, at least, allow me to keep the record.

Thanks,
James

"Arvin Meyer [MVP]" wrote in message
...
Memo fields are more prone to corruption because they are not actually
stored in the same table, just a pointer to them. Very often, just the
memo is corrupt, not the entire record. If you go in the table, you can
sometimes delete just the memo field data.

Most corruption is caused by either users inappropriately turning off
their computer or by a bad network card. Corruption can also be cause by
not having a split database if there are multiple users.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"JamesJ" wrote in message
...
Access 2007 sp2.
I have a rich text memo field in a table with 2 other fields.
Sometimes when I make changes to the memo field I am told that
access can't update the record because it is being "used by another
user"
Not sure of the entire error message but I'll get it because it will
happen again.

From that point on I am unable to make changes the that record. I must
delete the record and create a new one.

Is any one else having this problem?

Must be some limit to something in the rich text memo fields.

James





  #13  
Old December 16th, 2009, 01:23 AM posted to microsoft.public.access.forms
JamesJ[_5_]
external usenet poster
 
Posts: 32
Default Record Corruption

That's why I put the address there. I wasn't sure.
Anyway. I already have a backend db with all my data.
I'm assuming you mean I should create a new db and store
the rich text memo field(s) there?
If so, how might I link the memo field with the pertinent record?

James

"David W. Fenton" wrote in message
36.98...
"JamesJ" wrote in
:

I also found the following to be useful.

http://www.devx.com/dbzone/Article/42850/1954?pf=true


Overall a fairly good article, but I stumbled over this advice:

store them in a separate backend database

...as a way of avoiding problems with memos. I wonder if the person
writing the article is an xBase refugee, where each DBF file is a
data table and referred to colloguially as a "database," even though
it's actually a data table.

The advice as it stands is insanely wrong, as there's absolutely no
danger of a corrupted memo pointer in one table in an Access data
file somehow polluting the other tables and corrupting their data.
So, I'd definitely change that advice to:

store them in a separate data table

...which will be sufficient to isolate memo corruption from the main
data records.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


  #14  
Old December 17th, 2009, 05:37 AM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Record Corruption

"JamesJ" wrote in
:

I'm assuming you mean I should create a new db and store
the rich text memo field(s) there?


No, not at all. The whole point of my post was to emphasize that you
don't need a new back-end database, but just a new table in the same
back end.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #15  
Old December 17th, 2009, 05:38 AM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Record Corruption

"Jeff Boyce" wrote in
:

Any chance there's a wireless LAN involved here somewhere? That
can also cause problems...


....and, of so, you shouldn't be using Access across it with a
Jet/ACE back end, as that way lies madness (and perpetual
corruption). WiFi is simply not reliable enough for use with Access,
unless you're running the app on a remote terminal server, or if
your back end is SQL Server or some other server database, where all
the data processing happens on the server.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #16  
Old December 17th, 2009, 12:41 PM posted to microsoft.public.access.forms
JamesJ[_5_]
external usenet poster
 
Posts: 32
Default Record Corruption

This is only to segregate the memo field from the rest of the record?
Even now I find that when the memo field gets corrupted I can
go to the table and delete the text from the memo field and the
the record is ok. I just need then to put the data back into the memo field.

If that's the case, even replacing the record wouldn't be much more work
than replacing the data in the memo field.
It seems that the corruption of the memo field occurs when there is a large
amount of data in it.
Most of this data comes from my dvd library and the dvds that are tv shows
with many show titles and descriptions and I'm cramming all the information
about the dvd into the memo field.
So a text field wont get it. 255 characters doesn't go very far.

Also I know I believed I asked this in this thread but before I make any
rash
changes to my db I want to find out if anything can be done to actually
minimize the chance of corruption in memo fields.

1) It appears that a large amount of data in memo fields seems to promote
corruption.
Are my observations true?
2) If I use only plain text as apposed to rich text, will that minimize the
chance for corruption?

James

"David W. Fenton" wrote in message
36.97...
"JamesJ" wrote in
:

I'm assuming you mean I should create a new db and store
the rich text memo field(s) there?


No, not at all. The whole point of my post was to emphasize that you
don't need a new back-end database, but just a new table in the same
back end.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


  #17  
Old December 18th, 2009, 01:57 AM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Record Corruption

"JamesJ" wrote in
:

This is only to segregate the memo field from the rest of the
record? Even now I find that when the memo field gets corrupted I
can go to the table and delete the text from the memo field and
the the record is ok. I just need then to put the data back into
the memo field.


But it's very often the case that you *can't* get the data out of
the rest of the record, and that's why segregating memo fields in
separate tables is helpful.

If that's the case, even replacing the record wouldn't be much
more work than replacing the data in the memo field.


In my experience, that has *not* been the case -- the whole record
was lost. And even when the other data was available, you still have
to create a new record to hold the recovered data, since the corrupt
pointer cannot be removed from the record. If the primary key of the
deleted record matters, you have to append the data, retrieve the
new PK value and then update all child records of the old record to
point to the new record, and only then can you delete that record.

So it's a pain if you have memo fields in tables that participate as
parent records in parent/child relationships between tables. If the
table with memos in it is only a child record, and its PK is
immaterial, then you're right, it's no big deal. But if not, it's
quite a bit of work, seems to me.

It seems that the corruption of the memo field occurs when there
is a large amount of data in it.


I don't think that's true. It can happen with any amount of data.
What matters is the editing state when the event that interrupts the
edit occurs.

Most of this data comes from my dvd library and the dvds that are
tv shows with many show titles and descriptions and I'm cramming
all the information about the dvd into the memo field.
So a text field wont get it. 255 characters doesn't go very far.


I would never argue for avoiding memo fields entirely for data that
exceeds 255 characters.

Also I know I believed I asked this in this thread but before I
make any rash
changes to my db I want to find out if anything can be done to
actually minimize the chance of corruption in memo fields.

1) It appears that a large amount of data in memo fields seems to
promote corruption.
Are my observations true?


Not in my experience.

2) If I use only plain text as apposed to rich text, will that
minimize the chance for corruption?


I don't believe so. I have never seen any evidence that the content
of the memo field has anything to do with the corruption of the memo
pointer.

One less drastic step that can be taken to minimize the possibility
of memo field corruption is to edit the memos what I call
"semi-bound". That is, on your forms, remove the control source of
the text box that is bound to your memo field, and then in the
form's OnCurrent event, load the data into the unbound text field
from the form's underlying recordsource:

Me!txtMemo = Me!Memo

Then in the AfterUpdate of this unbound control, write it back:

Me!Memo = Me!txtMemo
Me.Dirty = False

It's important to save the record immediately after writing to the
memo field or you've gained nothing by going with an unbound
control.

But the truly reliable method is to move the records to a separate
table.

On the other hand, the above requires no restructuring of the data
and will minimize the time at which your memo fields are vulnerable.

All that said, if you're having frequent corruptions, you've got
something wrong with your computer or network and rather than
spending all this time on workarounds, you should fix the underlying
problem that's leading to the memo-pointer corruption, particularly
given that other kinds of data can also get corrupted and those
aren't necessarily as easily worked around.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #18  
Old December 18th, 2009, 04:41 AM posted to microsoft.public.access.forms
Arvin Meyer [MVP][_2_]
external usenet poster
 
Posts: 2,310
Default Record Corruption


"JamesJ" wrote in message
...
This is only to segregate the memo field from the rest of the record?
Even now I find that when the memo field gets corrupted I can
go to the table and delete the text from the memo field and the
the record is ok. I just need then to put the data back into the memo
field.

If that's the case, even replacing the record wouldn't be much more work
than replacing the data in the memo field.
It seems that the corruption of the memo field occurs when there is a
large amount of data in it.


The corruption is not data centered. Practically all corruption occurs
because there is a loss of connectivity. Either a machine is turned off
without backing out, or packets are dropped due to a bad network card, or
even a hard drive is asleep when you are trying to write to it.

I have an Access 97 database with more than 6 thousand records in a memo
field. I'd guess the average record is about 2 to 3 K. The largest records
are more than 64 K. It was converted from an Access 2.0 database, 10 years
ago, and has never corrupted in either version, so size is definitely not
the cause.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


  #19  
Old December 18th, 2009, 12:25 PM posted to microsoft.public.access.forms
JamesJ[_5_]
external usenet poster
 
Posts: 32
Default Record Corruption

In my case none of the things you mentioned occurred.
There is a possibility that power might have been interrupted
and the computer shut down but I can't recall that happening.
In that case wouldn't all my memo fields become corrupt?
I'm not on a network and don't share the db with anyone and
I have no settings allowing my computer to sleep.
I to never had problems with memo field corruptions when I
ran my db with access 2003. Only since I've been uising ac 2007
has this problem poped up.

Have you converted your db to 2007 format, ran it for a while
and see if the memo fields become corrupted?

James

"Arvin Meyer [MVP]" wrote in message
...

"JamesJ" wrote in message
...
This is only to segregate the memo field from the rest of the record?
Even now I find that when the memo field gets corrupted I can
go to the table and delete the text from the memo field and the
the record is ok. I just need then to put the data back into the memo
field.

If that's the case, even replacing the record wouldn't be much more work
than replacing the data in the memo field.
It seems that the corruption of the memo field occurs when there is a
large amount of data in it.


The corruption is not data centered. Practically all corruption occurs
because there is a loss of connectivity. Either a machine is turned off
without backing out, or packets are dropped due to a bad network card, or
even a hard drive is asleep when you are trying to write to it.

I have an Access 97 database with more than 6 thousand records in a memo
field. I'd guess the average record is about 2 to 3 K. The largest records
are more than 64 K. It was converted from an Access 2.0 database, 10 years
ago, and has never corrupted in either version, so size is definitely not
the cause.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

  #20  
Old December 18th, 2009, 12:29 PM posted to microsoft.public.access.forms
JamesJ[_5_]
external usenet poster
 
Posts: 32
Default Record Corruption

In fact when the memo field gets corrupt I'm able to copy the contents
of the memo field to the clip board delete the data form the memo field
at the table level and then paste it beck. The record seems ok then.
I can't say this happens frequently. More like occasionally.

This seems to have started with ac 2007.
I'm really at wit's end here. I have no idea what's causing this.

James

"David W. Fenton" wrote in message
36.90...
"JamesJ" wrote in
:

This is only to segregate the memo field from the rest of the
record? Even now I find that when the memo field gets corrupted I
can go to the table and delete the text from the memo field and
the the record is ok. I just need then to put the data back into
the memo field.


But it's very often the case that you *can't* get the data out of
the rest of the record, and that's why segregating memo fields in
separate tables is helpful.

If that's the case, even replacing the record wouldn't be much
more work than replacing the data in the memo field.


In my experience, that has *not* been the case -- the whole record
was lost. And even when the other data was available, you still have
to create a new record to hold the recovered data, since the corrupt
pointer cannot be removed from the record. If the primary key of the
deleted record matters, you have to append the data, retrieve the
new PK value and then update all child records of the old record to
point to the new record, and only then can you delete that record.

So it's a pain if you have memo fields in tables that participate as
parent records in parent/child relationships between tables. If the
table with memos in it is only a child record, and its PK is
immaterial, then you're right, it's no big deal. But if not, it's
quite a bit of work, seems to me.

It seems that the corruption of the memo field occurs when there
is a large amount of data in it.


I don't think that's true. It can happen with any amount of data.
What matters is the editing state when the event that interrupts the
edit occurs.

Most of this data comes from my dvd library and the dvds that are
tv shows with many show titles and descriptions and I'm cramming
all the information about the dvd into the memo field.
So a text field wont get it. 255 characters doesn't go very far.


I would never argue for avoiding memo fields entirely for data that
exceeds 255 characters.

Also I know I believed I asked this in this thread but before I
make any rash
changes to my db I want to find out if anything can be done to
actually minimize the chance of corruption in memo fields.

1) It appears that a large amount of data in memo fields seems to
promote corruption.
Are my observations true?


Not in my experience.

2) If I use only plain text as apposed to rich text, will that
minimize the chance for corruption?


I don't believe so. I have never seen any evidence that the content
of the memo field has anything to do with the corruption of the memo
pointer.

One less drastic step that can be taken to minimize the possibility
of memo field corruption is to edit the memos what I call
"semi-bound". That is, on your forms, remove the control source of
the text box that is bound to your memo field, and then in the
form's OnCurrent event, load the data into the unbound text field
from the form's underlying recordsource:

Me!txtMemo = Me!Memo

Then in the AfterUpdate of this unbound control, write it back:

Me!Memo = Me!txtMemo
Me.Dirty = False

It's important to save the record immediately after writing to the
memo field or you've gained nothing by going with an unbound
control.

But the truly reliable method is to move the records to a separate
table.

On the other hand, the above requires no restructuring of the data
and will minimize the time at which your memo fields are vulnerable.

All that said, if you're having frequent corruptions, you've got
something wrong with your computer or network and rather than
spending all this time on workarounds, you should fix the underlying
problem that's leading to the memo-pointer corruption, particularly
given that other kinds of data can also get corrupted and those
aren't necessarily as easily worked around.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.