A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Mail Merge and Access



 
 
Thread Tools Display Modes
  #1  
Old March 31st, 2010, 06:24 PM posted to microsoft.public.word.mailmerge.fields
Rose B
external usenet poster
 
Posts: 93
Default Mail Merge and Access

I have an Access application distributed to users of Office 2003 and Office
2007 via a runtime 2007 front end. The application needs to trigger a Mail
Merge
which will use query results in back end, which is protected by a password.

I have coded VB to cope with this in an Office 2007 environment - but
when running with Office 2003 Word is asking for the data source when
initially opening the mail merge document from Accesss; when opened directly
it does not ask for the data source and when responding with "No" to the SQL
prompt it opens fine. You don't actually have to choose a data source when
asked, just click on "Open" and all is well. Can anyone help?

My code is

With myWordDoc.MailMerge
.OpenDataSource Name:="", ConfirmConversions:=False,
ReadOnly:=False, LinkToSource:=False, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=0, _
Connection:="DSN=MS Access Database;DBQ=" & DataSource &
";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;PWD=mypass word;UID=admin;" _
, SQLStatement:=mySQL, SQLStatement1:="", SubType:=1
.Destination = myDestination
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1
.LastRecord = -16
End With
.Execute Pause:=False
End With
  #2  
Old March 31st, 2010, 10:16 PM posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP
external usenet poster
 
Posts: 8,239
Default Mail Merge and Access

Maybe getting rid of the SQL prompt will overcome the problem.

See http://support.microsoft.com?kbid=825765

--
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, originally posted via msnews.microsoft.com

"Rose B" wrote in message
...
I have an Access application distributed to users of Office 2003 and
Office
2007 via a runtime 2007 front end. The application needs to trigger a Mail
Merge
which will use query results in back end, which is protected by a
password.

I have coded VB to cope with this in an Office 2007 environment - but
when running with Office 2003 Word is asking for the data source when
initially opening the mail merge document from Accesss; when opened
directly
it does not ask for the data source and when responding with "No" to the
SQL
prompt it opens fine. You don't actually have to choose a data source when
asked, just click on "Open" and all is well. Can anyone help?

My code is

With myWordDoc.MailMerge
.OpenDataSource Name:="", ConfirmConversions:=False,
ReadOnly:=False, LinkToSource:=False, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=0, _
Connection:="DSN=MS Access Database;DBQ=" & DataSource &
";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;PWD=mypass word;UID=admin;" _
, SQLStatement:=mySQL, SQLStatement1:="", SubType:=1
.Destination = myDestination
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1
.LastRecord = -16
End With
.Execute Pause:=False
End With


  #3  
Old March 31st, 2010, 10:42 PM posted to microsoft.public.word.mailmerge.fields
Rose B
external usenet poster
 
Posts: 93
Default Mail Merge and Access

Thanks for your thought Doug - but have done that; it must be something
really simple but it is driving me crackers!!! Any other thoughts very
welcome.

Rose

"Doug Robbins - Word MVP" wrote:

Maybe getting rid of the SQL prompt will overcome the problem.

See http://support.microsoft.com?kbid=825765

--
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, originally posted via msnews.microsoft.com

"Rose B" wrote in message
...
I have an Access application distributed to users of Office 2003 and
Office
2007 via a runtime 2007 front end. The application needs to trigger a Mail
Merge
which will use query results in back end, which is protected by a
password.

I have coded VB to cope with this in an Office 2007 environment - but
when running with Office 2003 Word is asking for the data source when
initially opening the mail merge document from Accesss; when opened
directly
it does not ask for the data source and when responding with "No" to the
SQL
prompt it opens fine. You don't actually have to choose a data source when
asked, just click on "Open" and all is well. Can anyone help?

My code is

With myWordDoc.MailMerge
.OpenDataSource Name:="", ConfirmConversions:=False,
ReadOnly:=False, LinkToSource:=False, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=0, _
Connection:="DSN=MS Access Database;DBQ=" & DataSource &
";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;PWD=mypass word;UID=admin;" _
, SQLStatement:=mySQL, SQLStatement1:="", SubType:=1
.Destination = myDestination
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1
.LastRecord = -16
End With
.Execute Pause:=False
End With


  #4  
Old March 31st, 2010, 11:10 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default Mail Merge and Access

Off the top of my head, try changing

, SQLStatement:=mySQL, SQLStatement1:="", SubType:=1


to

, SQLStatement:=mySQL, SQLStatement1:="", SubType:=8

