If the database has a password "thepassword", you have to specify it in
the connection string, like this (I think). I've assumed username
"theusername"
objApp.ActiveDocument.MailMerge.OpenDataSource _
Name:= strCurrentFileName, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password=""" ";" & _
"User ID=theusername;Data Source=strCurrentFileName;" & _
"Mode=Read;Jet OLEDB
atabase Password=""thepassword"";", _
SQLStatement:="SELECT * FROM `mergetable`", _
SubType:=wdMergeSubTypeAccess
If the database has a workgroup security file, then you have to specify
the user name "theusername", password, and the location of that file
"wgpathname", e.g.
objApp.ActiveDocument.MailMerge.OpenDataSource _
Name:=strCurrentFileName, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password=""" ";" & _
"User ID=theusername;Data Source=strCurrentFileName;" & _
"Mode=Read;Jet OLEDB:System database=""wgpathname"";", _
SQLStatement:="SELECT * FROM `mergetable`", _
SubType:=wdMergeSubTypeAccess
Notes:
a. this is off the top of my head.
b. I don't think you need any of the the other parameters for
OpenDataSource
c. I think at the moment you have "Date Source" when you should have
"Data Source". But maybe you do not need either
d. if you have pathnames for the Data Source and System database that
push the length of the Connection parameter over 255 characters, you may
have a problem
e. it's possible that in order to make this work, you'll have to put
the connection info. into a .odc file rather than supply it inline
f. bear in mind that doing this embeds securty info. in the Word file.
Unfortunately, even if you prompt for the password info. at runtime, the
connection string will probably still end up embedded in the word doc.,
at least if the user saves the file
Peter Jamieson
http://tips.pjmsn.me.uk
Murray Muspratt-Rouse wrote:
I already have 1 .mdb working with mailmerge. With a 2nd one Word is
asking me to sign in authorise the connection to the .mdb. Here is the
Access VBA code, which some may find familiar - Doug Robbins and Peter
Jamieson helped me to get it working some months ago!
Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim strCurrentFileName As String
On Error Resume Next
DoCmd.OpenQuery "Delete all rows from MergeTable"
'Load data to MergeTable with a query that collects the required data
after update
If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateVolunteerRefereeLetterDate")
DoCmd.OpenQuery ("qryAppendVolunteerRefereedata")
Else
If strLetterDescription = "Volunteer Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateVolunteerRefereeChaserDate")
DoCmd.OpenQuery ("qryVolunteerRefereechaser")
Else
If strLetterDescription = "TRAINING DATES" Then
DoCmd.RunMacro ("Set up training dates data for
merge")
Else
DoCmd.OpenQuery ("Volunteer letter data")
End If
End If
End If
End If
If strFormName = "Clients" Then
If strLetterDescription = "REFERENCE CLIENT" Then
DoCmd.OpenQuery ("qryUpdateClientRefereeLetterDate")
DoCmd.OpenQuery ("qryAppendClientRefereedata")
Else
If strLetterDescription = "Client Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateClientRefereeChaserDate")
DoCmd.OpenQuery ("qryClientRefereechaser")
Else
DoCmd.OpenQuery ("qryClient")
End If
End If
End If
If strFormName = "Befrienders" Then
DoCmd.OpenQuery ("qryBefriender")
End If
strCurrentFileName = CurrentDb.Name
Set objApp = CreateObject("Word.Application")
objApp.Visible = False
'objApp.Activate
'Set objMMMD = objApp.Documents.Open(FileName:=strDocName)
objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", XMLTransform:=""
objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
With objApp.ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)
objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
'objMMMD.Close SaveChanges:=False
'Set objMMMD = Nothing
'objApp.Documents.Open strDocName
objApp.Visible = True
objApp.Activate
End Sub
I have a nasty feeling that the Admin password has been changed from
blank - but this part of the code is still working in the other .mdb.
Word was asking for the ODBC password. Can I change the code to use my
account name and password? Please ask for any more information you
require.
Murray