Mailmerge with subset of Access recordset

[Karl E. Peterson]

Hi Folks --

My apologies for the crosspost, but this one just seems to need multiple
perspectives. I've googled and googled, but not come up with a "best" way
to do this.

I have an Access 2000 database, and would like to use a subset of one its
queries as a datasource for a Word 2000 mailmerge. I found KB article
Q258512 (http://support.microsoft.com/kb/258512/), and have sort of adopted
it to fit. All but subsetting the embedded query.

I don't really want to parameterize the query directly within Access, as I'd
then need to have dialogs popping from Access, right?

Instead, I'd just like to select those records that meet some user-defined
specification. This is the code I'm using to open the datasource:

.OpenDataSource _
Name:=TheDatabase, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="QUERY qryLabels", _
SQLStatement:="SELECT * FROM qryLabels;", _
SQLStatement1:=""

That works, too! But, what I'd _really_ like to use in that SQLStatement
line is something like this:

"SELECT * FROM qryLabels WHERE (((qryLabels.ListID)=3));"

That, however, causes CPU usage to spike, and no results are ever returned.

BOTTOM LINE: Is there a way to use ADO or DAO directly within Word VBA to
select a recordset and assign that as the datasource for the mailmerge?
(The stored query itself uses several tables and is way, way over the
character limit for the OpenDataSource parameters.) Pointers to good
references also appreciated!

Thanks... Karl

 

[Karl E. Peterson]

BOTTOM LINE: Is there a way to use ADO or DAO directly within Word
VBA to select a recordset and assign that as the datasource for the
mailmerge? (The stored query itself uses several tables and is way,
way over the character limit for the OpenDataSource parameters.)
Pointers to good references also appreciated!

Well, I "solved" my problem. Not sure I think it's the best alternative,
but it certainly works. I'm now building the recordset with
straight-forward DOA, then spitting it out to a CSV in the %tmp% folder, and
using that as the datasource. It works. And works well/fast. Next thing
to try, I suppose, is seeing if it's at all portable. <g>

 

[Cindy M.]

Hi Karl,

FWIW, what you describe with the SELECT statement SHOULD work. Your syntax may
be something Word isn't comfortable with, is the only thing I can imagine.
Let's see what happens if you...

1. Set up the mail merge without the subset
2. In the Word UI, go to the Mail Merge Helper, then through the Query
Options.
3. In this interface, select the field ListId and set it to equal 3

Does this work correctly? If it does, Alt+F11 to open Word's VB Editor. Ctrl+G
for the Immediate Window. Type the following, then press Enter:
?ActiveDocument.MailMerge.DataSource.QueryString

Try using the result in the SQLStatement parameter of your code. OR let the
code finish connecting, then as the next line set the QueryString property.
One or the other should work.

My apologies for the crosspost, but this one just seems to need multiple
perspectives. I've googled and googled, but not come up with a "best" way
to do this.

I have an Access 2000 database, and would like to use a subset of one its
queries as a datasource for a Word 2000 mailmerge. I found KB article
Q258512 (http://support.microsoft.com/kb/258512/), and have sort of adopted
it to fit. All but subsetting the embedded query.

I don't really want to parameterize the query directly within Access, as I'd
then need to have dialogs popping from Access, right?

Instead, I'd just like to select those records that meet some user-defined
specification. This is the code I'm using to open the datasource:

.OpenDataSource _
Name:=TheDatabase, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="QUERY qryLabels", _
SQLStatement:="SELECT * FROM qryLabels;", _
SQLStatement1:=""

That works, too! But, what I'd _really_ like to use in that SQLStatement
line is something like this:

"SELECT * FROM qryLabels WHERE (((qryLabels.ListID)=3));"

That, however, causes CPU usage to spike, and no results are ever returned.

BOTTOM LINE: Is there a way to use ADO or DAO directly within Word VBA to
select a recordset and assign that as the datasource for the mailmerge?
(The stored query itself uses several tables and is way, way over the
character limit for the OpenDataSource parameters.) Pointers to good
references also appreciated!

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Peter Jamieson]

I'm pretty sure that with the code you posted Word will use DDE to get the
Access data - it may be worth trying an ODBC connection instead.

Peter Jamieson

 

[Karl E. Peterson]

Hi Cindy --

FWIW, what you describe with the SELECT statement SHOULD work.

Heh, figures.

Your syntax may be something Word isn't comfortable with,

That's looking to be in contention for the Understatement Of The Year award!

is the only
thing I can imagine. Let's see what happens if you...

1. Set up the mail merge without the subset
2. In the Word UI, go to the Mail Merge Helper, then through the Query
Options.
3. In this interface, select the field ListId and set it to equal 3

Does this work correctly?

Yep!!! Sure does.

If it does, Alt+F11 to open Word's VB
Editor. Ctrl+G for the Immediate Window. Type the following, then
press Enter: ?ActiveDocument.MailMerge.DataSource.QueryString

Kaboom... That instance of Word vanishes -- the IDE, and all open document
windows. At the moment I press Enter. No error messages, nothing. Not a
good situation.

Thanks... Karl

 

[Karl E. Peterson]

I'm pretty sure that with the code you posted Word will use DDE to
get the Access data -

The timeouts I encountered would surely suggest DDE, yep.

it may be worth trying an ODBC connection
instead.

Well, it's working at the moment, using DOA to prepare a temporary
text-based merge file. Much faster, too. Might be best off leaving
well-enough alone?

Thanks... Karl

 

[Peter Jamieson]

Might be best off leaving

well-enough alone?

I would leave well alone as long as...

....and you can ignore the rest, but here goes...

....I was confident that I would never get problems with CSV delimiter
characters in the data or encoding issues (both of which lead me to a
general preference for avoiding using simple delimited intermediate files).
The former problem is predictable if you know your data. The latter problem
is unlikely to come up if it hasn't already, but can occur in some case
because
a. Word uses a heuristic method to determine the encoding of some text data
sources
b. does not always get it right
c. does not have parameters that let you tell it the encoding when the file
is being used as a data source.

You ought to be able to solve both problems simply by creating a
single-table .mdb on-the-fly using ADOX (say) and stuffing your data in
there instead of a .csv. No idea how the performance would compare with the
..csv approach, nor whether using ODBC instead of DDE would improve
anything - probably not, if the problem is in the way jet executes the
query.

Peter Jamieson

 

[Cindy M.]

Hi Karl,

Kaboom... That instance of Word vanishes -- the IDE, and all open document
windows. At the moment I press Enter. No error messages, nothing. Not a
good situation.

Yikes! I've NEVER encountered that before, and I've been doing this for
fifteen years... <sigh>

If you've the patience and nerves for it, I'd be curious to know what happens
with an ODBC (rather than DDE) connection. I generally prefer ODBC because it
ports well between versions.

To test, go over the dialog box (Open Datasource) to link in the mdb file
again, this time activating the "Select method" checkbox. You should get a
list with DDE (the default) and ODBC in it. Select ODBC.

To add the queries to the list of tables, click the "Options" button and
activate "Views". Select your query and proceed as usual. Then repeat the
test.

If it still crashes on you, I'd try a setting up a brand-new Word document.
The original one could be damaged.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Karl E. Peterson]

Hi Cindy --

Yikes! I've NEVER encountered that before, and I've been doing this
for fifteen years... <sigh>

If you've the patience and nerves for it, I'd be curious to know what
happens with an ODBC (rather than DDE) connection. I generally prefer
ODBC because it ports well between versions.

Worth a shot...

To test, go over the dialog box (Open Datasource) to link in the mdb
file again, this time activating the "Select method" checkbox. You
should get a list with DDE (the default) and ODBC in it. Select ODBC.

Wow, never noticed that checkbox before! It's always the little things, eh?

To add the queries to the list of tables, click the "Options" button
and activate "Views". Select your query and proceed as usual. Then
repeat the test.

Okay, done! The Merge itself worked. When I popped into the IDE, and
attempted to print the QueryString, same result. POOF! Word went bye-bye.
Fun stuff, huh?

If it still crashes on you, I'd try a setting up a brand-new Word
document. The original one could be damaged.

Just to be clear, the above tests _were_ all done in brand-new documents.

Thanks... Karl

 

[Karl E. Peterson]

Hi Peter --

I would leave well alone as long as...

...and you can ignore the rest, but here goes...

...I was confident that I would never get problems with CSV delimiter
characters in the data

Easy enough, as I'm building the CSV on the fly. Tabs and CRs (CR/LF pair?)
are all I should care about, right?

