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. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|