Using VBA to import text file into Access truncates Long Text fields

Question

I have set up a saved import spec called "Copy Import" to import a text file into Access; the fields are set up as Long Text, and when I manually run the saved import in Access, everything works just fine.

When I run this line of code as part of a click button on a form, the import truncates data to 255 characters without returning an error.

DoCmd.TransferText acImportDelim, "Copy Import", "Copy", "H:\data-full-export.txt", True

What can I do to include this code, but get all characters in my fields?

This is not an answer but might help lead you in the right direction. I have had MANY issues with access and long text fields. stackoverflow.com/questions/42908563/… — Pants
An alternative is to write your own VBA sub using the AppendChunk() function. It exists in both ADO and DAO. I've had hit-and-miss success with other techniques in Access, but my own code reading files and using AppendChunk() has work every time when other wouldn't. — C Perkins

HesE HesE · Accepted Answer · 2017-05-08T12:59:19

Thanks for the help. It turns out that the issue wasn't with the import, but rather with functionality within the query designed to search and replace within the Large Text field. I removed that code and everything ran fine. I'll be building a macro within excel to do the find and replace there instead.

Using VBA to import text file into Access truncates Long Text fields

1 Answers