[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="""";Us er 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;Pass word="""";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

)
--
Peter Jamieson
http://tips.pjmsn.me.uk

"Graywolf" wrote in message
...
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="""";Us er 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

[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" wrote:

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;Pass word="""";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

)
--
Peter Jamieson
http://tips.pjmsn.me.uk

"Graywolf" wrote in message
...
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="""";Us er 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]

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 wrote:
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" wrote:

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;Pass word="""";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

)
--
Peter Jamieson
http://tips.pjmsn.me.uk

"Graywolf" wrote in message
...
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="""";Us er 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

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


"Peter Jamieson" wrote:

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 wrote:
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" wrote:

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;Pass word="""";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

)
--
Peter Jamieson
http://tips.pjmsn.me.uk

"Graywolf" wrote in message
...
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="""";Us er 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

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

"Peter Jamieson" wrote:

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 wrote:
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" wrote:

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;Pass word="""";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

)
--
Peter Jamieson
http://tips.pjmsn.me.uk

"Graywolf" wrote in message
...
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="""";Us er 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

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

"Peter Jamieson" wrote:

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 wrote:
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" wrote:

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;Pass word="""";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

)
--
Peter Jamieson
http://tips.pjmsn.me.uk

"Graywolf" wrote in message
...
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="""";Us er 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

[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" wrote in message
...
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="""";Us er 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

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

"Graywolf" wrote:

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.


"Peter Jamieson" wrote:

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 wrote:
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" wrote:

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;Pass word="""";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

)
--
Peter Jamieson
http://tips.pjmsn.me.uk

"Graywolf" wrote in message
...
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="""";Us er 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

[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

"Graywolf" wrote in message
...
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.

"Graywolf" wrote:

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.


"Peter Jamieson" wrote:

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 wrote:
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" wrote:

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;Pass word="""";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

)
--
Peter Jamieson
http://tips.pjmsn.me.uk

"Graywolf" wrote in message
...
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="""";Us er
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]

One other problem is that you have

..OpenDataSource _
Name:=gsDBLocation, _
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";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;Pass word="""";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 wrote:
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.


"Peter Jamieson" wrote:

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 wrote:
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" wrote:

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;Pass word="""";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

)
--
Peter Jamieson
http://tips.pjmsn.me.uk

"Graywolf" wrote in message
...
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="""";Us er 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

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

"Doug Robbins - Word MVP" wrote:

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

"Graywolf" wrote in message
...
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.

"Graywolf" wrote:

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.


"Peter Jamieson" wrote:

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 wrote:
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" wrote:

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;Pass word="""";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

)
--
Peter Jamieson
http://tips.pjmsn.me.uk

"Graywolf" wrote in message
...
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="""";Us er
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

[Graywolf]

Peter,
Thanks for catching that punctuation/syntax error. I've made the correction
to that and it looks like I am changing datasources correctly now. However, I
still am not retaining my mapped fields. The link to the knowledgebase
article shows how to return the name of a mapped field or how to return the
data value held in the mapped field once the merge is complete. However, it
does not tell you how to programmatically set the field mappings; and if I'm
reading the object browser in VB correctly for the MailMerge class in the
Word library for MappedDataFields, the properties are almost all 'readonly'
which would mean I can't set a mapped data field. The DataFieldIndex
property of MappedDataField and the Item Function of MappedDataFields
collection are not marked as Readonly. Any idea on how to use them to set a
data field mapping?

"Peter Jamieson" wrote:

One other problem is that you have

..OpenDataSource _
Name:=gsDBLocation, _
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";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;Pass word="""";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 wrote:
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.


"Peter Jamieson" wrote:

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 wrote:
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" wrote:

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;Pass word="""";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

)
--
Peter Jamieson
http://tips.pjmsn.me.uk

"Graywolf" wrote in message
...
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="""";Us er 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]

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.Cou nt) 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

Graywolf wrote:
Peter,
Thanks for catching that punctuation/syntax error. I've made the correction
to that and it looks like I am changing datasources correctly now. However, I
still am not retaining my mapped fields. The link to the knowledgebase
article shows how to return the name of a mapped field or how to return the
data value held in the mapped field once the merge is complete. However, it
does not tell you how to programmatically set the field mappings; and if I'm
reading the object browser in VB correctly for the MailMerge class in the
Word library for MappedDataFields, the properties are almost all 'readonly'
which would mean I can't set a mapped data field. The DataFieldIndex
property of MappedDataField and the Item Function of MappedDataFields
collection are not marked as Readonly. Any idea on how to use them to set a
data field mapping?

"Peter Jamieson" wrote:

One other problem is that you have

..OpenDataSource _
Name:=gsDBLocation, _
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";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;Pass word="""";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 wrote:
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.


"Peter Jamieson" wrote:

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 wrote:
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" wrote:

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;Pass word="""";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

)
--
Peter Jamieson
http://tips.pjmsn.me.uk

"Graywolf" wrote in message
...
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="""";Us er 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