Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Connection problem with Access
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word could not re-establish a DDE Connection to Microsoft Access | Mailmerge | |||
DDE connection w/ Access & Word 2007 | Mailmerge | |||
Word could not reestalish a DDE connection to ACCESS DB | Mailmerge | |||
mailmerge from access database using DDE connection? | Mailmerge | |||
Data Source Connection Problem | Mailmerge |