Results 1 to 5 of 5
  1. #1
    winginit is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2016
    Posts
    9

    Memo field corruption - best way to convert memo fields to text fields

    My Access 2007 database has about 100 tables, and many of them contain multiple (1-5) memo fields. In my current single-user (just me) environment these memo fields pose no problem.

    However, I have heard (and experienced) problems--dreaded memo field corruption--when in my previous company we had multi-users. I have heard/read that the solution is to eliminate all memo fields in the primary back-end, create a new a new accdb with only memo fields, and link those back to their respective tables. I don't know where I read that, and don't see how that would solve the problem. But even assuming that it would solve the problem, my question then is how to do that. I have a LOT of memo fields and I would like to leave the field name the same in the primary tables so that my numerous queries wont puke when they are run.



    Any help for this novice-hack would be most appreciated (though upsizing to sql server or hiring a real developer / db administrator is not in the cards at the current time.

    Thanks to anyone with some thoughts on this!

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    "mdb" databases were notorious for corrupting memo fields. I have not experienced it in accdb, although that is not to say that Microsoft has fixed the problem, it is a risk you have to take. Eliminating all memo fields is not an option as there is no other way to store large text fields.

    I have at times created a "comments" table to store my memo fields which is then tied to all other tables thru the PK and a "type" and whatever else is needed to identify each comment record. This doesn't eliminate the problem but at least if corruption occurs it is only on that one table and my main data remains intact.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would like to leave the field name the same in the primary tables
    Don't understand that statement - if you were to move the memo fields to another table, that field would no longer contain data in the original table, so the queries wouldn't work as intended anyway.
    Regardless, I've used memo fields (only as required) for many years and cannot recall any corruption of them, but that might be due to how I used them**. IMHO, you're more likely to experience corruption through practices such as altering design on an active shared database, forcing shutdowns with Task Manager, network hiccups, etc., which is why regular backups are important. It would be enough to move the memo fields to their own table in the same backend database if that provided some level of comfort. I see no compelling reason to move them to an entirely different db. What I would never do is link memo fields between tables or attempt to sort on a memo field in a query. If sorting is a requirement, you should add a calculated field to the query, sorting on the Left(n) characters of the same memo field without showing it in the form or report results.

    **I did not allow the memo field to be continually edited/appended to. For example, if successive comments need to be captured during a work phase, it is far better to capture each comment in a table of comments where the comment field is a regular text field, all the while capturing the date and user id of the writer rather than append to a memo field and losing edit history. Even if I needed a memo field for this, my approach would be to not repeatedly append to it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    winginit is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2016
    Posts
    9
    Thank you. Clear that there is no silver bullet but helpful to know that accdb is less susceptible to the corruption. The PK + type would also, as you point out, minimize the damage if corruption did occur.

  5. #5
    winginit is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2016
    Posts
    9
    Thanks, good advice. It seems what I could do is create a table of comments, with 3 fields: the source table's PK, the source table name, and the comment (memo) field. I could then populate that one table with the data from all my many memo fields currently in the original source tables. Then convert the original source tables to Text 255, which would truncate them. Queries would still work fine, with the only limitation that only the first 255 chars of the data would appear. To view the remainder of the data would just be a matter of drilling down to the Memo Table. Thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Convert memo field to multiple text fields
    By hertfordkc in forum Access
    Replies: 3
    Last Post: 11-14-2014, 06:59 AM
  2. Replies: 5
    Last Post: 06-19-2012, 10:46 AM
  3. Memo -- SQL text fields
    By jim56 in forum Access
    Replies: 0
    Last Post: 12-01-2011, 03:54 PM
  4. Replies: 3
    Last Post: 03-05-2011, 12:46 PM
  5. Memo field causes corruption?
    By evander in forum Database Design
    Replies: 1
    Last Post: 07-03-2010, 08:37 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums