Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2010

    Unanswered: MDB database field limited to 64 or 255 characters


    I'm trying to work with an existing database table with some fields already defined. One of them seems to be limited to only 64 characters, so when I try to insert a new item into that field, it gets truncated to 64 characters. I've tried deleting the column in Access and re-inserting a column, but it's limited to 255 characters too. The data I'm trying to store is a text string of 256 characters, so I'm 1 character short. Any ideas on how to increase the character limit? I've read about switching from text to memo field data type, but I can't figure out how to switch. Also, will sql queries work once the change is implemented?

    Edit/Delete Message

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    Short of making it a memo type field (which you can't sort/filter on in a query), I'd personally try to keep it a text field minus the 1 character (since text fields are limited to 255 characters - no exceptions.) Memo fields don't give you much flexibility when it comes to searching on data in them such as text type fields do but they give you unlimited data (to a point). Regardless, you must change the field types on the 'source' table in the source mdb or wherever it's located (ie. not on a linked table).

    To switch from text to memo, you just change the field type in the table design. If going from a memo to a text field type, you'll most likely get prompted that you'll lose data. Remember to refresh any frontend mdb's which might have the tables linked into them.

    If all else fails, just design a new table (or copy/paste the table without the data if not a linked table), or copy the field names from the old table and paste into the new table. Before saving the new table, change the field types to the ones you need. After you're done with the new table and have saved it, run a simple append query to append the data from the old table to the new table. Then delete the old table and rename the new table as appropriate.
    Last edited by pkstormy; 04-05-10 at 23:13.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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