Mail merge connection (Access 2000)

Topic

New Reply

I currently use the following code to allow users to mail merge from Access to Word. It appears to cicumvent all the problems in other methods by using Word to ‘pull the data’ rather than have Access ‘push the data’, which doesn’t work in an MDE. I’ve found it the only successful way of handling mail merges.

With doc.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource _
Name:=gstrDataPath & gstrDataName, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Connection:=”TABLE tblMerge”
End With
appWord.Visible = True
appWord.Activate

The problem is that the extra copy of Access needed by DDE is sometimes left on the Windows taskbar and this may confuse users. Does anyone know how I can remove this copy from within Access?

Reply | Quote

Replies

Actually DDE doesn’t always start a second copy of Access, though it will under several conditions. One of those is if you’ve change the application name under Tools/StartUp, and another is if you have two or more Access databases open when you start the merge. The solution to the first is to name your application “Microsoft Access – MY APP NAME” which is enough to fool DDE. The second doesn’t have a nice neat solution, and in fact depends on which session of Access was started first. You might find some additional useful info in this tutorial on Access driven mail merges.

Unfortunately, I don’t know of a programatic way of closing the extra Access session once it’s been opened.

Reply | Quote

In Access 97, setting a database caption in Tools/Startup… caused extra copies of Access to appear when doing a mail merge using DDE. I don’t know if this holds for Access 2000.
I use ODBC to perform mail merges in code; this is faster than ODBC, doesn’t open extra instances of Access, and allows merging with a SQL statement built on the fly. The OpenDataSource part then looks like this:

With doc.MailMerge
…
.OpenDataSource _
Name:=””, _
LinkToSource:=True, _
Connection:=”DSN=MS Access Database;DBQ=” & CurrentDb.Name, _
SQLStatement:=”SELECT * FROM tblMerge”
…
End With

Replace MS Access Database by the name of an Access ODBC datasource.

Reply | Quote

Thanks Hans. ODBC looks like the better bet and being able to send an SQL gets rid of much of the code needed to set up tblMerge in the first place.

However I am having a problem with the line starting Connection. I don’t understand your last comment “replace MS Access Database by the name of an Access ODBC datasource”. I presume that DBQ=CurrentDb.Name picks up the FE from which the code is being run but do I put the name of the BE in the DSN? I’ve tried all the possibilities that occur to me but still cannot get the VBEditor to accept.

Reply | Quote

David,

To perform a mail merge using ODBC, you must leave the Name argument of OpenDataSource blank. The Connection argument is a string which consists of several parts separated by semicolons. You must al least specify a Data Source Name (DSN) and a database (DBQ). If your database is secured, you can also specify a user (UID) and password (PWD). There is also a FIL argument that specifies the file type; in this case, it’s ignored.

The Data Source Name (DSN) is created in the ODBC control panel (most Windows versions) or ODBC Data Source Administrator (Windows XP); chances there is already a user DSN on your system named “MS Access Database” or something like that. If not, you can create a generic data source based upon the Microsoft Access Driver (*.mdb). Don’t specify a database or path to be used; this will be done in code.

Use the name of the Access DSN in your code. Let’s say that you created a clone of the standard Access DSN named Dolly, then you would use DSN=Dolly; in the Connect argument of OpenDataSource. In the post that started this thread, you had gstrDataPath & gstrDataName; I suppose this refers to the back end database; use DBQ=” & gstrDataPath & gstrDataName in the Connect argument.

Together, this would become something like

strSQL = “SELECT … FROM … WHERE ….”
With doc.MailMerge
…
.OpenDataSource _
Name:= “”, _
LinkToSource:=True, _
Connection:=”DSN=Dolly;DBQ=” & gstrDataPath & gstrDataName, _
SQLStatement:=strSQL

Reply | Quote

<>

Did you mean DDE with the second ODBC? I agree it is faster, but one of the limitations is that you cannot use parameter queries, and merges often seem to involve them.

Reply | Quote

Wendell,

It’s true that you can’t use parameter queries when merging with ODBC. Instead of using a parameter query, I present the user with a form (in Access) on which “parameters” can be specified; then I construct a SQL statement in code; this SQL doesn’t contain parameters, only literal values taken from the form. The SQL string is used in the mail merge. So each time the user specifies criteria on the form, a new SQL statement is generated.
The documents to be used contain merge fields, but have been restored to non-merge documents. This means that the merge can’t be initiated from Word, but on the other hand, the merge can use a variety of SQL statements as long as the merge fields are included.

Reply | Quote

(Edited by WendellB on 05-Mar-03 03:57. activate link)

In case this issue is not closed for you, have you ever tried some of the merging option presented by our resident Access Newsletter editor, Helen Feddema? I have used her methoed of pumping recordsets into custom Word Doc properties rather than the old Mail Merge standard practice and found it to be very quick and fairly easy to implement.

Check out http://www.helenfeddema.com/CodeSamples.htm%5B/url%5D and look specifically at Download 24; 4 Mail Merge Examples.

Rich P

Reply | Quote

You are correct – Automation arrived with Office 97 and is a viable alternative. There are tradeoffs however. It is much easier to create a mail merge in Word from a development perspective – the VBA to create a complex document can get really messy. On the other hand it is generally 2 or 3 times faster than a mail merge. There is an excellent section on Automation of various applications from Access in The Access Developer’s Handbook.

Reply | Quote

Thanks Rich P, I am aware of Helen’s work. In fact I use her methods to pass single records from Access to Word, with the user being able to select the target doc and they work fine. Unfortunately for merging several hundred records something that doesn’t create multiple Word documents is needed. The one method that might was to invoke the Mail Merge Wizard from code: this works fine until you put the Access front end into an MDE at which point it fails. I therefore set out to do the whole thing in code.

THanks to all who have contributed. I have now sorted my original problem using Hans’s alternative code and the bonus is that it’s also much faster.

Reply | Quote