Error when creating mail merge doc or any word doc

[maxmaggot]

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.

       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.

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.

[Dal Jeanis]

Three points about this -

1) Access can get very picky. If any open form has been modified, even by resizing, it can generate that kind of a silly lock.

TEST - Do a quick compact and repair, then try running your mailmerge code immediately, without doing anything else. If it works, then
forms changing can be part of your problem.

2) MailMerge.OpenDataSource uses an ODBC connection string. Your string looks odd to me, but it seems to conform to the specs at http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

I would have expected something more like

strConnect = "Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" & CurrentDb.Name & ";"

or

Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq="& CurrentDb.Name &";

3) Hmmm. I notice you have both the NAME parameter of OpenDataSource, and the DBQ parameter in the connection string. I'm not sure what the effect of that is ... you could be attempting to attach to the same database twice. Try removing one of them and see if that fixes your problem.

4) Memo to self - never say how many points you are going to write...
I found this note here - http://answers.microsoft.com/en-us/o...1-490544623a92

In theory you shouldn't need the [], but you do, because of an error in Word. You may need to leave the ";" off too (not sure), i.e.

.OpenDataSource Name:="G:\Database\Tcc_KidCare\KidCare_2010_FE.acc de", sqlstatement:="[SELECT * FROM tblWordMerge]"

That was all the code needed for this type of operation, so try removing the connection string completely, and use just name= and sqlstatement= and see what happens.

[maxmaggot]

Thanks Dal,

The compact and repair had no effect. It was the first thing I tries too.

The strConnect = "Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" & CurrentDb.Name & ";" looks alot better to me than what was there. It seems to do the same job as before.

When I remove Name:=CurrentDb.Name I get a prompt in word to confirm the data source (access, word, html, etc.) and if I choose db it just sits at the template document but with no error.

When I remove Connection:=strConnect, I get the same error as before.

So to my eyes the problem is occuring at Name:=CurrentDb.Name with the possibility of the db being called to open twice.

Here's the code I have now (very little change).

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 = "Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" & CurrentDb.Name & ";"
        .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

P.S. This has only recently started happening. Same code/same calls.

Two things changed.
Firstly it's a hybrid db now so it shares its tables and code with sharepoint while running on the desktop (probably the cause of the issue)

Secondly and much less likely is that I remember from a week or so ago installing a few office updates.

Any ideas? Thanks for your help

K

[maxmaggot]

I rewrote the code from a sample that works,

I recreated the mail merge document using office 2007 docx.

My version still puts up with the error

"error has occurred: The database has been placed in state by 'Admin' on machine 'Ueer-Tosh' that prevents it from being opened or locked.

Here is the new code I tried

Private Sub cmdNonPaymentMiniMarathon_Click()

    Dim objWord As Word.Document
    
    Set objWord = GetObject(CurrentProject.Path & "\MailMerge2.docx", "Word.Document")
    'Make Word Visible
    objWord.Application.Visible = True

    'Set the mail merge data source as the current database
    objWord.MailMerge.OpenDataSource _
    Name:=CurrentDb.Name, _
    LinkToSource:=True, _
    Connection:="TABLE AGENCIES", _
    SQLStatement:="SELECT * FROM [MMNPMailMergeQuery]"

    'Execute the mail merge
    objWord.MailMerge.Execute

End sub

Does anyone think it may be because it is connecting to a sharepoint server that the db is getting locked. As stated previously this only started since I connected and synced up with sharepoint.

Thanks again
K

[maxmaggot]

Also read this which suggests that Access Sharepoint lists need to be connected to differently than access tables

http://answers.microsoft.com/en-us/o...5-ffd8e1d790ac

Painful news

[maxmaggot]

Anyone have any ideas:

strSQL = "SELECT * FROM [MMNPMailMergeQuery]"
strConnect = "Provider=Microsoft.ACE.OLEDB.14.0; Data Source=" & CurrentDb.Name & ";"
objApp.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, Connection:=strConnect, SQLStatement:=strSQL

It stops on objApp.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, Connection:=strConnect, SQLStatement:=strSQL

I have no more avenues around this. It musn't be that hard.

If I had a guess its elevating access/word to admin level and security is kicking in. Very rough guess.