(8 is wdMergeSubTypeWord2000)

There are one or two signs that Word 2007 deals with ODBC data sources
differently differently from Word 2003, but you always /used to/ have to
use wdMergeSubTypeWord2000 to make an ODBC connection.


Peter Jamieson

http://tips.pjmsn.me.uk

On 31/03/2010 22:42, Rose B wrote:
Thanks for your thought Doug - but have done that; it must be something
really simple but it is driving me crackers!!! Any other thoughts very
welcome.

Rose

"Doug Robbins - Word MVP" wrote:

Maybe getting rid of the SQL prompt will overcome the problem.

See http://support.microsoft.com?kbid=825765

--
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, originally posted via msnews.microsoft.com

"Rose wrote in message
...
I have an Access application distributed to users of Office 2003 and
Office
2007 via a runtime 2007 front end. The application needs to trigger a Mail
Merge
which will use query results in back end, which is protected by a
password.

I have coded VB to cope with this in an Office 2007 environment - but
when running with Office 2003 Word is asking for the data source when
initially opening the mail merge document from Accesss; when opened
directly
it does not ask for the data source and when responding with "No" to the
SQL
prompt it opens fine. You don't actually have to choose a data source when
asked, just click on "Open" and all is well. Can anyone help?

My code is

With myWordDoc.MailMerge
.OpenDataSource Name:="", ConfirmConversions:=False,
ReadOnly:=False, LinkToSource:=False, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=0, _
Connection:="DSN=MS Access Database;DBQ="& DataSource&
";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;PWD=mypass word;UID=admin;" _
, SQLStatement:=mySQL, SQLStatement1:="", SubType:=1
.Destination = myDestination
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1
.LastRecord = -16
End With
.Execute Pause:=False
End With


  #5  
Old March 31st, 2010, 11:11 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default Mail Merge and Access

Off the top of my head, try changing

, SQLStatement:=mySQL, SQLStatement1:="", SubType:=1


to

, SQLStatement:=mySQL, SQLStatement1:="", SubType:=8

(8 is wdMergeSubTypeWord2000)

There are one or two signs that Word 2007 deals with ODBC data sources
differently differently from Word 2003, but you always /used to/ have to
use wdMergeSubTypeWord2000 to make an ODBC connection.


Peter Jamieson

http://tips.pjmsn.me.uk

On 31/03/2010 22:42, Rose B wrote:
Thanks for your thought Doug - but have done that; it must be something
really simple but it is driving me crackers!!! Any other thoughts very
welcome.

Rose

"Doug Robbins - Word MVP" wrote:

Maybe getting rid of the SQL prompt will overcome the problem.

See http://support.microsoft.com?kbid=825765

--
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, originally posted via msnews.microsoft.com

"Rose wrote in message
...
I have an Access application distributed to users of Office 2003 and
Office
2007 via a runtime 2007 front end. The application needs to trigger a Mail
Merge
which will use query results in back end, which is protected by a
password.

I have coded VB to cope with this in an Office 2007 environment - but
when running with Office 2003 Word is asking for the data source when
initially opening the mail merge document from Accesss; when opened
directly
it does not ask for the data source and when responding with "No" to the
SQL
prompt it opens fine. You don't actually have to choose a data source when
asked, just click on "Open" and all is well. Can anyone help?

My code is

With myWordDoc.MailMerge
.OpenDataSource Name:="", ConfirmConversions:=False,
ReadOnly:=False, LinkToSource:=False, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=0, _
Connection:="DSN=MS Access Database;DBQ="& DataSource&
";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;PWD=mypass word;UID=admin;" _
, SQLStatement:=mySQL, SQLStatement1:="", SubType:=1
.Destination = myDestination
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1
.LastRecord = -16
End With
.Execute Pause:=False
End With


  #6  
Old April 1st, 2010, 08:41 AM posted to microsoft.public.word.mailmerge.fields
Rose B
external usenet poster
 
Posts: 93
Default Mail Merge and Access

BRILLIANT!!!!!!!!! Peter - you have saved me from the lunatic asylum (at
least for now!). Thank you so much.

Is there anywhere I should have looked to find this out? I had tried
searching out documentation/help butu could not find anything on what the
various values are.

Thanks again

Rose

"Peter Jamieson" wrote:

Off the top of my head, try changing

, SQLStatement:=mySQL, SQLStatement1:="", SubType:=1


to

, SQLStatement:=mySQL, SQLStatement1:="", SubType:=8

