[Murray Muspratt-Rouse]

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