Word 365 Mail Merge Problem re DDE connection error

Topic

New Reply

Hi there.  I’m really hoping someone can help and that the solution is relatively simple.

For years, we have been using a mail merge to connect a SQL database and create file labels.  Now that we have switched to Office 365, we’re getting a DDE connection error.  Basically, the coding is relatively simple.  The macro opens a new document, types the query:

XLODBC

1

driver= SQL Server; SERVER=…

SELECT …

It then saves this as :

ActiveDocument.SaveAs FileName:=STRMACROFILES & “NMLabels.dqy”, fileformat:=wdFormatText, _

lockcomments:=False, Password:=””, addtorecentfiles:=False, WritePassword:=””, ReadOnlyRecommended:=False, _

EmbedTrueTypeFonts:=False, savenativepictureformat:=False, SaveFormsData:=False, saveasAOCELetter:=False, _

Encoding:=1252, InsertLineBreaks:=False, AllowSubstitutions:=False, LineEnding:=wdCRLF

ActiveDocument.SaveAs FileName:=STRMACROFILES & “NMLabels.dqy”, fileformat:=wdFormatText, _

lockcomments:=False, Password:=””, addtorecentfiles:=False, WritePassword:=””, ReadOnlyRecommended:=False, _

EmbedTrueTypeFonts:=False, savenativepictureformat:=False, SaveFormsData:=False, saveasAOCELetter:=False, _

Encoding:=1252, InsertLineBreaks:=False, AllowSubstitutions:=False, LineEnding:=wdCRLF

I’ve tried connecting the database directly, but the SQL query is quite long and I get a different error that the string is longer than 255 characters.

I don’t know why it had to change, but is there an easy way to get the same results in Word 365?  I’m hoping that maybe it’s just a matter of changing the way I save the file containing the query, but I’m having trouble finding anything on-line.

Any assistance would be very much appreciated.

Reply | Quote

Replies

So I changed the coding to use OLEDB connection but can’t get the SQL query small enough to be less than 255 characters.  Please let me know if I’m on the right path and if there is something else I can do?

strDatabase = “DRIVER=SQL Server;SERVER=…;DATABASE=son_db”
strSQL = “SELECT * FROM son_db.dbo.client client, son_db.dbo.matter matter, son_db.dbo.praccode praccode, son_db.dbo.timekeep timekeep, son_db.dbo.udf udf, son_db.dbo.udfdef udfdefWHERE timekeep.tkinit = matter.mbillaty AND matter.mprac = praccode.pcode AND udfdef.udfindex = udf.udfindex”
strSQL2 = “AND udfdef.udtype = udf.udtype AND matter.mmatter = udf.udjoin AND matter.mclient = client.clnum AND ((matter.mstatus=’OP’) AND (udf.udtype=’MT’) AND (udf.udfindex=41) AND (matter.mmatter = ‘” & strvMatterNumber & “‘))”
ActiveDocument.MailMerge.OpenDataSource Name:=strDatabase, SQLStatement:=strSQL, SQLStatement1:=strSQL2

Reply | Quote

If you can’t make the query small enough you could write a procedure to store in the database, then call that.

cheers, Paul

Reply | Quote

Thanks — that’s helpful

Do you have some general code you could post that would help guide me in the right direction by any chance?  I am really struggling with this …

Thank you again!

Reply | Quote

Sorry, not a SQL programmer.

Search for SQL stored procedure to get some ideas, or ask your SQL bods.

cheers, Paul

Reply | Quote

Ultimately what I am trying to do is connect a MS Query to a mail merge main document.  The original code I had is here:

‘Open a blank document

Documents.Add documenttype:=wdNewBlankDocument

On Error GoTo Oops

 

‘Manually type the query and save

With Selection

.TypeText Text:=”XLODBC”

.TypeParagraph

.TypeText Text:=”1″

.TypeParagraph

.TypeText Text:=”DRIVER=SQL Server;SERVER=NAME;UID=NAME;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=01234;DATABASE=son_db”

.TypeParagraph

.TypeText Text:=”SELECT matter.mmatter, client.clname1, client.clname2, matter.mdesc1, matter.mdesc2, matter.mdesc3, matter.mclient, timekeep.tkinitb, matter.mprac, praccode.pdesc, udf.udvalue  FROM son_db.dbo.client client, son_db.dbo.matter matter, son_db.dbo.praccode praccode, son_db.dbo.timekeep timekeep, son_db.dbo.udf udf, son_db.dbo.udfdef udfdef  WHERE timekeep.tkinit = matter.mbillaty AND matter.mprac = praccode.pcode AND udfdef.udfindex = udf.udfindex AND udfdef.udtype = udf.udtype AND matter.mmatter = udf.udjoin AND matter.mclient = client.clnum AND ((matter.mstatus=’OP’) AND (udf.udtype=’MT’) AND (udf.udfindex=41) AND (matter.mmatter = ‘” & strvMatterNumber & “‘))”

End With

 

‘Save the query text

ActiveDocument.SaveAs FileName:=STRMACROFILES & “NMLabels.dqy”, fileformat:=wdFormatText, _

lockcomments:=False, Password:=””, addtorecentfiles:=False, WritePassword:=””, ReadOnlyRecommended:=False, _

EmbedTrueTypeFonts:=False, savenativepictureformat:=False, SaveFormsData:=False, saveasAOCELetter:=False, _

Encoding:=1252, InsertLineBreaks:=False, AllowSubstitutions:=False, LineEnding:=wdCRLF

ActiveDocument.Close

Application.ScreenUpdating = False

 

‘Open the template and set as main document

 

Documents.Add Template:=STRMRTEMPLATES & “Open NEW Matter Labels.dotx”, newtemplate:=False

Set MainDoc = ActiveDocument

ActiveDocument.MailMerge.OpenDataSource Name:=STRMACROFILES & “NMLabels.dqy”, ConfirmConversions:=False

 

Since we switched to Word 365, when this macro is run it has a message at the bottom saying connecting to MS Query, it takes a long time and then returns a DDE error.  I would like to know how to fix it so that I can still attach this query to a mail merge document without the error.

 

Does anyone have any ideas?

Reply | Quote

> Does anyone have any ideas?

This seems like a long shot, but is there any chance your change to Office/Word 365 included a quiet change to a blocking security setting? Maybe double-check current Word security settings?

Hope this helps.

Reply | Quote