Solved

MAIL MERGE IN ACCESS 2007/MS WORD 2007

Posted on 2013-05-28
14
941 Views
Last Modified: 2013-06-13
Is there anyway to get ird of the pop-up error message in the mail merge process from access to word? Error "Opening this document will run the following SQL command:  Select * from [table].
0
Comment
Question by:centralmike
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39201493
You can edit the registry to suppress that security message. The details are here:
http://support.microsoft.com/kb/825765
0
 

Author Comment

by:centralmike
ID: 39201731
I am sorry we are large company.  We are not allowed to change the registry settings.  Is there anyway through code to eliminate this message from popping up?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39201910
The only other way would be to save the main document as a 'Normal Word Document', and to re-establish it as a merge document and to reconnect the record source each time that it is opened.

This could be done in VBA code (if you allowed to use it)
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:centralmike
ID: 39202386
If possible can you send me an example of that process.  Changing the "Main Document" into a "Normal Document" through code.  When the mail merge process ends I would word to return to its normal process being the "Main Dcoument".
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39202616
Here is a sample.
Sub SetUpDocumentAndMerge(docMain As Document)

    With docMain.MailMerge
        .MainDocumentType = wdFormLetters
        .Destination = wdSendToNewDocument
        .OpenDataSource Name:="X:\MyDatabases\MyFile.mdb", _
            SQLStatement:="SELECT * FROM `MyTable`"
        .Execute
    End With
    docMain.Close wdDoNotSaveChanges 'thus saved document remains a non-merge document
    
End Sub

Open in new window

0
 

Author Comment

by:centralmike
ID: 39202663
Graham, I going to send you a copy of my module if you can add the line of code that will allow me to close the document without saving.
---------------------------------------------------------------------------------------------------------------------------------------------
Function MergeIt()

DoCmd.SetWarnings False
   Dim objWord As Word.Document
   Set objWord = GetObject("X:\Special Risk\miketesting\DEVELOPMENT\merge.docx", "Word.Document")


 
   objWord.Application.Visible = True
    objWord.MailMerge.OpenDataSource _
      Name:="X:\Special Risk\miketesting\DEVELOPMENT\SELECTIONPATHCHOOSEN.ACCDB", _
      LinkToSource:=True, _
      Connection:="TABLE TABLE1", _
      SQLStatement:="SELECT * FROM [TABLE1]"
   ' Execute the mail merge.
   
   objWord.MailMerge.Destination = wdSendToNewDocument
 ' aDoc.MailMerge.MainDocumentType = wdNotAMergeDocument

   objWord.MailMerge.Execute



objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.PrintOut
'DoCmd.SetWarnings True



End Function
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39202848
You don't have a separate object variable for the Word application, so you need to keep the main document open until the printing has been done:
Function MergeIt()

DoCmd.SetWarnings False
   Dim objWord As Word.Document
   Set objWord = GetObject("X:\Special Risk\miketesting\DEVELOPMENT\merge.docx", "Word.Document")
 
   objWord.Application.Visible = True
    objWord.MailMerge.OpenDataSource _
      Name:="X:\Special Risk\miketesting\DEVELOPMENT\SELECTIONPATHCHOOSEN.ACCDB", _
      LinkToSource:=True, _
      Connection:="TABLE TABLE1", _
      SQLStatement:="SELECT * FROM [TABLE1]"
   ' Execute the mail merge.
   
   objWord.MailMerge.Destination = wdSendToNewDocument
 ' aDoc.MailMerge.MainDocumentType = wdNotAMergeDocument

   objWord.MailMerge.Execute

objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.PrintOut
'DoCmd.SetWarnings True
objWord.Close wdDoNotSaveChanges

End Function  

Open in new window

0
 

Author Comment

by:centralmike
ID: 39204467
Good Morning Graham, I ran the module above and still receive the following sql error prompt.   Is there something else I am missing?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39204967
The code doesn't actually remove merge status and link.

Open the document manually, set to a Normal Word Document and save it. Next time that it opens, it will be as such.

Our code converts it to a main document, uses it and then closes it without saving.
0
 

Author Comment

by:centralmike
ID: 39205211
Just one last question Graham.  After the merge process ends word remains open.  I would close it after the last document prints.  I tried the following statement: objWord.Application.Quit an received an error message "462"
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 39205306
I always like to use an object variable for the application as well as any documents. The code for that would look more like this
Function MergeIt()
    Dim objWordApp As Word.Application
    Dim objWord As Word.Document
    Dim bNewInstance As Boolean
    
    On Error Resume Next 'suppress error checking for next instruction only
    Set objWordApp = GetObject(, "Word.Application")
    On Error GoTo 0 'resume error checking
    
    If objWordApp Is Nothing Then
        Set objWordApp = CreateObject("Word.Application")
        bNewInstance = True
    End If
    objWordApp.Visible = True 'In case macro doesn't run to the end, leaving an invisible instance of Word.
     
    Set objWord = objWordApp.Documents.Open("X:\Special Risk\miketesting\DEVELOPMENT\merge.docx", "Word.Document")
    objWord.MailMerge.OpenDataSource _
      Name:="X:\Special Risk\miketesting\DEVELOPMENT\SELECTIONPATHCHOOSEN.ACCDB", _
      LinkToSource:=True, _
      Connection:="TABLE TABLE1", _
      SQLStatement:="SELECT * FROM [TABLE1]"
    
    objWord.MailMerge.Destination = wdSendToNewDocument
    
    ' Execute the mail merge.
    objWord.MailMerge.Execute
    
    objWordApp.Options.PrintBackground = False
    objWordApp.ActiveDocument.PrintOut
    
    'close the documents
    objWordApp.ActiveDocument.Close wdDoNotSaveChanges
    objWord.Close wdDoNotSaveChanges
    
    'Close application if it was created for this merge
    If bNewInstance Then
        objWordApp.Quit
    End If
End Function

Open in new window

0
 

Author Comment

by:centralmike
ID: 39205362
Now I am receiving a runtime error 13 on the following line.

  Set objWord = objWordApp.Documents.Open("X:\Special Risk\miketesting\DEVELOPMENT\merge.docx", "Word.Document")
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39205395
Oops. I couldn't actually test the code because it referred to your folders. I changed your GetObject line into the Open code and failed to remove the ' , "Word.Document"' bit.

it should read:
 Set objWord = objWordApp.Documents.Open("X:\Special Risk\miketesting\DEVELOPMENT\merge.docx")
0
 

Author Closing Comment

by:centralmike
ID: 39246081
Thanks for all the help Graham.  Sorry for the delay I had to leave town.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question