Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing long strings (ACC97-SR2)

    I'm having huge problems with importing memo length strings into tables without truncating data.

    This also occurs if I copy the table and paste (structure and data) into a new table.

    Any time I'm moving this data, I'm losing some of it.
    Is there a better way of doing this? <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

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

    Re: Importing long strings (ACC97-SR2)

    There's definitely something wrong if copying the table truncates the memo field. You only have one memo field per table, right? Is this happening on all tables with memo fields or only on a few tables? When you say "importing" are you appending records from one table to another or importing from some other data source? Have you tried compacting and repairing the database? In Access 97 it sometimes took a compact, repair, compact combination to get it all cleaned up.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing long strings (ACC97-SR2)

    I did a 'compact repair'.
    While I was able to copy and paste successfully (to the best of my knowledge) without truncation, I still can't import without it.
    Then I had a new problem.
    The customer kept bringing in new records to insert into the system.
    Naturally, the Autonumber field was obsolete and I have no idea how to renumber the records.

    Yesterday I got frustrated and just crammed it all into Excel which was very easy to work with, but those report features of Access are really missed.
    In an effort to retain that nifty hard copy, I tried to re-import the finished version back into Access. Bummer! More truncation.

    When going through the import wizard, I don't have an option to declare the field datatype, so I can't tell if it sees it as a memo field.
    So, I linked the table. Set up the report to work with the linked table.
    Then I got this simple message... I know what it means.
    Memo type too long for text box, how do I work around this?

    Worse yet...linking the table seems to have truncated the field contents also...
    What is the CORRECT way to handle records with memo fields? <img src=/S/scream.gif border=0 alt=scream width=15 height=15>
    Attached Files Attached Files

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

    Re: Importing long strings (ACC97-SR2)

    Where are you importing it from? Excel doesn't handle memo fields, at least not Excel 97 and earlier. You can actually export a memo field to Excel, but if you try to look at it, it will get truncated to fit into a cell. If you're importing from a text file, then the simple way is to create a table the shape of the data you're importing and set the datatype there. Then when you import, tell the wizard to append to the existing table instead of creating a new one.
    <hr>Naturally, the Autonumber field was obsolete and I have no idea how to renumber the records.<hr>
    This suggests you aren't using Autonumber fields properly. Don't ever rely on them to be continuous. Autonumbers are there to provide a unique key that can't be misspelled and doesn't ever need editing, not to provide a handy mnemonic for users looking for a record. If you want the records to come up in a particular order, that's what indexes and queries are for.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing long strings (ACC97-SR2)

    <font color=blue><Where are you importing it from? Excel doesn't handle memo fields</font color=blue>

    I exported it from Access to Excel and all of the data was intact.
    I had to renumber some last minute records that were entered,
    and Excel was the only way I knew how to insert things in the middle of the stack.
    When I tried to bring it back in for sake of reports only, I was trying to make a new table.
    That may explain why I wasn't allowed to define the datatype.

    There is a primary key and an entry number field.
    Since the data must remain in the order it is submitted, I have to number them in order to provide a sort function for the report.
    The data can not be sorted in any other way.

    How would you put this in an order for a report?
    Groups:
    Orange-2037c
    <font color=red>4568</font color=red>
    325
    Watermelon-<font color=red>8456</font color=red>
    987
    1789l
    Banana-156p
    987o
    0652
    <font color=red> Red indicates new records </font color=red>

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

    Re: Importing long strings (ACC97-SR2)

    <hr>The data can not be sorted in any other way.<hr>
    Data is *not* sorted in tables, data is sorted in queries and in reports. It doesn't matter what order the records are in the table. If you need to keep them in a particular sort order, add a SortBy field to the table and stick a number in it. Then use that field in sorting and grouping in a report to set the sort order, but don't try to make it a meaningful number because the maintenance problems are horrendous. Your truncation problem is caused from exporting and importing the data between Access and Excel and you are *not* going to get around that in Office 97 or Office 2000. Since you said the data was OK in Excel, that means you looked at it, and that's all it takes for Excel to truncate the memo field.

    I'm sorry, but I can't see any logic in your "records" and I can't even figure out what is supposed to be a record. Is the number a record, or is the "group" a record or what, and which are you trying to order by? If its the numbers, then just sort them in sorting and grouping, unless they aren't supposed to be in numeric order. And, by the way, if your numbers are actually alphanumeric strings, then they're going to sort alphabetically, which may give you unexpected results.
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing long strings (ACC97-SR2)

    I realize that data is sorted in the queries, my bad on the syntax.

    This data is out on a maiframe somewhere, but I can't get anyone to send me the e-list because of a security clearance, so we are forced to rekey the data.
    Frustrating is an understatement

    In regards to the truncated data, your first example was the correct one.
    If a table is set up in advance with the correct fields and datatypes defined, they should accomodate the data.
    (e.g. I set up a memo field in the receiving table and it worked like a charm.)

    This project is over...thanks for the help.

Posting Permissions

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