Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word/Access 2007 Mail Merge Question
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 |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word/Access 2007 Mail Merge Question
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;Use r 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 "gant_g" wrote in message ... 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 |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Word/Access 2007 Mail Merge Question
Thanks, I will give it a shot.
"Peter Jamieson" wrote: 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;Use r 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 "gant_g" wrote in message ... 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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word and Access 2007 Mail Merge Problem | Mailmerge | |||
Can I mail merge from Access 2007 to Word 2003? | Mailmerge | |||
Question on Mail Merge from Access | Mailmerge | |||
Access to Word Mail Merge; symbols change to question marks | Mailmerge | |||
Word 2003 and Access 2003 Mail Merge question | Microsoft Word Help |