VBA & Macro Problems

Export Access Query Records to Word Templates with VBA

Camila MilosovichCamila Milosovich Nov 22, 2026 868 views

Question details

Create a separate Word document from a template for each record in an Access query.

Export Access Query Records to Word Templates with VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Prepare the template and folder

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.

2
Add the VBA procedure

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

3
Map the remaining fields

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.

4
Run and check the export

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.

Create a Word document for each snapshot record
Bookmark behavior: Writing text to a Word bookmark's range removes that bookmark. This example starts a fresh document from the template for every record, so the bookmark is available again in each new document.
Free Microsoft Office alternative

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.

Open and review the individual documents produced by the exportCompatible with Microsoft Word DOCX and DOC formatsFree, lightweight office suite with a familiar interfaceMove existing Office documents into WPS for continued editing
microsoft office alternative - wps office

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.