Merge Fields won't populate after Datasource change

[Graywolf]

If this post should be in a different forum, please let me know. I'm trying
to run a Word 2003 Mailmerge to a mailing label template. I've set up the
Mailing label mailmerge and my datasource is a stored query in an Access
database. Everything works fine with the mailmerge from Word. Now I want to
run that mailmerge from a Visual Basic 6 program, but change the datasource.
When I change the datasource I don't get anything coming out on the labels.
I'm trying to set the datasource to a query statement that I build in the
program. For my initial tests the Access database is the same and the query
statement I build is identical to the stored query statement in the database.
The fields that are returned in the queries will be identical, ultimately the
where clause will change so it will select different records. But first I
have to get the labels populated. When I run the code in VB and the new Word
doc is created with the labels the labels are blank. Here's the code and
the query statement. I recorded a Word macro to get the proper syntax for
the Opendatasource command. gsDBLocation is the fully qualified path to the
database. The msSQL variable holds the query statement. After opening the
datasource, firstrecord returns a 1 and lastrecord returns a -16 (there
should be 3 records) so it appears that the query is failing, but it's the
same as the stored query (which works) so I'm stumped. Any help would be
appreciated. Thanks in advance.

Query:
SELECT distinct REG.*, REGADDR.*, Address.* FROM REG INNER
JOIN (Address INNER JOIN REGADDR ON Address.A_ADDRCODE =
REGADDR.RA_ADDRCODE) ON REG.CODE = REGADDR.RA_REGCODE WHERE
(((REGADDR.WIRE_NOTICE)=True)) or (((REGADDR.WIRE_NOTICE_DUP)=True));

Code:
Set objWordApp = CreateObject("Word.application") 'New word.Application

