Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Rose B Rose B is offline
external usenet poster
 
Posts: 4
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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Rose B Rose B is offline
external usenet poster
 
Posts: 4
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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Rose B Rose B is offline
external usenet poster
 
Posts: 4
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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Rose B Rose B is offline
external usenet poster
 
Posts: 4
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

.

.

Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge with Access Marly Mailmerge 1 June 3rd 08 10:54 PM
Mail Merge .bmp from access? pope Mailmerge 2 February 16th 07 07:06 AM
Mail merge using Access Geoff Mailmerge 5 November 6th 06 02:19 PM
word opens access in mail merge. Access is already opened.=twice Sherri Mailmerge 1 February 11th 06 05:59 PM
Mail Merge from Access ken a Mailmerge 1 March 14th 05 01:07 PM


All times are GMT +1. The time now is 01:35 PM.

Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"