or encoding issues

Not sure what you mean, precisely, there?

(both of which lead me to a
general preference for avoiding using simple delimited intermediate
files). The former problem is predictable if you know your data. The
latter problem is unlikely to come up if it hasn't already, but can
occur in some case because
a. Word uses a heuristic method to determine the encoding of some
text data sources
b. does not always get it right
c. does not have parameters that let you tell it the encoding when
the file is being used as a data source.

Would peeking at the CSV prep code lend any insight into these issues?

' Build textfile all as one string.
' http://vb.mvps.org/samples/StrBldr
Set txt = New CStringBuilder

' Output header with fieldnames
For i = 1 To rs.Fields.Count
txt.Append Trim$(rs.Fields(i - 1).Name)
If i < rs.Fields.Count Then
txt.Append vbTab
Else
txt.Append vbCrLf
End If
Next i

' Output each record
rs.MoveFirst
Do While Not rs.EOF
For i = 1 To rs.Fields.Count
If IsNull(rs.Fields(i - 1).Value) = False Then
txt.Append Replace$(Trim$(rs.Fields(i - 1).Value), vbCrLf, " ")
End If
If i < rs.Fields.Count Then
txt.Append vbTab
Else
txt.Append vbCrLf
End If
Next i
rs.MoveNext
Loop

You ought to be able to solve both problems simply by creating a
single-table .mdb on-the-fly using ADOX (say) and stuffing your
data in there instead of a .csv. No idea how the performance would
compare with the .csv approach, nor whether using ODBC instead of DDE
would improve anything - probably not, if the problem is in the way
jet executes the query.

Hmmmm, creating a brand new MDB, eh? That's another thought. Hard to
imagine it'd compare on time alone, but if you see issues above I may need
to consider that.

Thanks... Karl

 

[Peter Jamieson]

Easy enough, as I'm building the CSV on the fly. Tabs and CRs (CR/LF

pair?)
are all I should care about, right?

Would peeking at the CSV prep code lend any insight into these issues?

In theory you have to consider field delimiters (i.e. tabs in your
scenario), record delimiters (i.e. CRLFs in your scenario) and text
delimiters, i.e. usually double-quote characters, which you will probably
have to double up, so you may need a second Replace$

Not sure what you mean, precisely, there?

Long partial explanation follows...

You know when you open a text file in Word, it sometimes pops up an
"encoding" dialog box if it can't identify the character encoding (Windows,
UTF-8, 16-bit Unicode etc., all that stuff)?

Perhaps not, in which case, you probably don't have to deal with this
particular problem. But in the general case, sometimes Word does pop up such
a dialog box because it has decided that it cannot unambiguously identify
the text file's character encoding.

For example, if a text file starts with a UTF-8 Byte Order Mark (BOM) and
otherwise conforms to the UTF-8 spec., Word will correctly identify it as
UTF-8. If the file doesn't have the BOM, Word may decide it can't identify
the encoding and pop up the box.

In the case where
a. you just want to open text files using Word
b. you know what the encoding should be