(8 is wdMergeSubTypeWord2000)

There are one or two signs that Word 2007 deals with ODBC data sources
differently differently from Word 2003, but you always /used to/ have to
use wdMergeSubTypeWord2000 to make an ODBC connection.


Peter Jamieson

http://tips.pjmsn.me.uk

On 31/03/2010 22:42, Rose B wrote:
Thanks for your thought Doug - but have done that; it must be something
really simple but it is driving me crackers!!! Any other thoughts very
welcome.

Rose

"Doug Robbins - Word MVP" wrote:

Maybe getting rid of the SQL prompt will overcome the problem.

See http://support.microsoft.com?kbid=825765

--
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, originally posted via msnews.microsoft.com

"Rose wrote in message
...
I have an Access application distributed to users of Office 2003 and
Office
2007 via a runtime 2007 front end. The application needs to trigger a Mail
Merge
which will use query results in back end, which is protected by a
password.

I have coded VB to cope with this in an Office 2007 environment - but
when running with Office 2003 Word is asking for the data source when
initially opening the mail merge document from Accesss; when opened
directly
it does not ask for the data source and when responding with "No" to the
SQL
prompt it opens fine. You don't actually have to choose a data source when
asked, just click on "Open" and all is well. Can anyone help?

My code is

With myWordDoc.MailMerge
.OpenDataSource Name:="", ConfirmConversions:=False,
ReadOnly:=False, LinkToSource:=False, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=0, _
Connection:="DSN=MS Access Database;DBQ="& DataSource&
";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;PWD=mypass word;UID=admin;" _
, SQLStatement:=mySQL, SQLStatement1:="", SubType:=1
.Destination = myDestination
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1
.LastRecord = -16
End With
.Execute Pause:=False
End With

.

  #7  
Old April 1st, 2010, 10:03 AM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default Mail Merge and Access

Is there anywhere I should have looked to find this out?

There is an article at

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

but I'm not sure it would have been easy to find given your starting point.

butu could not find anything on what the
various values are.


Microsoft eventually "documented" the Subtype value at
http://support.microsoft.com/kb/324378
and
http://support.microsoft.com/kb/827891

but they tell you almost nothing other than the names of the constants
in the relevant enumeration. The tests I have done suggest that the
Subtype parameter is probably only significant in a limited range of
cases (e.g. where you're trying to force Word 2000 behaviour, and in
Word 2007 even that does not work in the case where you are trying to
connect using DDE). FWIW, many of the parameters of OpenDataSource have
no apparent function - in the case of the first few parameters, I would
guess that the designer made the reasonable starting-point assumption
that much the same parameters would be needed as Documents.Open (for
example), but I don't think that Format, ConfirmConversions, ReadOnly,
AddToRecentFiles or Revert are ever used, and I have yet to see a
situation where "OpenExclusive" is used.

Peter Jamieson


http://tips.pjmsn.me.uk

On 01/04/2010 08:41, Rose B wrote:
BRILLIANT!!!!!!!!! Peter - you have saved me from the lunatic asylum (at
least for now!). Thank you so much.

Is there anywhere I should have looked to find this out? I had tried
searching out documentation/help butu could not find anything on what the
various values are.

Thanks again

Rose

"Peter Jamieson" wrote:

Off the top of my head, try changing

, SQLStatement:=mySQL, SQLStatement1:="", SubType:=1


to

, SQLStatement:=mySQL, SQLStatement1:="", SubType:=8

(8 is wdMergeSubTypeWord2000)

There are one or two signs that Word 2007 deals with ODBC data sources
differently differently from Word 2003, but you always /used to/ have to
use wdMergeSubTypeWord2000 to make an ODBC connection.


Peter Jamieson

http://tips.pjmsn.me.uk

On 31/03/2010 22:42, Rose B wrote:
Thanks for your thought Doug - but have done that; it must be something
really simple but it is driving me crackers!!! Any other thoughts very
welcome.

Rose

"Doug Robbins - Word MVP" wrote:

Maybe getting rid of the SQL prompt will overcome the problem.

See http://support.microsoft.com?kbid=825765

--
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, originally posted via msnews.microsoft.com

"Rose wrote in message
...
I have an Access application distributed to users of Office 2003 and
Office
2007 via a runtime 2007 front end. The application needs to trigger a Mail
Merge
which will use query results in back end, which is protected by a
password.

