A Simple Example of Merge Document (Microsoft Access 2003)

• Download scripts - 692 B

Introduction

This article will describe how easily you can merge a document from Microsoft Access 2003 database.

Background

It is a very common practice to create various word documents from the database and merge the document. Most VBA developers are familiar with that. In this article, I would like to demonstrate how you can fetch a record form the database tables by executing transact SQL statement and merge all the records in a document.

Using the Code

This is a very effortless way. You just need some basic idea on MailMerge.OpenDataSource method.

• MailMerge.OpenDataSource: Attaches a data source to the specified document, which becomes a main document if it's not one already.

Sample Example

Public Sub MergeDocument(strDocumentPath As String _
                      , strDocumentFile As String _
                      , strSQLStatement As String)
                      
On Error GoTo ErrorHandler

Dim ObjApplication As Word.Application
Dim ObjDocument As Word.Document

DoEvents

Dim strSourceName As String
Dim MergeSubType As WdMergeSubType

    strSourceName = "C:\MyDatabase.mdb"
    MergeSubType = wdMergeSubTypeWord2000
    
    Set ObjDocument = GetObject(strDocumentPath & strDocumentFile, "Word.Document")
    
    ObjDocument.Application.Visible = True
    
    DoEvents
    
    ObjDocument.MailMerge.OpenDataSource Name:=strSourceName, 
				SQLStatement:=strSQLStatement
    
    DoEvents
    
        With ObjDocument.MailMerge
            .Destination = wdSendToNewDocument
            .Execute Pause:=True
        End With

DoEvents

ObjDocument.Close wdDoNotSaveChanges
Set ObjDocument = Nothing
Set ObjApplication = Nothing

Exit_mergeDocument:
    Exit Sub

ErrorHandler:
    MsgBox Err.Description
    Resume Exit_mergeDocument

End Sub 

Conclusion

I hope that this simple example might be helpful to you.

History

• 5^th November 2009: Initial post