Error 'An operation cannot be completed because of database engine errors'

[catflap]

Hi


Hopefully this is posted in the right area - it's related to Access, but the essential problem is with Word:


I'm trying to automate the following on a series of Word docs from Access vba:



1. Open the word doc
2. Change data source


3. Merge to a pdf


I've got some code for changing the data source of a doc (which I got from from ChatGPT) which works fine for most circumstances:

Sub UpdateMailMergeDataSource(docPath As String, dataSourcePath As String, sqlQuery As String)
    
    Dim wdApp As Object
    Dim wdDoc As Object
    
    ' Open Word application
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If wdApp Is Nothing Then
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    
    ' Open the Word document
    Set wdDoc = wdApp.Documents.Open(docPath)
    
    ' Update the data source
    With wdDoc.MailMerge
    .MainDocumentType = 0 ' wdFormLetters
    .OpenDataSource Name:=dataSourcePath, ConfirmConversions:=False, _
                    ReadOnly:=True, _
                    LinkToSource:=True, _
                    AddToRecentFiles:=False, _
                    PasswordDocument:="", _
                    PasswordTemplate:="", _
                    WritePasswordDocument:="", _
                    WritePasswordTemplate:="", _
                    Revert:=False, _
                    format:=0, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & dataSourcePath & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
                    SQLStatement:=sqlQuery
    End With
    
    ' Save the document
    wdDoc.Save
    
    ' Close the document without prompting to save changes
    wdDoc.Close SaveChanges:=False
    
    ' Quit Word application if no other documents are open
    If wdApp.Documents.Count = 0 Then
        wdApp.Quit SaveChanges:=False
    End If
    
    ' Release objects
    Set wdDoc = Nothing
    Set wdApp = Nothing
End Sub

..however, it falls over when the doc has been linked to an excel file which has then been deleted (or has name changed). On oipening I get the error 'An operation cannot be completed because of database engine errors', which stops the code from carrying on.



The usual routine when when opening the doc manually would be to cancel the error, remove the merge source, and re-attach the new one.



So I'm looking for a way to ignore or cancel the errors which come up when you get an issue with datasource. After that the existing code should work.


Could anyone suggest a way to do this please?


Thanks!

[Guessed]

Can you try this...
Application.DisplayAlerts = False

For example

Private Sub MacroName()
  Application.DisplayAlerts = False
  'Function causing the alert goes here (eg opening the document)
  Application.DisplayAlerts = True   'restore normal behaviour
End Sub

[catflap]

Thanks for reply.


I'm now using:

Sub test_update(docPath As String, dataSourcePath As String, sqlQuery As String)
    Dim wdApp As Object
    Dim wdDoc As Object

    ' Open Word application
    On Error Resume Next
    
   
    Set wdApp = GetObject(, "Word.Application")
    
    wdApp.DisplayAlerts = False
    
    If wdApp Is Nothing Then
        Set wdApp = CreateObject("Word.Application")
    End If
    
    
    On Error GoTo 0
    
    ' Check if Word application is open
    If wdApp Is Nothing Then
        MsgBox "Unable to open or create Word application.", vbExclamation
        Exit Sub
    End If

    ' Open the Word document
    On Error Resume Next
    'Set wdDoc = wdApp.Documents.Open(docPath)
    
    wdApp.DisplayAlerts = False
    
    Set wdDoc = wdApp.Documents.Open(docPath, False, True, False, , , , , , , , True) 'set object as Word document used as base
    
    ' Ignore specific merge file errors
    If Err.Number <> 0 Then
        Err.Clear
    End If
    On Error GoTo 0
    
    If wdDoc Is Nothing Then
        MsgBox "Unable to open the Word document: " & docPath, vbExclamation
        Exit Sub
    End If
    
    wdApp.DisplayAlerts = True

    ' Update the data source
    On Error GoTo HandleError
    With wdDoc.MailMerge
        .MainDocumentType = 0 ' wdFormLetters
        .OpenDataSource Name:=dataSourcePath, _
                        ConfirmConversions:=False, _
                        ReadOnly:=True, _
                        LinkToSource:=True, _
                        AddToRecentFiles:=False, _
                        PasswordDocument:="", _
                        PasswordTemplate:="", _
                        WritePasswordDocument:="", _
                        WritePasswordTemplate:="", _
                        Revert:=False, _
                        format:=0, _
                        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & dataSourcePath & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
                        SQLStatement:=sqlQuery
    End With
    
    ' Check for errors during the data source update
    If Err.Number <> 0 Then
        ' Handle known database engine error by ignoring it
        Debug.Print "Error: " & Err.Number
        If Err.Number = 112 Then
            Err.Clear
        Else
            MsgBox "Error updating the data source: " & Err.Description, vbExclamation
            wdDoc.Close SaveChanges:=False
            Set wdDoc = Nothing
            Set wdApp = Nothing
            Exit Sub
        End If
    End If

    ' Save the document
    wdDoc.Save

    ' Close the document without prompting to save changes
    wdDoc.Close SaveChanges:=False

    ' Quit Word application if no other documents are open
    If wdApp.Documents.Count = 0 Then
        wdApp.Quit SaveChanges:=False
    End If

    ' Release objects
    Set wdDoc = Nothing
    Set wdApp = Nothing

    MsgBox "Mail merge data source updated and document saved.", vbInformation
    Exit Sub

