Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2015
    Posts
    1

    Answered: access 2007 memo field error

    Hi

    Access 2007 memo field corruption

    I am currently supporting a Access database. The database has a Access 2007 Front end which in saved on the desk top for multiple users and the backend sits on an Access 2007 database on the office server (Dell Xeon 490). Users input and view records in the form on a tracker and data table.

    The problem field is a memo field and we know users sometimes incorrectly try to access the same record at the same time and if done in normal circumstances they will be locked out from this record.

    However recently we have has “#Error” values appearing on the memo field and therefore the system is loosing the data. Anyone else had similar problems with Access 2007 and know the exact cause of this error and recommend any actions to prevent this? This also just affects the same memo field each time. The significance of this memo field is text from the internet is copied on the field and saved in Access 2007 database.

    I would be grateful if anyone could provide some guidance on this issue. I am not sure if recent server slowdown and performance issues is contributing to this error occurring on an access DB.

    Thanks

  2. Best Answer
    Posted by muslihsadri

    "Some things that may say you about corruption and methods to get rid of it, if they can't assist use more powerful solution for complicated cases of access database corruption https://access.recoverytoolbox.com/ Recovery Toolbox for Access

    Access uses a pointer to another location for the data in large fields (memo, hyperlink, or OLE Object). If the pointer is written incorrectly, the field displays garbage.
    To address this kind of corruption, delete the memo field from your table. Compact to get completely rid of it:
    • In Access 2010, Compact and Repair Database on the Database Tools ribbon.
    • In Access 2007, click Office Button (top left) | Manage | Compact.
    • In Access 95 - 2003, Tools | Database Utilities | Compact.
    Then create the memo field again. If the data is important, you may be able to link to an older backup (File | Get External | Link in Access 95 - 2003; External Data | Import in Access 2007 and 2010), create a query joining the current and older copy on the primary key, and then change it to an Update query to update the now blank memo field with the contents of the old one.
    If the strange characters appear only in the query, not when you view the table, this is not a corruption. This occurs when JET is unable to determine the data type of the query field, and is triggered by lots of situations. Solutions for this non-corruption issue include:
    • Add a primary key to your table.
    • Explicitly typecast the field.
    • Do not concatenate text fields that generate more than 255 characters."


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    hmmm, never seen this error on the memo fld.
    And you checked to see if the form is mapped to the correct memo field?
    and
    you are NOT trying to query on it. You cant search in memo fields.

    Plus, it the field on the form, bound to a table or a query?

  4. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by endrezsombor
    The database has a Access 2007 Front end which in saved on the desk top for multiple users
    First off, does the above mean that each user has a copy of the Front End on their desk top...or are all users sharing a single copy of the Front End?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #4
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    each user has a copy of the Front End
    they share the Backend tables.

  6. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Despite their sometimes bad reputation, Memo Fields are not evil...as long as they are used for their intended purpose, which is to enter 'free-hand,' if you will, notes, comments, descriptions, etc.! Working primarily in a Heath Care environment, I've used Memo fields extensively for a number of years, without ever having a problem. The secret is to follow one single rule:

    Never, never, never place data in a Memo field if there any possibility that you will ever need to search, sort, parse or in any other way manipulate the data!

    I'm not ruling out the possibility that your recent server performance issues might be the problem, possibly causing the (I'm guessing) large amount of data to become corrupted, when it's being written to disc, but my first guess, here, is that the problem is coming because you're copying text from the internet; I have to wonder if special characters, not visible when viewing the data online, are also being copied and pasted into the Memo Field, causing the problem. Not really sure what you could do, if this is the case, as a workaround.

    Linq ;0)>
    Last edited by Missinglinq; 06-24-15 at 13:11.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #6
    Join Date
    Jun 2015
    Posts
    1
    Provided Answers: 1
    Some things that may say you about corruption and methods to get rid of it, if they can't assist use more powerful solution for complicated cases of access database corruption https://access.recoverytoolbox.com/ Recovery Toolbox for Access

    Access uses a pointer to another location for the data in large fields (memo, hyperlink, or OLE Object). If the pointer is written incorrectly, the field displays garbage.
    To address this kind of corruption, delete the memo field from your table. Compact to get completely rid of it:
    • In Access 2010, Compact and Repair Database on the Database Tools ribbon.
    • In Access 2007, click Office Button (top left) | Manage | Compact.
    • In Access 95 - 2003, Tools | Database Utilities | Compact.
    Then create the memo field again. If the data is important, you may be able to link to an older backup (File | Get External | Link in Access 95 - 2003; External Data | Import in Access 2007 and 2010), create a query joining the current and older copy on the primary key, and then change it to an Update query to update the now blank memo field with the contents of the old one.
    If the strange characters appear only in the query, not when you view the table, this is not a corruption. This occurs when JET is unable to determine the data type of the query field, and is triggered by lots of situations. Solutions for this non-corruption issue include:
    • Add a primary key to your table.
    • Explicitly typecast the field.
    • Do not concatenate text fields that generate more than 255 characters.

Posting Permissions

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