Export Access Query Records to Word Templates with VBA
Question details
Create a separate Word document from a template for each record in an Access query.

- Product
- Microsoft Access and Microsoft Word
- Device & OS
- not provided
- Scenario
- An Access VBA procedure reads qryFailedElements and fills bookmarks in a Word template for each query record.
- Observed behavior
- A read-only error occurs when opening the recordset. After switching to a snapshot, the error disappears, but no merged documents appear in the expected output location.
Open qryFailedElements in Access and confirm that it returns records and includes the fields you want to export. In Word, check the template's bookmark names under Insert > Bookmark, and confirm that Word is installed and the output folder exists.
Create a Word document for each snapshot record
Use this approach when the query only needs to be read and each record should produce a separate document.
A snapshot recordset is read-only by design, so it is suitable for reading query results but not for editing them. Switching to a snapshot may resolve the reported recordset error; it does not by itself create or save documents.
The example creates a new document from the template for each record rather than editing the template itself. It uses one sample field and bookmark; add mappings for the remaining fields.
In Word, open the template and use Insert > Bookmark to confirm a bookmark named ElementName, or replace that name in the code. Create C:\AccessExports, or change the output path in the code to an existing folder where you have permission to save.
In Access, open the VBA editor and add this procedure to a standard module. Replace the template path and ElementName field and bookmark names with your own: Public Sub ExportQueryToWord() Dim db As DAO.Database Dim rs As DAO.Recordset Dim wordApp As Object Dim wordDoc As Object Dim outputFile As String Dim runStamp As String Dim n As Long On Error GoTo ExportError Set db = CurrentDb Set rs = db.OpenRecordset("qryFailedElements", dbOpenSnapshot) If rs.EOF Then MsgBox "The query returned no records." GoTo CleanUp End If Set wordApp = CreateObject("Word.Application") runStamp = Format(Now, "yyyymmdd_hhnnss") n = 0 Do While Not rs.EOF n = n + 1 Set wordDoc = wordApp.Documents.Add("C:\Templates\FailedElements.dotx") If Not wordDoc.Bookmarks.Exists("ElementName") Then Err.Raise vbObjectError + 1000, , "Bookmark ElementName was not found." End If wordDoc.Bookmarks("ElementName").Range.Text = Nz(rs.Fields("ElementName").Value, "") outputFile = "C:\AccessExports\Record_" & runStamp & "_" & n & ".docx" wordDoc.SaveAs2 outputFile, 12 wordDoc.Close 0 Set wordDoc = Nothing rs.MoveNext Loop MsgBox n & " document(s) saved to C:\AccessExports." CleanUp: On Error Resume Next If Not wordDoc Is Nothing Then wordDoc.Close 0 If Not wordApp Is Nothing Then wordApp.Quit If Not rs Is Nothing Then rs.Close Set wordDoc = Nothing Set wordApp = Nothing Set rs = Nothing Set db = Nothing Exit Sub ExportError: MsgBox "Export stopped: " & Err.Description Resume CleanUp End Sub
For each additional value, add a bookmark to the Word template through Insert > Bookmark, then add a corresponding Bookmarks("BookmarkName").Range.Text assignment before SaveAs2. Replace each field name with a column returned by qryFailedElements.
Run ExportQueryToWord from the Access VBA editor. Open C:\AccessExports, or your chosen output folder, and check that each DOCX file contains the expected record values.

Check why the export creates no files
Use these checks if the snapshot opens successfully but the expected documents are still missing.
Review exported Word documents with WPS Office
WPS Office is a free, lightweight alternative for opening and editing the Word documents created by your Access export. Its familiar interface and Microsoft Office format support make it easy to move existing documents into WPS. It does not fix Access VBA or replace the Access-to-Word automation.

Frequently Asked Questions
Why use dbOpenSnapshot for the Access query?
Use db.OpenRecordset("qryFailedElements", dbOpenSnapshot) when the VBA procedure only needs to read the results. A snapshot is read-only, so use a different recordset type if the procedure must update records.
Why are no documents appearing on my desktop?
Check the full path passed to Word's SaveAs or SaveAs2 method; it may point to a different folder. Also confirm that the query returns rows and that the destination folder exists and is writable.
How do I find the bookmark names in my Word template?
Open the template in Word and select Insert > Bookmark. Match each bookmark name exactly to the name used in the VBA code.
Does the export need to open the template for editing?
No. Word's Documents.Add method can create a new document based on the template. Save that new document as a DOCX file for each record rather than changing the template itself.
Why did my bookmark disappear after VBA inserted text?
Assigning text to a bookmark's Range removes that bookmark from the document. Create a fresh document from the template for each record, or recreate the bookmark if the same document needs it again.




