Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Access 2016 Long Text truncation

Access 2016 Long Text truncation

Access 2016 Long Text truncation

I had a thread a while back thread702-1783846: Access 2016 append query "long text" field is being truncated ["Access 2016 append query "long text" field is being truncated"] and was helped with a workaround. Just curious if anyone knows if Access 2016 has been provided a fix for this issue and would know the release number? I am continuing to workaround the issue and it just adds extra work and queries.

Appreciate any information of how I could find this out myself or if any one knows.

RE: Access 2016 Long Text truncation

The fundamental problem is when Access does an import it only scans so many rows to determine the data type of each field / column. It can make bad assumptions this way. In this case it is assuming Short text which has a max size of 255 characters instead of Long text. Because it decided wrong, it truncates when reading the data before writing to the table. The workaround for text files is to use an import specification to define the data types properly and use it to import. There is no such luck for Excel. The most typical bad decision involving scan rows where there are a bunch of rows with all numbers in it but it is a text column. Access errors non-numeric values importing because it assumes a number. For this case in Excel, if you format all the rows in the column with Text format, that will win out and "fix" it. I know of no such workaround/fix for long text.

In your original thread, Andy suggested reading from Excel directly via code. If you read the cell values with Excel automation, it is not on your back making a wrong data type decision and it should simply work albeit slower.

Another thought, automate excel to save the data as a CSV or whatever delimited text file. Then import that text file with an import specification into Access.

Having said all that there are registry settings that control how many lines Access scans to make its guess. MS likes to remove those KB articles. You might find reference to them in a thread here - something I don't remember cold nor have permission to explore here. I try to put details to things in this forum because of the helpful MS KB phenomenon - I suspect I may have listed the text file version, finding the Excel version should be an intuitive parallel hierarchy navigation.

Another solution too would be to change the output process that creates the file to ensure it outputs a few records with long text at the top of the file instead of you inserting bad data or simply put a few long records up top. You could even go so far as to make an Excel macro to sort it and save it somewhere for import.

As for it getting fixed? I was first bit by the numbers in a text field thing in the late 90's so I think this gets filed under Working As Designed (WAD) and it will persist as long as the product does.

RE: Access 2016 Long Text truncation

>gets filed under Working As Designed

Sadly so - and Microsoft get pretty obstinate about that (at least they certainly used to). I remember years ago trying to report a bug to Microsoft concerning one of their early email products - coming from an era that still used text screens it assumed the maximum width of an email should be 80 characters and inserted CRLFs at char position 79 to ensure this. Only it was a dumb insertion - because it was a) actually an overwrite and b) didn't check if there were already characters there. The overall result was that it actually damaged layout and overwrote data ... However, turned out that this was 'by design' (albeit a really bad design) and as a result COULD NOT be reported as a bug …

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close