Access 2016 Long Text truncation

[08211987]

Hi,
I had a thread a while back thread702-1783846 ["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.
Thanks!

 

[lameid]

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.

 

[strongm]

>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 …