Data Import & Export

How to Export Access Long Text Fields to UTF-16 Without Data Loss

Nimra Malik Oct 19, 2026 869 views

Question details

The user needs to export Microsoft Access tables containing Long Text fields to a UTF-16 text file without truncating data or corrupting records.

How to Export Access Long Text Fields to UTF-16 Without Data Loss

Product: Microsoft Access
Device & OS: not provided
Scenario: Exporting database records with extensive text entries to a text file using UTF-16 encoding.
Observed behavior: Using DoCmd.TransferText fails on fields over approximately 794 characters, and the Text File Export Wizard omits subsequent record data during Unicode (UTF-16) exports.

Before you start

Verify if your destination application strictly requires UTF-16 encoding, as using standard default encodings can often bypass this native Access limitation completely.

Solution 1Recommended

Use a Custom VBA Script with DAO Recordset2

Bypass the built-in Access export limitations by writing records directly to a text file using VBA and DAO.

Microsoft Access natively limits Long Text fields when processing certain bulk operations like DoCmd.TransferText. Using a custom VBA script ensures that large text strings are handled correctly without truncation.

Open the VBA Editor

Press ALT + F11 in Microsoft Access to open the Visual Basic for Applications (VBA) editor.

Create a New Module

Navigate to Insert > Module from the top menu to create a blank workspace for your script.

Write the Export Script

Use the DAO Recordset2 object to open your target table. Write a loop that iterates through each record, manually wrapping fields in quotation marks and separating them with delimiters.

Output to Text File

Use standard VBA file handling (such as the Open statement or FileSystemObject) to write the concatenated strings to a text file, explicitly specifying UTF-16 encoding.

Use a Custom VBA Script with DAO Recordset2

Custom Delimiters: When writing the VBA script, ensure you properly escape any existing quotation marks within your Long Text fields to maintain data integrity in the output file.

Solution 2

Export Using Default Encoding as a Workaround

If UTF-16 is not strictly mandatory, use the default encoding to prevent the native export wizard from corrupting Long Text fields.

Free Microsoft Office alternative

Discover WPS Office for Your Document and Data Needs

While Microsoft Access requires VBA workarounds for database exports, WPS Office offers a free, lightweight, and highly compatible suite for your everyday document, spreadsheet, and presentation tasks. Enjoy a familiar user interface and seamless migration from Microsoft Office without hefty subscription fees.

1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
2. Install and Launch: Run the installer. The lightweight setup process takes only a few minutes before you can launch the application.
3. Open Your Office Files: Drag and drop your existing Microsoft Office files directly into WPS Office to continue your work seamlessly.

Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Lightweight installation that runs smoothly on older or less powerful devices.Familiar tabbed user interface, requiring zero learning curve for transitioning users.Cost-effective alternative providing powerful data management tools for free.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Access truncate Long Text fields during export?

Microsoft Access natively limits Long Text (formerly Memo) fields when processing certain bulk operations like DoCmd.TransferText. It often cuts them off at approximately 794 characters or causes data misalignment when specifically exporting to UTF-16 formats.

What is the DAO Recordset2 object in VBA?

The Recordset2 object in Data Access Objects (DAO) is designed to handle complex data types in Access databases, including multi-valued fields and Long Text. It is essential for accurately reading and exporting large data strings in VBA without losing information.

Can I use UTF-8 instead of UTF-16 for Access exports?

Yes, Access generally handles default encodings and UTF-8 more reliably than UTF-16. If your target application or version control system supports it, switching to a different encoding can quickly resolve data corruption issues.

How do I handle quotation marks in a custom VBA export?

When manually looping through a Recordset in VBA, you must write code to explicitly wrap text fields in quotation marks using the Chr(34) function. You also need to escape any internal quotes by replacing single quotation marks with double ones.

Excel & Data Problems