I am having the strangest results with my automated mailmerge. Basically it does work, but not all the time. The basic idea is to allow the user to dynamically create a query that produces a result list which fills a temporary table. The use then selects a prebuilt merge template and merge is executed against the temp table. The merge template are of the .doc type, but sme have been converted to .docx; the .doc files tend to work most often, but all of the will eventually get a Table is locked message... However if I run in test mode with code breaks and manually step through the process it always works...
here is the heart of the code ...
Err_Pos = 10
There are many error cases in the error catch routine. That I have managed to make Access stop hanging when word has a problem or the table is locked. But I can't get the table to be free consistantly and why does it always work when I manually step through the code. :banghead:
here is the heart of the code ...
Err_Pos = 10
Code:
DoCmd.SetWarnings False
' if tmp tbl left over from last run kill it
DoCmd.RunSQL "Drop table Word_Merge_Tmp_TBL"
Err_Pos = 12
' this create a temp table named "Word_Merge_Tmp_TBL"
CurrentDb.Execute Create_Tmp_Word_Mrg_QryStr
DoCmd.SetWarnings True
cont = False
Err_Pos = 15
For Each Tdef In CurrentDb.TableDefs
If StrComp(Tdef.Name, "Word_Merge_Tmp_TBL", vbTextCompare) = 0 Then
cont = True
Exit For
End If
Next Tdef
If cont Then
Err_Pos = 20
oApp.Quit False
Set oApp = Nothing
Err_Pos = 25
Set oApp = CreateObject("Word.Application")
Set oMainDoc = oApp.Documents.Open(Mrg_Tmplt_Str, True, False, False, , , True, , , , , , True)
Err_Pos = 30
'Set up the mail merge data source to Current DB.
sDBPath = CurrentProject.FullName
Pause_Ops (5000)
With oMainDoc.MailMerge
Err_Pos = 40
.OpenDataSource Name:=sDBPath, _
Connection:="DSN=MS Access Database;DBQ=" & CurrentDb.Name & "; FIL=MS Access;", _
LinkToSource:=True, AddToRecentFiles:=False, ReadOnly:=True, _
SQLStatement:="SELECT * FROM [Word_Merge_Tmp_TBL]"
'Perform the mail merge to a new document.
.Destination = wdSendToNewDocument
Err_Pos = 50
.Execute Pause:=False
End With
DoCmd.SetWarnings False
DoCmd.RunSQL "Drop table Word_Merge_Tmp_TBL"
DoCmd.SetWarnings True
Else
If MsgBox("Access Table error!" & vbNewLine & "Cannot find Query results temp table." & vbNewLine & _
"Do you want to try again?", vbYesNo + vbQuestion, App_Q_MsgBx_Ttl) = vbYes Then
GoTo Re_Try
Else
GoTo Close_Template_On_Error
End If
End If
There are many error cases in the error catch routine. That I have managed to make Access stop hanging when word has a problem or the table is locked. But I can't get the table to be free consistantly and why does it always work when I manually step through the code. :banghead: