password problems in a mail merge

[Ultraviolet47]

Hi

I have an Access 03 db with a password. I have posed on here before
that when I start the merge which is based on a query, it asks 3 times
for a password,then mail merges, even if I press cancel. Some people
kindly posted back, but suggestions didn't work.

If I click "test connction" it says it cannot start the application as
the work group file is missing or in use by another application?
Access is already open and the code opens Word?

I have added some code found of MS Support to enter the password in
the merge, but it still asks for the passwords and gives that error.
Does anyone know why it still mail merges when cancel is clicked 3
times?! Can anyone spot anything wrong with the code?

I'd really appreciate you help guys!

Dim MyConn As New ADODB.Connection

MyConn.Provider = "Microsoft.Jet.OLEDB.4.0"
MyConn.Properties("Data Source") = "C:\Documents and Settings\Stel
\My Documents\Cavy\Sponsorship pack\Sponsors\CavyRescue.mdb"
MyConn.Properties("Jet OLEDBatabase Password") = "mypassword"
MyConn.Open

Dim objWord As Word.Document
Set objWord = GetObject("C:\Documents and Settings\Stel\My Documents
\Cavy\Sponsorship pack\Sponsors\welcome letter.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the Rat Rescue database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\Stel\My Documents\Cavy
\Sponsorship pack\Sponsors\CavyRescue.mdb", _
LinkToSource:=True, _
Connection:="QUERY Qry_WelcomeLetter", _
SQLStatement:="SELECT * FROM [Qry_WelcomeLetter]"
' Execute the mail merge.
objWord.MailMerge.Execute


End Sub

 

[GeoffG]

If I click "test connction" it says it cannot start the application as

the work group file is missing or in use by another application?
Access is already open and the code opens Word?

I don't know about Access 2003. My information is for Access 97, 2000 and
2002.

When you use VBA within Access to execute a Word mailmerge, a second copy of
Access will start (assuming the datasource is an Access database - even if
that database is already open and on screen). In your case, it sounds like
that second copy of Access is having trouble locating the appropriate system
database (aka Workgroup file) and pasword.

With the above previous versions of Access, the problem with Word mailmerges
has been that the second copy of Access doesn't close when the mailmerge
finishes and there appears to be no simple way to get it to close.
Therefore, one standard solution to this problem has been to use VBA's
DoCmd.OutputTo method to export a query's data to to a RTF (rich-text
format) file and then execute the mailmerge between a Word template and the
RTF file (which becomes the datasource). It would seem that such an
approach might also overcome the problems you're experiencing with missing
Workgroup file and password.

Incidentally, a fairly complex query that worked perfectly for me in Access
2000 is problematic in Access 2002. The query called a number of
user-defined functions aimed at reformating the data for the RTF datasource
file and for the mailmerge. There appears to be some sort of timing issue
with Access 2002 - it frequently stumbles when it hits the OutputTo method
and sends a message to Microsoft. However, when the user-defined functions
are removed from the query, everything works OK. It appears Access 2002
cannot process the user-defined functions in time for the OutputTo method.

You might want to consider using a Word template, not a Word document for
your mailmerge. As your mailmerge appears to be a letter, you'd be safer to
use Word's CreateDate() function in the template to insert the date into the
letter. The date won't then change if you save the resulting merged
document and open it on a later date.

I'm afraid I don't know the answers to your other questions.

The above RTF methodology is explained in the "Access Cookbook" by Getz,
Litwin and Baron.

Good luck with your project.
Geoff