Remote SQL Mail-Merging

Question

Hello, how will i mail-merge into a remote sql server.

I have a UNIX/Linux server with mysql/cPanel on and i wish to connect to this server to use mail-merge in my document. Can someone please assist me in doing so. I have teamviewer aswell so contact me if you can help. Thanks

dan [AT] truconcept [DOT] net

Answer 1

It isn't straightforward. Broadly speaking, you have to do this:

 a. ensure that your server allows the database to be opened from the IP address(es) you want to use. Everything after this is on the client machine

 b. ensure that the MySQL ODBC driver is installed on the client machine. (There was a MySQL OLE DB provider at one time, but AIUI it is no longer supported, so I'm not going to go there)

 c. create an ODBC DSN (e.g. in Control Panel->Administrative Tools->ODBC Administrator). You can probably use a Machine DSN (User DSN or System DSN - these are stored in the Registry; the System one should be available to all users on the machine) or a File DSN with a .dsn extension (using a file dsn can make it a bit more obvious what is in the DSN)

 d. Use Word VBA to make the connection. You can try manually, going via MSQuery (the old method) but I have never got it to work with Word 2007/Vista, even though it works OK with Excel). Once you have made the connection and saved the Mail Merge Main Document, you should be able to repen it and click through the usual security question to reconnect. However, because it seems to be impossible to connect without using VBA, you're then in trouble if for some reason the reconnection fails.

The VBA needs to look something like this for a Machine DSN. Putting the database name etc. in the Connection string should override the equivalent values in the DSN, and you will also have to put a password in the Connection string (or you could consider putting it in the File DSN). However you do it, it's probably a security risk that you will need to consider.

Sub connectToMySQLViaODBCMachineDSN()

With ActiveDocument.MailMerge

  ' lose the existing source, filters and sorts

  .MainDocumentType = wdNotAMergeDocument

  ' specify the merge type

  .MainDocumentType = wdDirectory

  ' For a machine DSN, use an empty name. You

  ' used to have to specify the Subtype

  ' wdMergeSubTypeWord2000 in that case

  ' Recently, Word has begun to expect

  ' delimiters such as quotes around the

  ' table name in the SELECT. If you have to

  ' add them, I don't know what to use in MySQL

  ' Server needs to specify the IP address

  .OpenDataSource _

    Name:="", _

    Connection:="DSN=mydsnname;Server=192.168.1.29;Database=mydatabasename;" & _

      "User=myusername;Password=mypassword;", _

    sqlstatement:="SELECT * FROM user", _

    SubType:=wdMergeSubTypeWord2000

End With

End Sub

For a File DSN, you try the following (notice that you have to use FILEDSN instead of DSN in the connection string)

Sub connectToMySQLViaODBCMachineDSN()

With ActiveDocument.MailMerge

  ' lose the existing source, filters and sorts

  .MainDocumentType = wdNotAMergeDocument

  ' specify the merge type

  .MainDocumentType = wdDirectory

  .OpenDataSource _

    Name:="c:\mydsns\myfiledsnname.dsn", _

    Connection:="FILEDSN=c:\mydsns\myfiledsnname.dsn;Server=192.168.1.29;Database=mydatabasename;" & _

      "User=myusername;Password=mypassword;", _

    sqlstatement:="SELECT * FROM user", _

    SubType:=wdMergeSubTypeWord2000

End With

End Sub

FWIW if you connect using Excel, it's usually a lot easier. But Excel then saves a .odc file that Word does not appear to be able to use.

There's more about the details of MySQL connection strings at www.connectionstrings.com

---

Peter Jamieson