[justme0010]

I have set up Mail Merge to execute from within Microsoft Access using
the Word 9 object library like http://support.microsoft.com/kb/209976
shows.

My issue is that this opens up another instance of the database, which
I don't want and it makes the mail merging much slower. Is there a way
to execute mail merge from Access without opening another instance of
Access?




--
justme0010

[Peter Jamieson]

Word 2000, right?

See also

http://support.microsoft.com/kb/224697/en-us

If you want to use ODBC programmatically, assuming ODBC is installed on your
system, you would need code more like the following (starting from the code
in that KB article): However, be aware that ODBC will not "see" certain
types of query, e.g. parameter queries.

Function MergeIt()
Dim objWord As Word.Document
Set objWord = GetObject("C:\MyMerge.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source as the Northwind database.

objWord.MailMerge.OpenDataSource _
Name:="", _
Connection:="DSN=MS Access Database;" & _
"DBQ=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;", _
SQLStatement:="SELECT * FROM [Customers]"
' Execute the mail merge.
objWord.MailMerge.Execute
End Function

You will need to get the correct DSN name from the ODBC Administrator on
your system.

Precisely what else needs to go in the Connection parameter depends on your
set-up. For example, if you are using Workgroup security, you will need
something more like

Connection:="DSN=MS Access Database;" & _
"DBQ=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;" & _
"SystemDB=c:\mywgdb\Secured.mdw;UID=myloginname;PW D=mypassword;", _

Peter Jamieson
"justme0010" wrote in message
...

I have set up Mail Merge to execute from within Microsoft Access using
the Word 9 object library like http://support.microsoft.com/kb/209976
shows.

My issue is that this opens up another instance of the database, which
I don't want and it makes the mail merging much slower. Is there a way
to execute mail merge from Access without opening another instance of
Access?




--
justme0010

[justme0010[_2_]]

Thanks- that helped a lot. That problem has gone away.

Although now, I am getting random errors happening 30% of the time
(using the exact same queries) saying that the database is exclusively
locked or the recordset is empty when it shouldnt be. I am using a temp
table that I insert with data from a query using ADODB execution. Could
there be a delay in filling the temp table and trying to retrieve that
data for the mail merge??




--
justme0010

[Peter Jamieson]

Can you post your code here? (Are you for example closing all the relevant
ADO recordsets and connections after you have created the temp table? Are
you opening the Word document with its ODBC data source attached?). Not sure
when I wil be able to reply to this as I may be out of circulation for a
while though...

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

"justme0010" wrote in message
...

Thanks- that helped a lot. That problem has gone away.

Although now, I am getting random errors happening 30% of the time
(using the exact same queries) saying that the database is exclusively
locked or the recordset is empty when it shouldnt be. I am using a temp
table that I insert with data from a query using ADODB execution. Could
there be a delay in filling the temp table and trying to retrieve that
data for the mail merge??




--
justme0010

[justme0010[_3_]]

Well, here's the code if anyone sees a problem. Like I said before, this
works about 75-80% of the time.

Dim strSQL As String
Dim objWord As Word.Document
Dim ContainingFolder As String 'Full folder path that this database
resides in
Dim cnn As ADODB.Connection

ContainingFolder = CurrentProject.Path
Set cnn = CurrentProject.Connection

'Make sure there are no other records in temp table
cnn.Execute "DELETE * FROM EnvelopeTemp"

'SQLCriteria is a global variable
strSQL = "INSERT INTO EnvelopeTemp (BusinessName, Street, City, State,
Zip, Country) " & _
"SELECT BusinessName, Street, City, State, Zip, Country
" & _
"FROM [tblContactInfo] WHERE " & SQLCriteria

'Fill temp table with addresses
cnn.Execute strSQL

cnn.Close
Set cnn = Nothing

Set objWord = GetObject(ContainingFolder & "\HT System Blank
Template.doc", "Word.Document")

' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source as this database
objWord.MailMerge.OpenDataSource _
Name:="", _
ReadOnly:=True, _
Connection:="DSN=MS Access Database;" & _
"DBQ=" & ContainingFolder & "\HT System.mdb", _
SQLStatement:="SELECT * FROM [EnvelopeTemp] ORDER BY [Country] DESC,
[BusinessName] ASC"

'Execute the mail merge.
objWord.MailMerge.Execute

'Close main document, don't save changes
objWord.Close False




--
justme0010

[justme0010[_4_]]

I also notice that after a merge is executed and the main document is
automatically closed, when I open up the main document it has the
connection information in it, even though I specified the document to
not save during the close method.

Can I make sure that the connection information is not saved in the
main (template) document or is erased somehow? Could that help me with
this problem?




--
justme0010

[Peter Jamieson]

Try changing

cnn.Execute strSQL

to

cnn.Execute strSQL,,adCmdText+adExecuteNoRecords

I have a suspicion that what happens otherwise is that ADO DB creates a
recordset that has locking implications and is not necessarily cleaned up by
the cnn.Close or perhaps Set cnn = Nothing. (FWIW the Access VBA help says
that a cnn.Close has no effect on the actual connection in this case
anyway - at least I think that's what it says.

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

"justme0010" wrote in message
...

Well, here's the code if anyone sees a problem. Like I said before, this
works about 75-80% of the time.

Dim strSQL As String
Dim objWord As Word.Document
Dim ContainingFolder As String 'Full folder path that this database
resides in
Dim cnn As ADODB.Connection

ContainingFolder = CurrentProject.Path
Set cnn = CurrentProject.Connection

'Make sure there are no other records in temp table
cnn.Execute "DELETE * FROM EnvelopeTemp"

'SQLCriteria is a global variable
strSQL = "INSERT INTO EnvelopeTemp (BusinessName, Street, City, State,
Zip, Country) " & _
"SELECT BusinessName, Street, City, State, Zip, Country
" & _
"FROM [tblContactInfo] WHERE " & SQLCriteria

'Fill temp table with addresses
cnn.Execute strSQL

cnn.Close
Set cnn = Nothing

Set objWord = GetObject(ContainingFolder & "\HT System Blank
Template.doc", "Word.Document")

' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source as this database
objWord.MailMerge.OpenDataSource _
Name:="", _
ReadOnly:=True, _
Connection:="DSN=MS Access Database;" & _
"DBQ=" & ContainingFolder & "\HT System.mdb", _
SQLStatement:="SELECT * FROM [EnvelopeTemp] ORDER BY [Country] DESC,
[BusinessName] ASC"

'Execute the mail merge.
objWord.MailMerge.Execute

'Close main document, don't save changes
objWord.Close False




--
justme0010