Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Memo fields in Access/MSDE (Access 2000)

    One hears of problems that Access has with Memo fields. Does an MSDE backend handle Memo fields better?

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

    Re: Memo fields in Access/MSDE (Access 2000)

    The equivalent in MSDE/SQL Server is a TEXT field. They are essentially the same as the Access memo field but corruption doesn't seem to be the issue in SQL Server that it is in Access.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo fields in Access/MSDE (Access 2000)

    Funny you should mention that - when I upsized an app recently to SQL 7 I had a memo field in one of the tables. The upsize made it text data type in the SQL table. But then I couldn't edit any of the records that had a value in that field. I eventually ended up changing the data type to varchar and limited the length to 1000. Works fine now.

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

    Re: Memo fields in Access/MSDE (Access 2000)

    I'm a little puzzled by that - we edit text SQL fields in an Access front-end with regularity, and haven't encountered any problems. There are issues however with running queries, sorting and other functions where they will give problems. Were you by chance trying to sort on the text field?
    Wendell

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo fields in Access/MSDE (Access 2000)

    Ah, just a thought. Can one correctly sort on these 'long' text fields in MSDE environment rather than the Access truncation first behaviour?

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo fields in Access/MSDE (Access 2000)

    How did the conversion of the back end go? Any problems?

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo fields in Access/MSDE (Access 2000)

    Yes, I was puzzled too. No, not sorting on this field as it has upward of 700 characters in some records. It is a design problem that if I had to do over I would do very differently. It's basically a comments field that the users use to record when an exception to the hr rules of leave taking occur. e.g. vacation time is used for a sick day when an employee no longer has sick time. Now, I would put it in a subform with each occurence a separate record. But I don't see a way of converting the existing data to that format without great pain and possible data loss. Sorry, I'm rambling. Thanks for sharing your experiences.

  8. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo fields in Access/MSDE (Access 2000)

    Not smoothly. There were many problems. I had to abandon the upsize wizard after many tries. It kept refusing to upsize certain tables. I finally used the data import tools in SQL and that worked better. I still had to do a lot of tweaking to the tables. Then we had problems backing up and restoring the db. And I haven't even looked at trying to optimize the queries yet, though the users say they don't notice any performance loss. If you're considering an upgrade give yourself at least two weeks.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo fields in Access/MSDE (Access 2000)

    Thanks for the info.

    ps. I've never been enamoured of the Access wizards for most things, they give a start but usually only that.

Posting Permissions

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