[Graywolf]

This question was originally posted at end of Dec. 08, but I can't seem to
reply to that thread anymore, so I'm starting a new question and pasting in
the previous info.

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]

I obviously had issues with posting, and now there seems to be several posts.
If the moderator can flush this question, please do. Sorry for any
inconvenience.

"Graywolf" wrote:

This question was originally posted at end of Dec. 08, but I can't seem to
reply to that thread anymore, so I'm starting a new question and pasting in
the previous info.

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

[Peter Jamieson]

I've replied to the original thread.

Peter Jamieson

http://tips.pjmsn.me.uk

Graywolf wrote:
This question was originally posted at end of Dec. 08, but I can't seem to
reply to that thread anymore, so I'm starting a new question and pasting in
the previous info.

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