I've been using this code to open mail merge docs in my database for a long time. I've recently made it into a hybrid sharepoint db. However when I try to run this code it opens the word template that I have created but then when it tries to access the datatable for the mailmerge on this line.
The error message that pops up in the word template (which loads successfully) is this
"Error has occurred. The databse has been placed in a state by user admin on machine "USER-TOSH" that prevents it from being opened or locked
If I click ok on the error I get an admin login screen which does not accept any credentials I have.
Back in Access I get the run time error 5922 and text saying Word was unable to open the data source.
The VBA code stops on the line below.
Code:
strConnect = "DSN=MS Access Database;DBQ=" & CurrentDb.Name & "FIL=MS Access;"
.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, _
ReadOnly:=True, LinkToSource:=True, _
Connection:=strConnect, _
SQLStatement:="SELECT * FROM [MMNPMailMergeQuery]"
Here is the full sub code.
Code:
Private Sub cmdNonPaymentMiniMarathon_Click()
Dim objApp As Object
Dim strConnect As String
'DoCmd.Close
'Open Mailmerge Document
'Start Word
Set objApp = CreateObject("Word.Application")
With objApp
.Visible = True 'Make it visible
.Documents.Open (CurrentProject.Path & "\Mini Marathon Mail Merge.doc") 'Open the Mailmerge Document
'Use the Query defined in the arguments as the datasource
strConnect = "DSN=MS Access Database;DBQ=" & CurrentDb.Name & "FIL=MS Access;"
.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, _
ReadOnly:=True, LinkToSource:=True, _
Connection:=strConnect, _
SQLStatement:="SELECT * FROM [MMNPMailMergeQuery]"
End With
'print and close Document
With objApp
.ActiveDocument.MailMerge.Execute Pause:=True
.ActiveDocument.PrintOut Background:=False
.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges 'Avoid Saving over your template
.Quit SaveChanges:=wdDoNotSaveChanges 'close all documents
End With
Set objApp = Nothing
ErrorHandler:
Select Case Err.Number
Case 4157
End Select
End Sub
I read online that if the vba code tries to change the db to design view during the process this error pops up. I don't see that here but if anyone has a better suggestion.
I can also open the word document, connect to the db and query and generate the mail merge. I don't want the user to have to do this though. Really frustrating. LOts of people seem to experience this issue and nobody seems to solve it.
Any help would be much appreciated. Thank you in advance.