How to Export an Access Query to CSV and Email It Without Multivalued Field Errors
Question details
The user wants to export a Microsoft Access query to a CSV file and attach it to an Outlook email, but receives an error when the query contains a multivalued field.
- Product
- Microsoft Access and Microsoft Outlook
- Device & OS
- not provided
- Scenario
- Automating export of an Access query result as a CSV file for email distribution.
- Observed behavior
- DoCmd.TransferText succeeds for ordinary queries, but queries with multivalued fields fail with: “Cannot reference a table with a multivalued field using an IN clause that refers to another database.”
Open the Access database and review the query in Design View. Identify whether the query includes any multivalued lookup fields before running the export code.
Export a Flattened Query Without Multivalued Fields
Use this method when the Access query contains a multivalued field and TransferText returns the IN-clause error.
Microsoft Access stores multivalued fields in hidden system tables, so direct exports of a query that includes them can trigger cross-database errors.
Creating a flattened query that expands or removes the multivalued field gives TransferText a clean result set to export.
In the Access Navigation Pane, right-click the query named “Appointment Confirmation3” and choose Design View.
Delete the multivalued lookup column from the query grid, or replace it with a subquery that returns the expanded values as ordinary rows.
Press Ctrl+S, enter a new query name such as “Appointment Confirmation3_Flat”, and close Design View.
In your VBA code, call DoCmd.TransferText acExportDelim, , "Appointment Confirmation3_Flat", "C:\tester.csv", True.
Create a Temporary Table and Export It
Use this if you cannot modify the original query or need to preserve the original object while creating a clean CSV export.
Move Your Access Export Workflows to WPS Office for Lightweight CSV Tasks
WPS Office is a free, lightweight Microsoft Office alternative that opens, edits, and saves Excel-compatible .xlsx and .csv files with a familiar ribbon interface. Although it does not include Microsoft Access, you can migrate table data to WPS Spreadsheets and use its CSV export and sharing features without the Access multivalued field limitations.
Frequently Asked Questions
Can I export a Microsoft Access query directly to CSV with DoCmd.TransferText?
Yes, if the query contains ordinary flattened fields. Use DoCmd.TransferText acExportDelim with a valid file path and set HasFieldNames to True.
Why do I get “Cannot reference a table with a multivalued field using an IN clause that refers to another database”?
A multivalued lookup field in the query forces Access to join hidden system tables, which TransferText cannot export across that join. Remove the field or replace it with a flattened query.
How do I attach the exported CSV to an Outlook email?
First create the CSV file. Then run Outlook VBA to create a MailItem and use Attachments.Add with the CSV path, or use DoCmd.SendObject to send a supported Access object if a CSV attachment is not mandatory.
Can WPS Office replace Access for exporting CSV files?
WPS Office does not have an Access database module, but WPS Spreadsheets can open, clean, save, and export Excel-compatible .csv files for email sharing.
Do I need Microsoft Outlook installed to email the CSV from Access?
Access email automation usually requires Microsoft Outlook or another MAPI-compatible email client that is installed and configured on the same computer.
