Multi-sourcing MM fields from Access DB

[Dennis]

(Office 2002) Okay, here's the deal. I had been requested to create a MM
document sourced on an Access database. No problem. The data source was a
single table. Fine. Then.... the requesting division needed additional
capability in the data source (as in, keeping more "stuff" in tables and
expanding what was to be placed into the Word document.) So this necessitated
using a multi-table methodology.

Now, I need to know IF there's a way to use multiple tables to source a
single Word doc. I THINK that a query is out due to the level of complexity
of the database as it (now) exists. What I THINK I need to do is, using VBA,
spin through the document looking for specific bookmarks. When I find those,
I need to do a database lookup and insert specific information into the
document at those points.

Sometimes the information will be in tables, and sometimes standard text.
What I want to know is, what methodology is best suited to accomplish my
task? I've been a database guy for over 25 years, and have a pretty fair
knowledge of Word (but not VBA behind Word; I was thinking of opening the doc
- hidden - in Access, and doing the VBA work from there).

Anyway, if some of you folks could shed some light on how this all might be
done, I'd sure appreciate it.

The actual task being performed is the creation of IT "Service Level
Agreement" documents. Each document contains a revision history, and multiple
categories of services (which appear in different sections of the document),
contact information (names, email addies, phone #'s, etc) and "external
reference documents" (including descriptions, titles, network paths, etc).
All this is specific to division, and there are 31 divisions in the agency.

Again, any assistance or ideas would be most appreciated.

 

[Peter Jamieson]

1. It certainly sounds as if MM was adequate for the requirement as
initially perceived, but not any more.

2. If you have not already done so, I would probably try to focus first on
the required outputs, and then what tool might be used to create them. (For
example, what does Access reporting not provide in this case?)

3. If you definitely need to output via Word, I think you are on the right
track if you are considering "roling your own merge", i.e. by using the Word
object model to insert stuff as you go along. If you have complete control
over the application, you can organise things how you like - e.g., if there
are large chunks of bolerpalte text and layout that you can insert, you can
consider them to be "data" for the application, keep them in .doc files (or
as bookmarked chunks of .doc files) and either insert them, or insert {
INCLUDETEXT } fields that will insert them. Probably the former. If you need
a "parent-child" style of reporting, you may be able to insert complete
tables using Word's InsertDatabase method or DATABASE fields and format them
afterwards, but will only get full control over the formatting of the data
if you insert the data yourself - there's nothing within InsertDatabase that
lets you select (for example) a numeric format or a date format for each
column.

 

[Dennis]

The reason for my wanting to get away from using Access reports is due
entirely to the complexity (and length) of each document (35-40 pages). Much
of that is indeed boilerplate, but that boilerplate keeps changing. (In the
original project, it was clearly stated that the boilerplate would almost
never change; just the division-specific information in the various tables.)
I had to create multiple subreports (set to "can grow" and "can shrink" of
course). I also had to break the document up into about four main reports
that open in sequence. That's because I ran out of room in the initial master
report. All of which creates a problematic page numbering system. And since
we also need a Table of Contents with correct numbers in THAT, I'm sure you
can see how the "scope creep" associated with this project has made things
way too complex.

Moving to Word and populating the "guts" of the document via VBA would allow
me to properly manage the ToC, page numbering, and content management for the
(supposedlyy "static") boilerplate content. Further, watermarking would
become possible ("First Draft", "Sample", etc). My mandate was to create a
document that was centrally updateable via a GUI, thus elliminating the
30-odd copies of a Word doc lying around on the network. Okay fine. Did that.
But they changed the rules in the middle of development. Further, my time
allotment for this was only 40 hours of development; fine under the original
scope, but totally inadequate once the "changes" were factored in.

*sigh*

Life in IT. Thanks for your information; I'm quite sure I'll be putting it
to good use shortly.

 

[Peter Jamieson]

<<
And since
we also need a Table of Contents with correct numbers in THAT, I'm sure you
can see how the "scope creep" associated with this project has made things
way too complex.
I certainly can.

Everything you have said says "get the data using ADO or DAO (personally I
only really know ADO) and create everything except boilerplate on-the-fly".
In Word, every other approach will eat up your time struggling with the
MailMerge object. You may be able to use Word Document Variables (in some
cases it may even make snse to use Document Properties) and { DOCVARIABLE }
and { DOCPROPERTY } fields as bookmarks that let you insert data and
reference Document variables in your main document from inserted
boilerplate, but I would not push them too hard (e.g. multiline Document
Properties are likely to cause grief as well, and these fields have limited
capacity).

Good luck!

 

[A person]

I need fun stuff

<<
And since
we also need a Table of Contents with correct numbers in THAT, I'm sure you
can see how the "scope creep" associated with this project has made things
way too complex.

I certainly can.

Everything you have said says "get the data using ADO or DAO (personally I
only really know ADO) and create everything except boilerplate on-the-fly".
In Word, every other approach will eat up your time struggling with the
MailMerge object. You may be able to use Word Document Variables (in some
cases it may even make snse to use Document Properties) and { DOCVARIABLE }
and { DOCPROPERTY } fields as bookmarks that let you insert data and
reference Document variables in your main document from inserted
boilerplate, but I would not push them too hard (e.g. multiline Document
Properties are likely to cause grief as well, and these fields have limited
capacity).

Good luck!