If chkLabels.Value = vbChecked Then
Set objWordDoc = objWordApp.Documents.Open(C:\Labels5663.doc")
End If


objWordApp.Visible = vbTrue


With objWordDoc.MailMerge
.MainDocumentType = wdMailingLabels

.SuppressBlankLines = True

.OpenDataSource Name:=gsDBLocation, 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=gsDBLocation;Mode=Read;Extended Properties="""";Jet OLEDB:System
database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _
, SQLStatement:=msSQL, SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess

objWordDoc.MailMerge.ViewMailMergeFieldCodes = vbTrue

With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

 

[Peter Jamieson]

If you haven't made the registry change described in
http://support.microsoft.com/kb/825765/en-us
try that first.

Everything else looks OK (I can't say I've checked in detail, but the SQL
statement is only around 248 characters so it should fit into SQLStatement.
However, Word does not always record the OpenDataSource statement correctly
(e.g. it usually truncates the Connection string). You can also cut a lot of
stuff out - I'd try:

.OpenDataSource _
Name:=gsDBLocation, _
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=gsDBLocation;Mode=Read;", _
SQLStatement:=msSQL, _
SubType:=wdMergeSubTypeAccess

(you may be able to get away with

.OpenDataSource _
Name:=gsDBLocation, _
SQLStatement:=msSQL

)

 

[Graywolf]

Thanks for the feedback Peter. I will try the registry change and let you
know what happens. I didn't think I needed everything on the opendatasource
line but like I said I just recorded a macro and cut a pasted the line into
my VB code changing the arguments where needed. I know the SQL statement is
restricted to about 250 chars. Is the second SQL argument restricted to the
same length? That might give me problems if it is. Thanks again. Happy New
Year!!!

 

[Peter Jamieson]

There are two different limits: the total you can fit into the combined
SQLStatement parameters, which is I think 510 chars, and the total that
the provider/driver will actually accept, which depends partly on the
version of Word and the type of driver/provider. Some don't allow more
than about 255 altogether. I can't rember the details now. Happy new year!


Peter Jamieson

http://tips.pjmsn.me.uk

 

[Graywolf]

Peter,
Sorry for the delay. Got pulled off this and put on another project for a
while. Now I'm back to this. Tried making the registry change with no affect
on my results; still getting blank labels. As I step through the code in the
debugger when I open the Word label document with the following statement
Set objWordDoc = objWordApp.Documents.Open("C:\Labels5663.doc")
and then make it visible, I can see the 'labels' on screen in normal view
because the document is set up to use the predefined query in the Access
database as the datasource. When I go to change the datasource by setting the
SQLStatement argument within the .OpenDataSource method I lose my labels.

Do you have any other ideas?
If I cannot find a resolution here, do you think it would be possible to
alter the SQL statement in the predefined query in the Access table and then
just open the mailmerge?? If so, any idea how I would go about changing the
query in Access? or is that question for another forum? Thanks again for all
your help.

 

[Graywolf]

Peter,
Sorry for the delay. I got moved to another project for a while, now I'm
back. Tried the registry change with no affect on my results; I'm still not
getting label output. When I step through the code in the debugger I can see
my labels when I open the Word document because the template points to the
predefined query in Access as the original datasource that was used when
creating the label mailmerge. When I go to change the datasource by altering
the SQLStatement argument of the OpenDataSource method, I lose my labels.

Any other ideas?
If I cannot find a resolution here, do you think it would be possible to
change the SQL in the predefined query in Access? If I can, then, in theory I
would only have to open the labels document and the altered predefined query
would take care of itself. If you think this is possible, do you have any
idea of how to change the sql statement in a predefined Access query? or is
that a question for a different forum? Thanks again for all your help.

 

[Graywolf]

Peter,
Sorry for the delay. I got moved to another project for a while, now I'm
back. Tried the registry change with no affect on my results; I'm still not
getting label output. When I step through the code in the debugger I can see
my labels when I open the Word document because the template points to the
predefined query in Access as the original datasource that was used when
creating the label mailmerge. When I go to change the datasource by altering
the SQLStatement argument of the OpenDataSource method, I lose my labels.

Any other ideas?
If I cannot find a resolution here, do you think it would be possible to
change the SQL in the predefined query in Access? If I can, then, in theory I
would only have to open the labels document and the altered predefined query
would take care of itself. If you think this is possible, do you have any
idea of how to change the sql statement in a predefined Access query? or is
that a question for a different forum? Thanks again for all your help.

 

[Graywolf]

Peter,
Sorry for the delay. I got moved to another project for a while, now I'm
back. Tried the registry change with no affect on my results; I'm still not
getting label output. When I step through the code in the debugger I can see
my labels when I open the Word document because the template points to the
predefined query in Access as the original datasource that was used when
creating the label mailmerge. When I go to change the datasource by altering
the SQLStatement argument of the OpenDataSource method, I lose my labels.

Any other ideas?
If I cannot find a resolution here, do you think it would be possible to
change the SQL in the predefined query in Access? If I can, then, in theory I
would only have to open the labels document and the altered predefined query
would take care of itself. If you think this is possible, do you have any
idea of how to change the sql statement in a predefined Access query? or is
that a question for a different forum? Thanks again for all your help.

 

[Graywolf]

Peter,
I've found that when I change the datasource I lose my mapped fields. Could
that be the problem? If so, can you give me a clue as to a quick way to remap
the fields?
Thanks again, sorry for the multiple posts, had a browser problem and I
thought I wasn't posting when apparently I was.

 

[Doug Robbins - Word MVP]

Access has a perfectly good utility for the production of labels. Why not
just use that and automate Access from your VB6 code?

--
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

 

[Peter Jamieson]

One other problem is that you have

..OpenDataSource _
Name:=gsDBLocation, _
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=gsDBLocation;Mode=Read;", _
SQLStatement:=msSQL, _
SubType:=wdMergeSubTypeAccess

when you need

..OpenDataSource _
Name:=gsDBLocation, _
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=" & gsDBLocation & ";Mode=Read;", _
SQLStatement:=msSQL, _
SubType:=wdMergeSubTypeAccess

Sorry, missed that before.

As for the mapped fields, if you still need to do that (and you probably
will), have a look at

http://support.microsoft.com/kb/282567

for guidance on the necessary code - it's slightly more awkward than it
really needs to be.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Graywolf]

Thanks for the suggestion Doug. My end users are more comfortable with MS
Word than Access in case anything needs to be changed, so I would prefer to
stay in Word; however, I may investigate your suggestion further if i can't
get Word working the way I need.

 

[Peter Jamieson]

Yes, you have to assign the values indirectly. I'm not quite sure
whether you would be able to get the existing mappings before opening
the new data source, but if not you can probably adapt the following
example...

Sub Macro4()
'
' Try to remap field names after a data source change
'
'
Dim i As Integer
Dim intMappedFieldIndexes() As Integer
Dim strDataFieldNames() As String
Dim objMappedDataField As Word.MappedDataField
' Assume we already have a data source - save the mapped field names
With ActiveDocument.MailMerge
ReDim strDataFieldNames(.DataSource.MappedDataFields.Count) As String
For i = 1 To .DataSource.MappedDataFields.Count
strDataFieldNames(i) = .DataSource.MappedDataFields(i).DataFieldName
Next

' disconnect the existing data source
.MainDocumentType = wdNotAMergeDocument

' you'll need an OpenDataSource that works here...
.OpenDataSource <<whatever>>

' If the old field name is not in the new source, ignore the problem

On Error Resume Next

' map the names by finding the /index/ of the name in Datafields()
For i = 1 To .DataSource.MappedDataFields.Count
.DataSource.MappedDataFields(i).DataFieldIndex _
= .DataSource.DataFields(strDataFieldNames(i)).Index
Next
Err.Clear
End With

End Sub

Peter Jamieson

http://tips.pjmsn.me.uk