then you can avoid this dialog box by specifying the encoding when you open
the file (there's a parameter in the relevant Open method).

However, when you are using a text file as a data source, there is no way to
specify the encoding in a parameter (e.g. there is no such parameter in the
OpenDataSource method call).

The trouble is that because Word uses a heuristic method (as far as I know -
I would guess it uses the standard Win32 routines that do this stuff) to try
to determine encoding, even files that you know are 100% ANSI encoded can be
misrecognised, at which point an automated process can suddenly be
interrupted by an unexpected dialog box or whatever.

In other words, even thoguh two data files may have the same encoding and
conform to the same CSV files structure with all the right field/record/text
delimiters, Word may open one without asking and pop up a dialog box when it
tries to open the other one.

I've never got to the bottom of this. I can't say I've experimented
exhaustively, but so many factors are potentially involved, e.g.
a. what method is Word actually using when it opens a text/CSV data file?
(in Word 2000, it may use its internal text converter, or it may use ODBC,
if ODBC has been installed and so on. If Word uses ODBC to open a text file,
it checks a SCHEMA.INI file for layout, delimiter, and encoding information.
In Word 2002/2003, Word may use OLEDB to open a text file, but it isn't
completely clear to me what actually pops up the delimiter dialog box and
generally asks twice for delimter info.)
b. default system encoding
c. actual file encoding
d. what heuristics does Word/Win32 actually use? Large numbers of fields,
long records, and unexpected spaces/characters seem to confuse it.

If your application really has to avoid these uncertanties, you can consider
opening your .csv in Word, specifying the correct encoding, and save it in
Word .doc format, then use that as the data source. But then you may
encounter other performance prblems...

Just to complicate things a bit further, if your data is actually
Unicode-encoded and uses characters outside the ANSI/Windows character set,
ODBC won't help either.

Peter Jamieson

 

[Cindy M.]

Hi Karl,

Wow, never noticed that checkbox before! It's always the little things, eh?
<g>

Yeah. I just discovered the "Show updates" box in WinXP "Add or Remove
programs" the other day...

When I popped into the IDE, and
attempted to print the QueryString, same result. POOF! Word went bye-bye.
Fun stuff, huh?

Yep How about starting Word in Safe Mode (hold down the Ctrl key when
starting it)?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Karl E. Peterson]

Hi Cindy --

Yeah. I just discovered the "Show updates" box in WinXP "Add or Remove
programs" the other day...

Yep How about starting Word in Safe Mode (hold down the Ctrl key
when starting it)?

POOF! I think I need to stick with "good enough", eh?

Thanks again... Karl

 

[Karl E. Peterson]

In theory you have to consider field delimiters (i.e. tabs in your
scenario), record delimiters (i.e. CRLFs in your scenario) and text
delimiters, i.e. usually double-quote characters, which you will
probably have to double up, so you may need a second Replace$

Ugh... Not sure the db will have any double-quotes, but it certainly may
have some single-quotes. Dang, well, if stuff starts breaking down, I think
I'll look at moving to creation of the new MDB instead. If I do that
locally, in temp folders, it should be fairly low-impact and no threat to
multiuse.

Long partial explanation follows...

You know when you open a text file in Word, it sometimes pops up an
"encoding" dialog box if it can't identify the character encoding
(Windows, UTF-8, 16-bit Unicode etc., all that stuff)?

Ahhhhh... We USians are mercifully naive on that score, as it rarely if
ever confronts. Yes, I have had to deal with it in other contexts, but
never in this one, so I think I'll just close my eyes to it for the moment

The trouble is that because Word uses a heuristic method (as far as I
know - I would guess it uses the standard Win32 routines that do this
stuff) to try to determine encoding, even files that you know are
100% ANSI encoded can be misrecognised, at which point an automated
process can suddenly be interrupted by an unexpected dialog box or
whatever.

In other words, even thoguh two data files may have the same encoding
and conform to the same CSV files structure with all the right
field/record/text delimiters, Word may open one without asking and
pop up a dialog box when it tries to open the other one.

Well, I'll definitely keep this in mind, too, and use it as another excuse
to go new-MDB if things start breaking down.

I've never got to the bottom of this. I can't say I've experimented
exhaustively, but so many factors are potentially involved, e.g.
a. what method is Word actually using when it opens a text/CSV data
file? (in Word 2000, it may use its internal text converter, or it
may use ODBC, if ODBC has been installed and so on. If Word uses ODBC
to open a text file, it checks a SCHEMA.INI file for layout,
delimiter, and encoding information.

That might present a possible test, using filemon, eh?

Just to complicate things a bit further, if your data is actually
Unicode-encoded and uses characters outside the ANSI/Windows
character set, ODBC won't help either.

Heh, wow... Well, they are Unicode fields in Access, although VB's UniMess
bastardizes them to ANSI when writing the textfile, so that looks like
another bullet successfully dodged. Good grief, though. "Supposed to be
simple," eh? Sheesh. <g>

Thanks again... Karl

 

[Cindy M.]

Hi Karl,

POOF! I think I need to stick with "good enough", eh?

Sure thing

Cindy Meister