Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Large Text (Memo) Fields (2000 SR1a)

    I'm toying with a couple of different approaches to a problem and would like to solicit some opinions.

    In one scenario, I have 2 tables with some 3500 records each that have about 40 memo fields between them. Many of the memo fields contain text in the 500 to 1000 character range. These tables are used to construct a Word document which is emailed to various people and saved. My concern with this approach is the problem of corruption in memo fields - there are typically 3-5 users in the database, and it could go as high as 10. In addition, I can't store any formatted text unless I use an Rich Text control for editing and display.

    The other scenario would be to actually store the Word document as an OLE object in the database, or alternatively store a link to it. Unfortunately in that approach I lose the power of Access forms and the ability to do finds and filters. I might also be trading Access corruption problems for Word corruption problems, and two people can't readily share a Word document.

    Any strong opinions out there as to which is the better choice, or for that matter, another way of tackling the problem? Thanks in advance.
    Wendell

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Large Text (Memo) Fields (2000 SR1a)

    I know nothing about replication, but would replication help with these tables?

    Pat

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Large Text (Memo) Fields (2000 SR1a)

    In spite of the deservedly bad rap memo fields get, our applications use them extensively and we haven't had problems. I haven't used the rich text control because I have a healthy scepticism about Access and any MS activex control, although I've used third party controls in Access without problems. I think the largest number of memos we have in any table is perhaps 10 or 12, but as long as you are careful and keep users from stepping all over each other, you should be OK.

    I would opt for this approach rather than storing the stuff in Word, but that may be just my Access bias showing through. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Large Text (Memo) Fields (2000 SR1a)

    Actually I support charlotte on this one (since when don't I?). In the past at a Steel Mill site we had an A97 database that got corrupt memo fields with about 6-8 users.

    When I built an A2K database with numerous memo fields (at the same site) we had about 6-10 users with no problems at all, and they would be hitting the database pretty hard at times. On this database I had record level locking, if this makes any difference.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Large Text (Memo) Fields (2000 SR1a)

    I am admittedly biased toward Access too - but handling text in Access does have it's shortcomings. The client in this case has an Access database that has evolved over the last 8 years or so and would like us to make it lots more user friendly and eliminate lots of duplicate effort. The initial information is collected in a Word document, put into the database, evaluated with a mathematical process, edited and then a final Word document is created and sent back to the originator and others who are interested. The organization is far flung - many only have sporadic internet connection via email.

    As far as the corruption issue, they are currently on 2000, and have had a couple of corruptions recently. The database (with about 3000 records) is 22MB. Unfortunately, many records contain memo fields that have been truncated at 254 or 255 bytes, which is one of the things that can happen if somebody gets sort happy while messing with the table. The bottom line is that the memo field things makes me just a bit uneasy. We could probably solve the problem by going to the MSDE or SQL Server since the latest version allows varchar fields up to 8000 characters, but Access is still likely to be a source of problems since when you do that, Access treats it as a memo field. Maybe I'm just worrying unnecessarily, but . . .
    Wendell

  6. #6
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Large Text (Memo) Fields (2000 SR1a)

    Personally I have never had a corruption issue with a memo field. I do know of a Memo Field bug (known by MS) involving Memo Fields when dealing with both Access and Foxpro.

    However, just to toss another suggestion to you, you could use a BLOB field. Storing the Word Doc's or even plain text in an OLE field, as just plain old data. This would involve a little more coding, but it would make unintentional goofing a lot less likely. You would probably end up creating 'temp files' to make it all work, but I just wanted to toss it out as an option, in case you were still debating....(Sorry for the late response......been busy elsewhere lately!)

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Large Text (Memo) Fields (2000 SR1a)

    Still getting the smoke out of your belongings, Drew? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  8. #8
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Large Text (Memo) Fields (2000 SR1a)

    Yep....or I think so. My wife and Mom have been doing most of the cleaning. I've been in charge of moving the clean stuff to the new place. Last night was the first night my wife and little girl slept in our new place. (I've been there for about 2 weeks now....women need so much stuff to 'survive'! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>).

    I went back about 12 pages on this forum, and I think that's enough. Only a few I could add anything too....am I dedicated or what? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Talking about Memo Fields.....what do you think of AccessD's Archive Search capability? Is that fast or what? (Just showed it to my co-worker. Ran a search for 'Collection' on the old archives (which haven't had any records added since January), and it took about 15 to 20 seconds (and only returned up to 500 records....had to do that to limit search time....), then ran the same search on the new archives, and it was almost instantaneous...maybe 1 second at most....(and returned over a thousand hits....)

    There's reliable Memo fields for ya! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Large Text (Memo) Fields (2000 SR1a)

    Glad to see you back - hope you didn't loose too much! What's the prognosis on your web site - I tried to find something on it the other day and found it unavailable?
    Wendell

  10. #10
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Large Text (Memo) Fields (2000 SR1a)

    I did lose a few things to water damage, but almost everything got salvaged.

    My website is up now....though I don't have anything on it, other then the AccessD archives.

  11. #11
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Large Text (Memo) Fields (2000 SR1a)

    <hr>Unfortunately, many records contain memo fields that have been truncated at 254 or 255 bytes, which is one of the things that can happen if somebody gets sort happy while messing with the table. <hr>
    This is a problem that I was not aware of! Does the problem only apply to sorting in the table or will sorting a memo field in a form cause truncation of the field as well? and if so is there a way to prevent sorting on a memo field?

    Peter

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Large Text (Memo) Fields (2000 SR1a)

    The sorting capacities of Access with respect to memo fields have increased with recent versions:

    <table border=1><tr><td>

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Large Text (Memo) Fields (2000 SR1a)

    Most of the issues in this area have to do with either doing GroupBy queries, doing action queries (ie make table query) while sorting on a memo field, or with exporting data to Excel or to an RTF format. Actually, I think many of them have been solved in 2000/2002 - and I'm not sure how long ago the truncation for this client occurred. They've been using Access since 2.0 apparently - but some of the truncated data had been entered in the last couple of years. It is a pretty easy thing to spot - all you have to do is calculate the length of the memo field, and only look at those that are 254 or 255 characters long. When data stops in the middle of a word or sentence, it's pretty obvious.
    Wendell

  14. #14
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    No problems? - Re: Large Text (Memo) Fields (...)

    Dear members of the board,
    and maybe HansV & Charlotte in particular, as well as other lucky people which never had (much) trouble with memo fields (<!post=remembering posts,255713>remembering posts<!/post> <!post=276371,276371>276371<!/post> <!post=249846,249846>249846<!/post>) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Regarding the memo field problems mentioned above...
    Did you, then, never needed action queries, GroupBy queries, transferring data to other applications (Excel,Word,...) and such? Or, if you did (inevitably, I suppose): didn't you have any of those problems either? Or did you find a solution to avoid them, handling the memo fields in a specific way?

    (Just trying to anticipate at some problems in a database project I'ld like to start up around here)
    Hasse

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    No problems? - Re: Large Text (Memo) Fields (...)

    I have frequently used totals (group by) and action queries on tables containing memo fields, and several of my databases contain an export to Word feature, also involving memo fields. Export to Excel usually doesn't involve memo fields, although the tables involved may have them. None of these have caused problems, so I haven't had to develop workarounds...

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •