To Merge or Not To Merge (a philosophical technology question)

[news.microsoft.com]

I could really use some quality advice here to ensure I am not barking up
the wrong technology tree.



I have been an Access Developer for a number of years and I have become
rather good at creating custom reports and exporting to HTML etc., however,
I have a customer asking me for something I have not done since my Office 95
days (dating myself here). Namely MAILMERGE.



Currently my customer goes into the field with paper checklists and gathers
information. They bring the paper checklists back to the office where the
information gets manually added to a boilerplate document (or template),
which then gets massaged into the final product.



There are 2 phases to the project:

In phase 1, they want an easier way to enter the data into their template.
Instead of flipping through all of the pages a entering the information in
scattered locations, they want to punch it all into a database and merge it
into the document. In phase 2, they want to start taking PDA's out to the
field to collect the data and they want to sync and merge it in the office.



Now to my question. 10 years ago, I would have assumed this was a job for
"Mail Merge". Today with the ever changing integration of the Office Suite,
I am not sure what other technologies I should explore before assuming that
Mail Merge is, and was, and ever more shall be, my best approach. Is there
anything else I should be considering?



Any advice (and especially links) would be greatly appreciated.



Thanks in Advance,

Tiffany

 

[Anne Troy]

Hi, Tiffany. I do a lot of Office project management, which I think
qualifies my response, but the MVPs may not agree.

Personally, if I had the choice whether to Access-Report-It or Mail-Merge-It
using data with an Access DB, I'd do the report. The issue for me has always
been this: What is the end product required? Many people have to email it,
so the Report viewer with Access is not as desirable. But, you can PDF from
Access, too, right? So that's an option. Of COURSE if you can keep it in
Access, do so. What is there reason for wanting a Word document? MUST they
have it in a Word doc for other purposes? If they're "massaging" the reports
AFTER the data is place, the probably they do. Hence, Mail merge. Mail merge
has become extremely unfriendly in newer versions of Office (IMHO), but it's
definitely still doable. You might want to go to the Access ng and ask about
"pushing" the mail merge from that end instead, so the mail merge can be run
from your DB instead of a Word doc. But for mail merge stuff in 2002 and
2003, see:

http://www.officearticles.com/word/mail_merge_letters_in_microsoft_word.htm

For 2000, see:

http://www.theofficeexperts.com/word.htm#MailMerge

For "push" from Access (sorry, different versions), see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210271
http://www.access-programmers.co.uk/forums/showthread.php?t=83988&highlight=mail+merge

I hope those help even a little bit!!
*******************
~Anne Troy

www.OfficeArticles.com

 

[Peter Jamieson]

Broadly speaking I would agree with Anne. A lot depends on
a. what documents are being produced and what needs to be done with them
b. what scale of operation your customer envisages
c. total cost of producing/maintaining any new system (including the cost
of grappling with new and unfamiliar technologies)

In Office itself, I'm not aware of any relatively easy-to-use features that
would do the sort of thing MailMerge does other than the ones you already
know. The new XML features in Office might be of interest as mechanisms for
manipulating and re-purposing data, but (a) the full features are only
available in certain versions of Office and (b) I doubt if they will help
end users in the kind of scenario you describe.

As far as Word MailMerge is concerned, it works in broadly the same way as
the Word 95 version, but there are some differences at a detailed level
a. the user interface has changed substantially
b. OLEDB connections to Access are now possible. Can be tricky if you are
using secured Access data.
c. Generally speaking, expect to do data formatting in Word merge field
switches rather than in your data source
d. there are security-related changes that can result in numerous pop-up
warnings, and which have affected the . Most can be disabled in one way or
another, e.g. via registry entries
e. there are MailMerge events which can help a programmer overcome some
types of problem

If you need to do anything that requires distribution of reports via e-mail
or in PDF format, there are various third-party products that you should
probably have a look at.

FWIW, in the larger MS-based world, if you happened to be using SQL Server,
there is a relatively new programmable reporting suite called Reporting
Services that is, if you like, the equivalent of the report generator in
Access. It doesn't sound to me as if you need to be looking at that, but
probably worth at least a glance.

Peter Jamieson

 

[Doug Robbins]

If the data is only used once in creating the document, and is not also
being retained in a database, then a template with a userform could well be
the way to go.

See the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

If however it is desired to retain the information in a database, then
either an Access Report as mentioned by others, or the single document
mailmerge solution that was created by fellow MVP and Bill Gates look-alike,
Albert Kallal at

http://www.members.shaw.ca/AlbertKallal/wordmerge/index.html




--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Tiffany]

Anne/Doug/Peter,



Thanks for the great replies. In response to Anne, I agree that sticking
with Access reports would be ideal (and certainly less foreign to me),
however, the massaging on the backend would kill it for us. Although the
final product does get PDF'd before going out to the customer, the Engineer
makes a lot of tweaks after the checklist data is populated.



The document is 80% boilerplate text/graphics to start with. The checklist
provides data to populate about 10-15% of the document and the remaining
5-10% is added / changed / customized by the Engineer. For this reason, it
appears the document will have to be in Word.



Something else I should add is that the Mail Merge functions would need to
be executed in VBA code or Macros (probably code) because the users are not
savvy in Mail Merge features and functions. Since I have much more
experience in Access than Word, I will be looking for sites that can help me
with Mail Merging from Access VBA. The links Anne provided on that should
be very helpful.



I would like to avoid some of the security pop-ups that Peter mentioned but
I'm sure I can work around most of those as they arise. I also thought
about the XML approach but the scale of this is rather small so I'm not sure
it will be worth the time investment.



I like the idea that Doug mentioned of using the "UserForm". That was
certainly something I had not thought of (which is why I posted here to
begin with). I'll have to check back with my customer to see if they think
they want to store the data long term and refer back to it. This might work
for one of their two projects but probably not for the bigger one.



BTW, They are all using Office 2000 and above.



Thanks for the links. Any additional input would be greatly appreciated.



Tiffany

 

[Doug Robbins]

With the userform approach, it is also possible to store the data that is
entered in a database.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP