What's the easiest way to MailMerge MS Word to MS SQL Server using VBA code?

Rabbi 0
Viewed 36

Why doesn't MS Word just use a Connection String and SQL statement like every other platform on the planet?

There are pretty standard connection strings to be used to connect to any database whether it's Access, MS Sql, MySql, etc. Whether you're coming from Ado in prehistoric VB, or ADO.net or EF, or Java, or Python, or PHP, etc. It's always the same. One simple Connection String, then the SQL to be executed. Why is MS Word that much different?

I've had an Access front end to a SQL backend running for 20 years. One of its functions sets up a Word Document with a MailMerge bound back to the Access front end. The upgrade to Office 11 (or whatever the latest upgrade was) made that function start throwing an exception "Datasource placed in a state by admin". The only fix was to massage the ActiveDocument.MailMerge.OpenDataSource method so that it now uses DDE to connect to the access database. The issue is that it freezes the computer for about 90 seconds.

I'm thinking that it might just be simpler to point the MailMerge directly as the SQL backend. Any help would be appreciated.

mailmerge sql-server ms-word
1 Answers

Difficult to know which is the "easiest way" for you, but here are the basics. As you probably know, making database connections from Word can be very tricky - it's almost impossible to provide a completely reliable set of instructions that will definitely work. But Excel is generally much better at it so if you have problems it is worth seeing if your connection works on that. (Although modern Excel makes it a lot harder to do that now, IMO).

To make an ODBC connection to a SQL Server data source from the current version of Windows desktop Word, you should be able to use one of the following.

Using a User DSN named SQLDSN that contains all the connection info (primarily driver name, instance\server name, DB name) for a database that is using Windows Authentication, and a table called MYTABLE, the starting point is straightforward:

Sub c_odbc_user_dsn_1()
With ActiveDocument.MailMerge
  ' optionally disconnect any existing data source
  .MainDocumentType = wdNotAMergeDocument
  .OpenDataSource Name:="", _
    Connection:="DSN=SQLDSN;", _
    SQLStatement:="SELECT * FROM ""MYTABLE"""
End With
End Sub

Notes:

If the DSN is lacks some of the details or you are not using WIndows Authentication, you need to provide those details and username and password after SQLDSN; in the usual way, bearing in mind that you need to double up any double quote characters.

I have only been able to make this work with a User DSN, not a System DSN.

You also need to use SQL Server's dialect of SQL, not Access Jet SQL.

You may also need to qualify the table name, e.g. using the full

database_name.schema_name.table_name

syntax. If you have to do that, you may also encounter a problem where you have to quote each part of the name separately, e.g. something like

SQLStatement:="SELECT * FROM ""database_name"".""schema_name"".""table_name"""

You must specify a DSN. If you omit that but provide driver name, database name etc. only, it just doesn't work. This is IMO the single most irritating aspect of Word ODBC connections.

For some reason, Word does not retrieve any data from Unicode format columns (NTEXT, NCHAR, NVARCHAR) via ODBC. I have always believed it is a problem in Word. Again, it is possible that there is some way to fix that using driver parameters but if so I do not know what that is. But if you don't have Unicode column types there should be no problem.

At least one older version of Word would not let you specify "" as the file name in the OpenDataSOurce call. Think it was fixed a while back though. Or it's possible that you had to provide an additional prameter to the call, like this:

Sub c_odbc_user_dsn_2()
With ActiveDocument.MailMerge
  ' optionally disconnect any existing data source
  .MainDocumentType = wdNotAMergeDocument
  .OpenDataSource Name:="", _
    Connection:="DSN=SQLDSN;", _
    SQLStatement:="SELECT * FROM ""MYTABLE""", _
    SubType:=wdMergeSubTypeWord2000 ' i.e. :=8
End With
End Sub

If your SQLStatement exceeds around 255 characters, you have to split it in two and use this

Sub c_odbc_user_dsn_3()
With ActiveDocument.MailMerge
  .MainDocumentType = wdNotAMergeDocument
  .OpenDataSource Name:="", _
    Connection:="DSN=SQLDSN;", _
    SQLStatement:="SELECT *", _
    SQLStatement2:=" FROM ""MYTABLE""", _
    SubType:=wdMergeSubTypeWord2000 ' i.e. :=8
End With
End Sub

IN fact, none of these parameters can exceed the 255 character limit so if you have a particularly long connection string you may need to cut some bits out of it.

If you have a File DSN, let's say at C:\my dsns\my sql server dsn.dsn then you should be able to start with this:

Sub c_odbc_file_dsn_1()
With ActiveDocument.MailMerge
  .MainDocumentType = wdNotAMergeDocument
  .OpenDataSource Name:="C:\my dsns\my sql server dsn.dsn", _
    Connection:="FILEDSN=C:\my dsns\my sql server dsn.dsn", _
    SQLStatement:="SELECT * FROM ""MYTABLE""", _
    SubType:=wdMergeSubTypeWord2000 ' i.e. :=8
End With
End Sub

In this case you definitely need the SubType parameter.

Much the same issues as above, including the problem with Unicode columns.

If you need Unicode columns, you have to use OLEDB. There are a few ways to do it. Manually, make sure you have your connection info. to hand, then in Word, use Mailings->Select Recipients->Use an Existing List, click the New Source... button near the bottom of the window, and you are then in the Data Connection Wizard. You may have several options.

  • The "Microsoft SQL Server" option uses, I think, whatever provider Word assumes is the default. Here, it is SQLOLEDB.1.
  • The "ODBC DSN" option takes you to a second page that lets you choose an ODBC DSN. This option uses the MSDASQL provider. Interestingly, this route allows Word to see the Unicode column data that it can't see using ODBC alone. It also lets you use a System DSN.
  • The "Other/Advanced" option takes you to a list of OLE DB providers - you may have more than one for SQL Server.

Once you have been through the dialogs, you are prompted to save your connection info in a .odc file. Word will let you finish making the connection, so in principle you might not need to use VBA. But if you do, all you actually need is something like this:

Sub c_oledb_1()
With ActiveDocument.MailMerge
  .MainDocumentType = wdNotAMergeDocument
  .OpenDataSource Name:="C:\my odcs\my sql server odc.odc", _
    SQLStatement:="SELECT * FROM ""MYTABLE"""
End With
End Sub

The .odc contains full connection info. but some other items in it are a bit misleading. Also, Word uses the .odc once to make a connection. If you save the Word Mail Merge Main document after that, and re-open it, Word re-opens the same connection, even if you changed the details in the .odc. So if you need your document to reflect the changes, you have to disconnect/reconnect.

If you have several OLE DB connections and you do not want to maintain/distribute several .odc files, you can use an empty .odc file (just a text file with no text and a .odc extension) and put the connection info in the Connection parameter. You will also probably need to have a Connection parameter if you need to provide a username/password. e.g.

Sub c_oledb1()
With ActiveDocument.MailMerge
  .MainDocumentType = wdNotAMergeDocument
  .OpenDataSource Name:="c:\my odcs\empty.odc", _
    connection:="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=MYSERVER;Initial Catalog=MYDATABASE", _
    SQLStatement:="SELECT * FROM ""MYTABLE"""
End With
End Sub

For an OLE DB connection, before .odc files there was a much simpler .udl file. But .odc is probably a better bet these days.

Finally, it is a while since I had to do this "for real". One of the things I remember is that making a connection to a Server without using a Trusted Connection could be much more problematic, but I cannot remember the details now.

jonsson0
Related