Word/Access 2007 Mail Merge Question

[gant_g]

I have a couple issues with a mail merge that I need to get resolved. I have
a Access 2007 database that uses shared-level password. I have created a
dialog form that allows the user to enter a application number and select the
letter they wish to print from a drip down box. The problem is that when the
word document opens I get "Error has occured: Not a valid password" then the
Data Link Properties box opens and the box is checked to "Use a Blank
Password". If I uncheck the box and enter the database password I get another
box that asks me for the password again. Is there something that I can set so
that the "Use a Blank Password" box is no longer checked in the first dialog
box? The code I am using to perform the mail merge is below. In case you are
wondering it works fine if I remove the password from the database but that
is not an option because the database resides on a shared network location.

Thanks in advance for any help you can provide.


'Create the Word instance and make it visible
Set objWord = GetObject(strFilePath, "Word.Document")
objWord.Application.Visible = True

'Open the data set from this database
objWord.MailMerge.OpenDataSource _
Name:=Application.CurrentProject.FullName, _
LinkToSource:=True, _
PasswordDocument:="'" & strPassword & "'", _
PasswordTemplate:="'" & strPassword & "'", _
WritePasswordDocument:="'" & strPassword & "'", _
WritePasswordTemplate:="'" & strPassword & "'", _
Connection:="QUERY Q_Letters", _
SQLStatement:="SELECT * FROM [Q_Letters] where [PK$ApplicationID] =
'" _
& appNumber & "'", _
OpenExclusive:=False

 

[Peter Jamieson]

The passwords in here

PasswordDocument:="'" & strPassword & "'", _
PasswordTemplate:="'" & strPassword & "'", _
WritePasswordDocument:="'" & strPassword & "'", _
WritePasswordTemplate:="'" & strPassword & "'", _

only have any effect if the data source is a Word document. For an Access
data source, how to deal with the password depends on how you need to
connect.

By default, Word 2007 should connect using OLE DB. Assuming that by
"shared-level password" you mean that there is a single database password
for all users, not a workgroup information file, and that password is in
strPassword, then your connection code needs to be more like:

objWord.MailMerge.OpenDataSource _
Name:=Application.CurrentProject.FullName, _
Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source="
_
& Application.CurrentProject.FullName _
& ";Mode=Read;Jet OLEDB:Engine Type=5;Jet OLEDBatabase
Locking Mode=0;Jet OLEDBatabase Password=" _
& strPassword & ";", _
SQLStatement:="SELECT * FROM [Q_Letters] where [PK$ApplicationID] = '" _
& appNumber & "'", _
SubType:=wdMergeSubTypeAccess, _
OpenExclusive:=False

(I've never worked out whether OpenExclusive actually does the thing its
name suggests it does, but it's probably not doing any harm either).

You may need to tweak that connection string and you may well be able to
eliminate a lot of it if you want. You probably do not need the Subtype
parameter.

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

I have a couple issues with a mail merge that I need to get resolved. I
have
a Access 2007 database that uses shared-level password. I have created a
dialog form that allows the user to enter a application number and select
the
letter they wish to print from a drip down box. The problem is that when
the
word document opens I get "Error has occured: Not a valid password" then
the
Data Link Properties box opens and the box is checked to "Use a Blank
Password". If I uncheck the box and enter the database password I get
another
box that asks me for the password again. Is there something that I can set
so
that the "Use a Blank Password" box is no longer checked in the first
dialog
box? The code I am using to perform the mail merge is below. In case you
are
wondering it works fine if I remove the password from the database but
that
is not an option because the database resides on a shared network
location.

Thanks in advance for any help you can provide.


'Create the Word instance and make it visible
Set objWord = GetObject(strFilePath, "Word.Document")
objWord.Application.Visible = True

'Open the data set from this database
objWord.MailMerge.OpenDataSource _
Name:=Application.CurrentProject.FullName, _
LinkToSource:=True, _
PasswordDocument:="'" & strPassword & "'", _
PasswordTemplate:="'" & strPassword & "'", _
WritePasswordDocument:="'" & strPassword & "'", _
WritePasswordTemplate:="'" & strPassword & "'", _
Connection:="QUERY Q_Letters", _
SQLStatement:="SELECT * FROM [Q_Letters] where [PK$ApplicationID] =
'" _
& appNumber & "'", _
OpenExclusive:=False

 

[gant_g]

Thanks, I will give it a shot.

The passwords in here

PasswordDocument:="'" & strPassword & "'", _
PasswordTemplate:="'" & strPassword & "'", _
WritePasswordDocument:="'" & strPassword & "'", _
WritePasswordTemplate:="'" & strPassword & "'", _

only have any effect if the data source is a Word document. For an Access
data source, how to deal with the password depends on how you need to
connect.

By default, Word 2007 should connect using OLE DB. Assuming that by
"shared-level password" you mean that there is a single database password
for all users, not a workgroup information file, and that password is in
strPassword, then your connection code needs to be more like:

objWord.MailMerge.OpenDataSource _
Name:=Application.CurrentProject.FullName, _
Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source="
_
& Application.CurrentProject.FullName _
& ";Mode=Read;Jet OLEDB:Engine Type=5;Jet OLEDBatabase
Locking Mode=0;Jet OLEDBatabase Password=" _
& strPassword & ";", _
SQLStatement:="SELECT * FROM [Q_Letters] where [PK$ApplicationID] = '" _
& appNumber & "'", _
SubType:=wdMergeSubTypeAccess, _
OpenExclusive:=False

(I've never worked out whether OpenExclusive actually does the thing its
name suggests it does, but it's probably not doing any harm either).

You may need to tweak that connection string and you may well be able to
eliminate a lot of it if you want. You probably do not need the Subtype
parameter.

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

I have a couple issues with a mail merge that I need to get resolved. I
have
a Access 2007 database that uses shared-level password. I have created a
dialog form that allows the user to enter a application number and select
the
letter they wish to print from a drip down box. The problem is that when
the
word document opens I get "Error has occured: Not a valid password" then
the
Data Link Properties box opens and the box is checked to "Use a Blank
Password". If I uncheck the box and enter the database password I get
another
box that asks me for the password again. Is there something that I can set
so
that the "Use a Blank Password" box is no longer checked in the first
dialog
box? The code I am using to perform the mail merge is below. In case you
are
wondering it works fine if I remove the password from the database but
that
is not an option because the database resides on a shared network
location.

Thanks in advance for any help you can provide.


'Create the Word instance and make it visible
Set objWord = GetObject(strFilePath, "Word.Document")
objWord.Application.Visible = True

'Open the data set from this database
objWord.MailMerge.OpenDataSource _
Name:=Application.CurrentProject.FullName, _
LinkToSource:=True, _
PasswordDocument:="'" & strPassword & "'", _
PasswordTemplate:="'" & strPassword & "'", _
WritePasswordDocument:="'" & strPassword & "'", _
WritePasswordTemplate:="'" & strPassword & "'", _
Connection:="QUERY Q_Letters", _
SQLStatement:="SELECT * FROM [Q_Letters] where [PK$ApplicationID] =
'" _
& appNumber & "'", _
OpenExclusive:=False