HandleError:
    ' Handle errors
    If Err.Number <> 0 Then
        ' Ignore known errors related to database engine
        Debug.Print "Error: " & Err.Number
        If Err.Number = 123 Then
            Resume Next
        Else
            MsgBox "An unexpected error occurred: " & Err.Description, vbExclamation
            On Error Resume Next
            If Not wdDoc Is Nothing Then wdDoc.Close SaveChanges:=False
            If wdApp.Documents.Count = 0 Then wdApp.Quit SaveChanges:=False
            Set wdDoc = Nothing
            Set wdApp = Nothing
        End If
    End If
End Sub

As you can see I've added 'wdApp.DisplayAlerts = False' just before the line that kicks off the error messages, but it still displays them.


If it helps, below is the full sequence I have to click through to clear the errors and then be able to assign a data source (I've removed name of doc and data):

[Guessed]

I've streamlined the logic of your code so it is cleaner but I'm not sure it will solve your issue and I don't have a file to do any testing on. Give this a try and if you cross your fingers you might find it works

Sub test_update(docPath As String, dataSourcePath As String, sqlQuery As String)
    Dim wdApp As Object, wdDoc As Object

    ' Open Word application
    On Error Resume Next
      Set wdApp = GetObject(, "Word.Application")
      If wdApp Is Nothing Then
        Set wdApp = CreateObject("Word.Application")
      End If
    On Error GoTo 0
    If wdApp Is Nothing Then    ' Check if Word application is open
      MsgBox "Unable to open or create Word application.", vbExclamation
      Exit Sub
    End If

    ' Open the Word document
    On Error Resume Next
      wdApp.DisplayAlerts = False
        Set wdDoc = wdApp.Documents.Open(docPath, False, True, False, , , , , , , , True) 'set object as Word document used as base
      wdApp.DisplayAlerts = True
      ' Ignore specific merge file errors
      If Err.Number <> 0 Then Err.Clear
    On Error GoTo 0
    If wdDoc Is Nothing Then
      MsgBox "Unable to open the Word document: " & docPath, vbExclamation
      Exit Sub
    End If

    ' Update the data source
    On Error GoTo HandleError       'This will send the macro down to HandleError before returning
    With wdDoc.MailMerge
        .MainDocumentType = 0 ' wdFormLetters
        .OpenDataSource Name:=dataSourcePath, _
                        ConfirmConversions:=False, ReadOnly:=True, _
                        LinkToSource:=True, AddToRecentFiles:=False, _
                        PasswordDocument:="", PasswordTemplate:="", _
                        WritePasswordDocument:="", WritePasswordTemplate:="", _
                        Revert:=False, Format:=0, _
                        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & dataSourcePath & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
                        SQLStatement:=sqlQuery
    End With

    ' Save and Close the document
    wdDoc.Close SaveChanges:=True

    ' Quit Word application if no other documents are open
    If wdApp.Documents.Count = 0 Then wdApp.Quit SaveChanges:=False

    ' Release objects
    Set wdDoc = Nothing
    Set wdApp = Nothing

    MsgBox "Mail merge data source updated and document saved.", vbInformation
    Exit Sub

HandleError:
    ' Handle errors
    If Err.Number <> 0 Then
        ' Ignore known errors related to database engine
        Debug.Print "Error: " & Err.Number
        If Err.Number = 123 Or Err.Number = 112 Then
          Err.Clear
          Resume Next
        Else
            MsgBox "An unexpected error occurred: " & Err.Number & vbCr & Err.Description, vbExclamation
            If Not wdDoc Is Nothing Then wdDoc.Close SaveChanges:=False
            If wdApp.Documents.Count = 0 Then wdApp.Quit SaveChanges:=False
            Set wdDoc = Nothing
            Set wdApp = Nothing
        End If
    End If
End Sub

[catflap]

Thanks for taking the time to do this - unfortunately it's still giving me the error messages and stopping the code.


I'm beginning to think this may not be possible - in theory one way around it would be to create a dummy data file under the name/path of the currently assigned data source before opening the document - the doc would then be able to find it when opened and not give any errors, and then I could then assign the datasource I really wanted.



The problem with that is that I don't think there's a way of getting the path to the current data source without opening the doc first, and then we're back to all the errors messages!

[Guessed]

Can you explain why you are choosing to create this temp doc from a document/template that has already been created (ie one you prepared earlier). If this is the case, why would you set it up with this problem lying in wait for you?

You could instead make sure that template has no linked data source before opening it with your macro which is going to attach the datasource anyway.

[catflap]

Hi


The back story:


It's all about streamlining things. I've got 18 docs in separate folders, and my client will send over data to merge some or all of the docs every week. I've tried merging the 18 docs into one to make things easier, but there are some complications with this, so I'm using them separately.



I've already got some code set up to copy the new xlsx data files to replace the old files each time, and as the names will be the same, the merge (to a pdf file) works nicely when I call each doc from an Access function.


So far so good - the snag comes when the client wants the docs back to edit. They've started doing this every month or so lately. They previously used Indesign files but now want to switch to word docs.


I'm anticipating they will change the merge file to a different location as part of their editing process so they can see how the data merges, so when they send them back to me I will get the error I mentioned when the doc can't find the data file they set up as merge source.


If I can't find a solution to the error messages, I'll just have to open all the docs one at a time and reset the merge source manually, I just thought it would be nice to find some way to automate this and save a lot of clicking!

[catflap]

I think I'll just have to give up on this one - looks like the windows that pop-up related to problems with mail merge just can't be controlled by automation.


Thanks Guessed for trying to help!