Error when I mail merge

[aziz rasul]

I have the following code which errors on line

.OpenDataSource Name:=CurrentProject.FullName, SQLStatement:="SELECT * FROM [qryMailMerge]"

The error (Error has occurred: The database has been placed in a state by user 'Admin' on machine 'W74XXXXXX' that prevents it from being opened or locked) appears in the Word document.

Public Sub CreateWordToPDF(strWordFile As String, strPDFFile As String, strSQL As String)
'Call CreateWordToPDF("C:\Doc1.doc", "C:\Doc1.pdf", "SELECT * FROM [qryMailMerge]")

    Dim objWord As Word.Application
    Dim docWord As Word.Document
       
    'Open MS Word using early binding.
    Set objWord = New Word.Application
    Set docWord = objWord.Documents.Open(strWordFile)
       
    'Opening the MS Word template file.
    With objWord
        .Visible = True
        .Activate
        .Documents.Open (strWordFile)
    End With
            
    With objWord.ActiveDocument.MailMerge
        .OpenDataSource Name:=CurrentProject.FullName, SQLStatement:="SELECT * FROM [qryMailMerge]"
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
       
    'Export as PDF.
    docWord.ExportAsFixedFormat OutputFileName:=strPDFFile, ExportFormat:=wdExportFormatPDF
   
    objWord.Quit
    Set objWord = Nothing
        
End Sub

Any ideas anyone how I resolve this?

 

[KenHigg]

I assume it has worked in the past so several ideas. The first is obvious, make sure all users are out of the db. You may even have to reboot the machines that access it. Turn everthing back on and see if it works. If that does not work compact and restore the db. Try those if they don't work post back what all you've tried...

 

[pr2-eugin]

There should be an argument "OpenExclusive", try setting it to False. I think the default is True.

 

[aziz rasul]

Compacting and Repair resolved the issue. I should have tried that.

I have a separate problem in that the 2 documents that are opened i.e. the template and the new Word file, I get prompted to save the files. Here's the new code:-

Public Sub CreateWordToPDF(strWordFile As String, strPDFFile As String, strSQL As String)
'Call CreateWordToPDF("C:\Doc1.doc", "C:\Doc1.pdf", "SELECT * FROM [qryMailMerge]")

    Dim objWord As Word.Application
    Dim docWord As Word.Document
       
    'Open MS Word using early binding.
    Set objWord = New Word.Application
    Set docWord = objWord.Documents.Open(strWordFile)
       
    'Opening the MS Word template file.
    With objWord
        .Visible = True
        .Activate
        .Documents.Open (strWordFile)
    End With
            
    With objWord.ActiveDocument.MailMerge
        .OpenDataSource Name:=CurrentProject.FullName, SQLStatement:=strSQL
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
       
    'Export as PDF.
    docWord.ExportAsFixedFormat OutputFileName:=strPDFFile, ExportFormat:=wdExportFormatPDF
   
    With objWord
        .DisplayAlerts = wdAlertsNone
        .Quit
        .DisplayAlerts = wdAlertsAll
    End With
    
    Set objWord = Nothing
        
End Sub

I still get prompted even though I'm switching the DisplayAlerts off.

 

[KenHigg]

Don't you simply need to use a word object command to save before you close?

 

[aziz rasul]

There is no need to save either Word doc as the pdf file that is created and is what I'm aiming for is based on line

 docWord.ExportAsFixedFormat OutputFileName:=strPDFFile, ExportFormat:=wdExportFormatPDF

 

[pr2-eugin]

Then just drop all changes. The Quit takes in that argument.

.Quit SaveChanges:=wdDoNotSaveChanges

 

[KenHigg]

Then just drop all changes. The Quit takes in that argument.

.Quit SaveChanges:=wdDoNotSaveChanges

I was thinking surely there was an option for this -

 

[aziz rasul]

Guys, how would I amend the code to create a pdf for more than record?

I can use a recordset in the calling routine, but is there a way of doing it in the mail merge itself?