Thursday, February 14, 2008

Access and mail merge

Recently I've been running into some challenges using Access 2003 (2000 format) as the data source for mail merge, when using either Word or Publisher to produce merge documents.

In either of those two programs, you select your Access file as the data source, and normally you're presented with a list of all tables and select queries in the database. But on several occasions, with different Access files and different Word and Publisher documents, not all the items were listed. Not sure why. There is no workgroup security in place; only a single user; none of the items were tagged as hidden in Access. When the first attempt failed, we tried again using a different login that had full administrative permissions; same result.

When using Publisher to generate annual contribution letters for one customer, the Access file had a totals query built with the desired data, but Publisher did not show it on the list. At first I thought it was the totals query that Publisher did not like, but numerous other ordinary select queries did not show either.

When using Word to produce attendance certificates for another customer, again Access contained the desired query but Word could not see it. This was especially perplexing because when the Access & Word merge was set up back in 2004 it ran just fine (before the files were moved to a new server).

In both cases, I had to create an Excel export file and then use that for the merge, which worked as expected.

If I discover the reason those queries did not display, I'll post my findings.

EDIT: 45 minutes later...

Found the answer here (applies to 2003, too):

Re: missing queries in data source list (Word/Access 2007)

...as it turns out, if you are using functions written in VBA (or even a few of the built-in functions) in your query, it will not display in the Word or Publisher list. This behavior was definitely altered by a service pack, hotfix or other MS patch, because they used to work. What a letdown. This means that mail-merge queries need to avoid VBA functions to guarantee they can actually be used for mail merge.

The price of progress, in pursuit of enhanced security? Or a severe boo-boo from Redmond. You decide.

No comments: