Pass MailMerge.CreateDataSource to document from Access

R

richardb

I create a query in Access and then use "GetObject", etc. to Activate a Word
document and then runautomacro, which contains the code to run the document
with mailmerge back to my Access query as the data source. I could use some
help with a complete example of "objWord.MailMerge.CreateDataSource", where
the data source is my Access query.

Another important question: In a multi-user environment, can more than one
Access user activate the same Word Document, pass a [unique] data source (a
query) and each one run their job at the same time? Many thanks.
 
R

richardb

I am dropping the second part of my question, because we have decided to
replicate the project on each work station. However, I would be grateful to
see an example per my first question.
 
C

Cindy M -WordMVP-

Hi =?Utf-8?B?cmljaGFyZGI=?=,
I create a query in Access and then use "GetObject", etc. to Activate a Word
document and then runautomacro, which contains the code to run the document
with mailmerge back to my Access query as the data source. I could use some
help with a complete example of "objWord.MailMerge.CreateDataSource", where
the data source is my Access query.

Another important question: In a multi-user environment, can more than one
Access user activate the same Word Document, pass a [unique] data source (a
query) and each one run their job at the same time?
Click to expand...
A note on this point: the answer would be no. But you could (and should, even
with the new scenario) set up a TEMPLATE. Your automation code then uses Set
doc = wdobj.Documents.Add("TemplatePathAndName") to create the new merge
document from the template.

It's difficult to give you any specific help with the syntax for OpenDataSource
because it can vary so widely depending on Word version, and which connection
method is used. The best way to get a handle on this is to

1. Test the available connection methods for the Office version involved. (Tell
me which you have, and I'll tell you how to do this)

2. Record making the connection in a macro in Word. You can this port this to
your Access VBA code.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :)
 
R

richardb

Cindy,

Running a macro in my Word 2003 environment showed the script I will use.
However, it created a first line, which was

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

Do I need this prior to my "objWord.MailMerge.CreateDataSource, etc."?

Otherwise I will try this in a little while and let you know. Thank you.

Cindy M -WordMVP- said:
Hi =?Utf-8?B?cmljaGFyZGI=?=,
I create a query in Access and then use "GetObject", etc. to Activate a Word
document and then runautomacro, which contains the code to run the document
with mailmerge back to my Access query as the data source. I could use some
help with a complete example of "objWord.MailMerge.CreateDataSource", where
the data source is my Access query.

Another important question: In a multi-user environment, can more than one
Access user activate the same Word Document, pass a [unique] data source (a
query) and each one run their job at the same time?
Click to expand...
A note on this point: the answer would be no. But you could (and should, even
with the new scenario) set up a TEMPLATE. Your automation code then uses Set
doc = wdobj.Documents.Add("TemplatePathAndName") to create the new merge
document from the template.

It's difficult to give you any specific help with the syntax for OpenDataSource
because it can vary so widely depending on Word version, and which connection
method is used. The best way to get a handle on this is to

1. Test the available connection methods for the Office version involved. (Tell
me which you have, and I'll tell you how to do this)

2. Record making the connection in a macro in Word. You can this port this to
your Access VBA code.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)


This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :)
Click to expand...
 
R

richardb

Cindy, I tried to pass the string that I got from recording a macro but it is
too long to pass (get error that string is over 255). Here it is. I'm
wondering what I can leave out. The environment is Windows XP using Office
2003. The connection method is from Access to Word by using:

Set objWord = GetObject(txtEncounterForm, "Word.Document")

Here is the open data source string:

objWord.MailMerge.OpenDataSource Name:="F:\Apps\Scanning.mdb", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=F:\Apps\Scanning.mdb;Mode=Read;Extended Properties="""";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database
Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database " _
, SQLStatement:="SELECT * FROM `qryEncounterForms`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess

Thank you...

Cindy M -WordMVP- said:
Hi =?Utf-8?B?cmljaGFyZGI=?=,
I create a query in Access and then use "GetObject", etc. to Activate a Word
document and then runautomacro, which contains the code to run the document
with mailmerge back to my Access query as the data source. I could use some
help with a complete example of "objWord.MailMerge.CreateDataSource", where
the data source is my Access query.

Another important question: In a multi-user environment, can more than one
Access user activate the same Word Document, pass a [unique] data source (a
query) and each one run their job at the same time?
Click to expand...
A note on this point: the answer would be no. But you could (and should, even
with the new scenario) set up a TEMPLATE. Your automation code then uses Set
doc = wdobj.Documents.Add("TemplatePathAndName") to create the new merge
document from the template.

It's difficult to give you any specific help with the syntax for OpenDataSource
because it can vary so widely depending on Word version, and which connection
method is used. The best way to get a handle on this is to

1. Test the available connection methods for the Office version involved. (Tell
me which you have, and I'll tell you how to do this)

2. Record making the connection in a macro in Word. You can this port this to
your Access VBA code.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)


This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :)
Click to expand...
 
C

Cindy M -WordMVP-

Hi =?Utf-8?B?cmljaGFyZGI=?=,
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

Do I need this prior to my "objWord.MailMerge.CreateDataSource, etc."?
Click to expand...
No. Word will automatically default to this type when you connect the
data source. But for the sake of clarity in your code, it wouldn't be a
bad idea to include it :)
I tried to pass the string that I got from recording a macro but it is
too long to pass (get error that string is over 255). Here it is. I'm
wondering what I can leave out. The environment is Windows XP using Office
2003. The connection method is from Access to Word by using:

Set objWord = GetObject(txtEncounterForm, "Word.Document")
Click to expand...
Yeah, OLE connection strings can really get out of hand. It should go
without the entire thing following Connection:=. Try removing the
following, but you may have to experiment a bit

Extended Properties="""";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Database
Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database

