Open Word document and automate mail merge

[dkwong]

I have a button in Access that I have opening a word document ready to mail merge with fields from the Access db. I want to automate this process so that the document is merged and ready for printing. I consulted the Microsoft Q209976 article that outlines how to do this uses the OpenDataSource and Execute meethods of the MailMerge object which would work fine IF the Access db were not secured. However, my Access db is secured with an associated .mdw workgroup file so it's trickier. I keep getting a Run Time Error 5922 cannot open data source. I'm unsure how to get by the security. Below is the code I think would work for an unsecured data source. How can I open a secure data source??

Dim objWord As Word.Document    
Dim WordDocPath As String    
Dim DataSourcePath As String        

WordDoc = <path to Word document>    
DataSourcePath = <path to Access database>        
Set objWord = GetObject(WordDoc, &quot;Word.Document&quot;)    

' Make Word visible.    
objWord.Application.Visible = True    

' Set the mail merge data source as the Donor DB database.    
objWord.MailMerge.OpenDataSource _       
NAME:=DataSourcePath, _       
LinkToSource:=True, _       
Connection:=&quot;QUERY TRIBUTE_CARD_ENTRY_FORM_QRY&quot;, _    

' Execute the mail merge.    
objWord.MailMerge.Execute        

Set objWord = Nothing

 

[VBslammer]

You need to create a DSN that defines the system.mdw location as well as your user name and password (Try using the Admin's user name and password). Then you can use the DSN for your connection:

objWord.MailMerge.OpenDataSource Name:="", Connection:="DSN=myDSN", SQLStatement:="SELECT * FROM myTable"

VBSlammer