Microsoft Office Forums - View Single Post - Error 'An operation cannot be completed because of database engine errors'

catflap · 08-05-2024, 02:36 AM

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:

Code:

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!