If it continues to give you problems, you might want to try an ODBC
connection, instead. One main advantage with those is that they work for
Word 97 through Word 2003, and are faster than OLE DB. If you want to do
that, let me know and I can tell you how to record a macro for that. Or
you'll find the details in the Word 2002/2003 section of the Mail merge
FAQ on my website.
Here is the open data source string:

objWord.MailMerge.OpenDataSource Name:="F:\Apps\Scanning.mdb", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=F:\Apps\Scanning.mdb;Mode=Read;Extended Properties="""";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database
Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database " _
, SQLStatement:="SELECT * FROM `qryEncounterForms`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
Click to expand...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail :)
 
R

richardb

Hello Cindy,

Removing the segments you suggested, plus the password strings that I don't
need allowed me to pass the "CreateDataSource" from Access to my MailMerge
document. This is very helpful because many different locations will use the
mail merge document and this avoids manually reseting the Open Data Source
each time I distribute a revised version from a master copy. However, I only
need to reset the data path when I install a revision of the document. So,
can you help me wilth how to retrieve the current data path from my "objWord"
so I don't have to update this unless my test indicates it has changed?
Thanks much...
 
C

Cindy M -WordMVP-

Hi =?Utf-8?B?cmljaGFyZGI=?=,
Removing the segments you suggested, plus the password strings that I don't
need allowed me to pass the "CreateDataSource" from Access to my MailMerge
document. This is very helpful because many different locations will use the
mail merge document and this avoids manually reseting the Open Data Source
each time I distribute a revised version from a master copy. However, I only
need to reset the data path when I install a revision of the document. So,
can you help me wilth how to retrieve the current data path from my "objWord"
so I don't have to update this unless my test indicates it has changed?
Click to expand...
(as long as it's a Word document, which it is in the sample code you showe me)
objWord.Path will return the path of the current document (without the
trailing backslash you'd need to use it later on).

Is that what you're asking me?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :)
 
R

richardb

Sorry if I wasn't clear; I hope I can do better: I want to retrieve the the
data source path from the Word document (not the Path where the document is
located). This way, Access "sees" that the Merge Word document data path has
been previously set to "F:\WrongFolder\Scanning.mdb", my Access code would
update data source in the Word ducument (using the script we have been
discussing) to "F:\CorrectFolder\Scanning.mdb". Thank you.
 
C

Cindy M -WordMVP-

Hi =?Utf-8?B?cmljaGFyZGI=?=,
I want to retrieve the the
data source path from the Word document (not the Path where the document is
located). This way, Access "sees" that the Merge Word document data path has
been previously set to "F:\WrongFolder\Scanning.mdb", my Access code would
update data source in the Word ducument (using the script we have been
discussing) to "F:\CorrectFolder\Scanning.mdb"
Click to expand...
Hmmm. I think the best thing for you to do would be to write the path to a
document VARIABLE (look that up in the Word VBA help, it's an object in the
object model) so that it goes with the document. With the new security
measures, I don't think you can rely on being able to read the current data
source. But if you store the path with the document you can always read it
programmatically, and change it the same way whenever necessary.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :)
 
R

richardb

Cindy,

Thank you for all of your help. I learned a lot. Here's what I figured out
to do. In Access I'll start with

strConnectString = objWord.MailMerge.DataSource.ConnectString

Then I'll search this for the correct connection string. If not correct,
then I will send the "objWord.MailMerge.CreateDataSource, etc." that you
helped me to formulate. I think that should do it and again, thank you for
your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Display bug in Word: tables disappearing on copy and paste of text with bullet points into table 2
Make one final list from four sources 1
OLE link 0
Mail Merge Functionality 0
Synchronization problems while moving folders to new email 1
Merge from Access to Word - Loses Data Source 2
creation of a document based on mailmerge template 2
Mailmerge recepients list blank (pulling from Access query) 0

Top