doc.Mailmerge.datasource.querystring=strSQL; Command Failed: Run-Time error 4198

[weisinator]

Automated mail merge, I thought this would be easy, lol.

MS Access 2000, Word 2002. Executing from Word VBA custom form.

I currently populate my custom form's group selection listbox with the
recordset. I know I have a connection to the database, just not with
the mail merge.

The group list selected value is used in the SQL statement I would
like to use for pulling mail merge records.

The SQL statement is valid (though ugly) and pulls data from the
tables as intended when I use it within Access.

However, if I exclude the "SQLStatement" parameter from the
OpenDataSource, it pops up with a table. I want to avoid that, so I
threw some generic code in there.

My problem comes with the document.mailmerge.datasource.querystring
property. I get "command failed" when I try to set it equal to strQry.
Doesn't matter if SQLStatement is populated or not.

Code:
-----------------------------------------------------------------------------------
Private Sub btnBeginMerge_Click()
Set docMerge = ActiveDocument

Dim strQry As String

strQry = "SELECT g.Grp, g.Ppl, " _
& "p.Ttl, p.Frstnm, p.Mddlnm, p.Lstnm, " _
& "a.Cmpny, a.Strt1, a.Strt2, a.Cty, a.Stt, a.Zp " _
& "FROM Addrss AS a, Ppl AS p, GrpMmbr AS g " _
& "WHERE g.Grp = " & drpGrpLst.Value & " " _
& "AND g.Ppl = p.Id " _
& "AND p.Id = a.Ppl;"

Debug.Print strQry

With docMerge.MailMerge
.MainDocumentType = wdFormLetters

.OpenDataSource Name:= _
dbPath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=" & dbPath & ";Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase Password="""";Jet", _
SQLStatement:="SELECT * FROM `MailMergeList`", _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess

'============
' error here!
'============
.DataSource.QueryString = strQry

With .Fields
.Add _
Range:=Selection.Range, _
Name:="FrstNm"
.Add _
Range:=Selection.Range, _
Name:="Grp"
End With

With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord

End With
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute pause:=False

End With

End Sub
-----------------------------------------------------

Since I know I can use ADO, I'm sure I could make/drop temp tables for
merging purposes, but that is a scenario I would love to avoid if at
all possible.

Any suggestions?

 

[Peter Jamieson]

Four possibilities, none of them entirely convincing:
a. for some reason you may not be able to assign anything to Querystring.
However, I have just checked here and it seems OK. Easily tested with e.g.
"SELECT * FROM [Addrss]"
b. your query is longer than around 255 chars. But it doesn't seem to be
unless drpGrpLst.Value was a string around 50 characters long
c. g.Grp is supposed to be a string value, in which case you need to put
single quotes around drpGrpLst.Value , e.g. (and then you obviously need to
be careful about quotes in the string etc.)

& "WHERE g.Grp = '" & drpGrpLst.Value & "' " _

But I think you would have got that right.
d. the syntax works in Access but not via OLE DB. For example

SELECT * FROM Addrss does not work from Word but AFAIK it is OK in Access;
SELECT * FROM [Addrss] works in both. However, as far as I know if you alias
your table names as you have then the syntax works OK in Word. Might be
worth just checking that.

Peter Jamieson

 

[weisinator]

On Feb 13, 12:46 pm, "Peter Jamieson"

SELECT * FROM Addrss does not work from Word but AFAIK it is OK in Access;
SELECT * FROM [Addrss] works in both. However, as far as I know if you alias
your table names as you have then the syntax works OK in Word. Might be
worth just checking that.

Peter Jamieson

I changed one line in the query:

& "FROM Addrss AS a, Ppl AS p, GrpMmbr AS g " _

to:

& "FROM [Addrss] AS a, [Ppl] AS p, [GrpMmbr] AS g " _

It works now!

Thank you Peter!