I have coded VB to cope with this in an Office 2007 environment - but
when running with Office 2003 Word is asking for the data source when
initially opening the mail merge document from Accesss; when opened
directly
it does not ask for the data source and when responding with "No" to the
SQL
prompt it opens fine. You don't actually have to choose a data source when
asked, just click on "Open" and all is well. Can anyone help?

My code is

With myWordDoc.MailMerge
.OpenDataSource Name:="", ConfirmConversions:=False,
ReadOnly:=False, LinkToSource:=False, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=0, _
Connection:="DSN=MS Access Database;DBQ="& DataSource&
";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;PWD=mypass word;UID=admin;" _
, SQLStatement:=mySQL, SQLStatement1:="", SubType:=1
.Destination = myDestination
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1
.LastRecord = -16
End With
.Execute Pause:=False
End With

.

  #8  
Old April 1st, 2010, 11:33 AM posted to microsoft.public.word.mailmerge.fields
Rose B
external usenet poster
 
Posts: 93
Default Mail Merge and Access

Thanks again Peter. I had played around with many of the variables, and will
probably do some more tests at some point. It would be good to have access to
a (probably massive) Microsoft Office Reference Manual with a good index/ToC!

"Peter Jamieson" wrote:

Is there anywhere I should have looked to find this out?


There is an article at

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

but I'm not sure it would have been easy to find given your starting point.

butu could not find anything on what the
various values are.


Microsoft eventually "documented" the Subtype value at
http://support.microsoft.com/kb/324378
and
http://support.microsoft.com/kb/827891

but they tell you almost nothing other than the names of the constants
in the relevant enumeration. The tests I have done suggest that the
Subtype parameter is probably only significant in a limited range of
cases (e.g. where you're trying to force Word 2000 behaviour, and in
Word 2007 even that does not work in the case where you are trying to
connect using DDE). FWIW, many of the parameters of OpenDataSource have
no apparent function - in the case of the first few parameters, I would
guess that the designer made the reasonable starting-point assumption
that much the same parameters would be needed as Documents.Open (for
example), but I don't think that Format, ConfirmConversions, ReadOnly,
AddToRecentFiles or Revert are ever used, and I have yet to see a
situation where "OpenExclusive" is used.

Peter Jamieson


http://tips.pjmsn.me.uk

On 01/04/2010 08:41, Rose B wrote:
BRILLIANT!!!!!!!!! Peter - you have saved me from the lunatic asylum (at
least for now!). Thank you so much.

Is there anywhere I should have looked to find this out? I had tried
searching out documentation/help butu could not find anything on what the
various values are.

Thanks again

Rose

"Peter Jamieson" wrote:

Off the top of my head, try changing

, SQLStatement:=mySQL, SQLStatement1:="", SubType:=1


to

, SQLStatement:=mySQL, SQLStatement1:="", SubType:=8

(8 is wdMergeSubTypeWord2000)

There are one or two signs that Word 2007 deals with ODBC data sources
differently differently from Word 2003, but you always /used to/ have to
use wdMergeSubTypeWord2000 to make an ODBC connection.


Peter Jamieson

http://tips.pjmsn.me.uk

On 31/03/2010 22:42, Rose B wrote:
Thanks for your thought Doug - but have done that; it must be something
really simple but it is driving me crackers!!! Any other thoughts very
welcome.

Rose

"Doug Robbins - Word MVP" wrote:

Maybe getting rid of the SQL prompt will overcome the problem.

See http://support.microsoft.com?kbid=825765

--
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, originally posted via msnews.microsoft.com

"Rose wrote in message
...
I have an Access application distributed to users of Office 2003 and
Office
2007 via a runtime 2007 front end. The application needs to trigger a Mail
Merge
which will use query results in back end, which is protected by a
password.

I have coded VB to cope with this in an Office 2007 environment - but
when running with Office 2003 Word is asking for the data source when
initially opening the mail merge document from Accesss; when opened
directly
it does not ask for the data source and when responding with "No" to the
SQL
prompt it opens fine. You don't actually have to choose a data source when
asked, just click on "Open" and all is well. Can anyone help?

My code is

With myWordDoc.MailMerge
.OpenDataSource Name:="", ConfirmConversions:=False,
ReadOnly:=False, LinkToSource:=False, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=0, _
Connection:="DSN=MS Access Database;DBQ="& DataSource&
";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;PWD=mypass word;UID=admin;" _
, SQLStatement:=mySQL, SQLStatement1:="", SubType:=1
.Destination = myDestination
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1
.LastRecord = -16
End With
.Execute Pause:=False
End With

.

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:25 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.