Merge Object and not displaying Access Queries with VBA

macropod · #1 12-22-2015, 08:02 PM

You could add the SQL Statement to the OpenDataSource method. The OpenDataSource method takes the following parameters:
Name, Format, ConfirmConversions, ReadOnly, LinkToSource, AddToRecentFiles, PasswordDocument, PasswordTemplate, Revert, WritePasswordDocument, WritePasswordTemplate, Connection, SQLStatement, SQLStatement1, OpenExclusive, SubType

You'll see two SQLStatement references - SQLStatement & SQLStatement1. SQLStatement accepts query strings of up to 255 characters. If the query string is longer than 255 characters, SQLStatement specifies the first portion of the string, and SQLStatement1 specifies the second portion.

You can export the current parameters to a new document, using code like:

Code:

Sub Test()
Dim StrMMData As String, DocTmp As Document
With ActiveDocument.MailMerge
  If .MainDocumentType <> wdNotAMergeDocument Then
    StrMMData = StrMMData & "Mail Merge Data Source Name:" & vbCr & .DataSource.Name & vbCr
    StrMMData = StrMMData & "Mail Merge Connect String:" & vbCr & .DataSource.ConnectString
    StrMMData = StrMMData & vbCr & "Mail Merge Query String:" & vbCr & .DataSource.QueryString
    Set DocTmp = Documents.Add
    DocTmp.Range.Text = StrMMData
  Else
    MsgBox "Not A Merge Document"
  End If
End With
End Sub

As for the necessity to use DDE, I suggest you verify that on a specialist Access forum (e.g. http://www.accessforums.net/)

jmo · #2 12-28-2015, 06:20 AM

Thank you, Macropod.

Do you know what SQL statements I would add to open up a window that displays all the queries/tables in a particular database? Leaving that statement blank seems to accomplish this, but does not seem to solve my problem.

I've tried toggling several settings to get the window that asks you to manually confirm the file connection so I can select DDE (which is what happens when the 'Confirm File Conversation..' advanced option is checked).

Essentially I'm wanting the window that opens that is done automatically when you follow the steps in this post: https://www.msofficeforums.com/mail-...ps-tricks.html (under Mail Merge Data Format Problems).

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel worksheet as OBJECT in WORD not displaying	mohsin	Word	1	03-27-2012 10:43 PM
Mailmerge with Access queries	optiontips.in	Mail Merge	2	11-21-2011 03:12 AM
mail merge queries not displaying	donwalt	Mail Merge	0	09-05-2010 02:41 PM
flash shockwave object not displaying	webguync	PowerPoint	7	04-20-2010 07:23 AM
Access Object library 10	Gyto	Office	0	10-09-2008 09:04 AM