Using a query as a data source for a mail merge

[Ultraviolet47]

Hello everyone.

I have used a table as a datasource to mail merge in Word 2003 from
Access 2003. Now I need to change this to a query. I have altered the
code that references the table and changed it to the query name
(Qry_WelcomeLetter), but it says Word cannot find the data source.

Another curious thing that happens is when I execute the merge via this
code, before it debugs to say it can't find the database, it asks me to
login as a user. I have gotten this message previously, but thought
that was because I had not set Word to become visible. I then added the
code to make Word visible and after a few weeks of peace it's asking me
for a user name and password for Admin again?

ANy ideas to stop the username request and how to get it to recognise
the data souce?

Here is my code:

Private Sub Print_Welcome_Letter_Click()
Dim objWord As Object
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 WHERE SponsorID =
(SELECT Max(SponsorID) FROM Qry_WelcomeLetter)"

objWord.MailMerge.Destination = SendToNewDocument
objWord.MailMerge.Execute

'The following line must follow the Execute statement because the
'PrintBackground property is available only when a document window is
'active. Without this line of code, the function will end before Word
'can print the merged document.

objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.PrintOut

End Sub

 

[Peter Jamieson]

What is the SQL code for the query?

code, before it debugs to say it can't find the database, it asks me to
login as a user.

When you open your database using Access, are you prompted for a (database)
password? Or are you promted for a username and password (workgroup
security)? Or do you start your database using a command line or icon that
specifies either of those two things?

BTW, are you the Cavey Rescue guys in Kent I visited with Brigid & Ralph
last summer? If so, feel free to despam my e-mail and e-mail directly.

Peter Jamieson

 

[Ultraviolet47]

Hi

I'm making this db for Cavy Rescue (and my rat Becky!) It's not been
fully implemented, just ironing out testing bugs.

There is a database password, but there is no workgroup security set.

I am asked to run the SQL command, so select Yes when prompted.
It then pops up a "Data link proprties tab" with the User Name Admin in
and the file path of the db, I deselect "blank password" and enter the
database password.

It then comes up with a new Authorize prompt, asking for Admin password
again, so enter db password again.

It then mail merges and prints.

Is there any way to avoid having to enter password (twice!), without
actually getting rid of the password itself?

Thanks for your help.

 

[Peter Jamieson]

OK,

1. In Word 2002/2003 you will get the data link dialog because Word wants to
use OLEDB to open an Access data source. To fix that, try

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 WHERE SponsorID = (SELECT
Max(SponsorID) FROM Qry_WelcomeLetter)", _
Subtype:=wdMergeSubtypeWord2000

2. However, I don't know of any way to specify the database password in the
OpenDataSource call (the PasswordDocument parameter only works for protected
Word documents and perhaps Excel documents as far as I know). The user
should only have to provide the database password if Access hasn't already
got the database open - in that case, Access should prompt in the usual way.
In other words, if you are initiating the merge from code in the Access
database, you should be OK.

3. Otherwise you may be able to specify the password in the Connection
string by connecting via OLEDB instead - but let me know if you need to try
that as I have to dig out my notes.

Peter Jamieson

 

[Ultraviolet47]

Thanks for that Peter.

Howver, I keep getting an "object required" error? I have listed all
the references I thought I needed in the object library-do you know
what the problem is?

Thanks for your help

 

[Peter Jamieson]

I hadn't looked at all of your code. I suggest

(1) you replace the following:

with something more like

Dim objWord as Word.Application
Dim objDocCheck as Word.Document
Dim objDoc as Word.Document
Dim bOpened as Boolean
Const strFilePath as string = "C:\Documents and Settings\Stel\My
Documents\Cavy\Sponsorship pack\Sponsors\welcome letter.doc"

' Get or create an instance of Word as necessary
On Error Resume Next
Set objApp = GetObject("Word.Application")
On Error Goto 0
bOpened = False
If objWord Is Nothing Then
Set objWord = CreateObject("Word.Application")
bOpened = True
End If

' See if the required document is already open...
For Each objDocCheck in objWord.Documents
If objDocCheck.FullName = strFilePath then
Set objDoc = objDocCheck
Exit For
End If
Next

' ...or open it
If objDoc Is Nothing Then
Set objDoc = objWord.Documents.Open(strFilePath)
End If

then modify the object variable names in your existing code (or modify
mine - they are the ones I usually use) and if necessary, use the bOpened
variable to determine whether or not you need to close the document at the
end of the process.

In other words, I don'tt think it's to do with a missing reference. But you
do need to include Word as a reference to make that lot work as expected.

2. You may have to take account of the following article:

"Opening This Will Run the Following SQL Command" Message When You Open a
Word Document"
at
http://support.microsoft.com?kbid=825765

Peter Jamieson