Results 1 to 6 of 6

Error when creating mail merge doc or any word doc

  1. #1
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91

    Error when creating mail merge doc or any word doc

    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.
    Last edited by June7; 08-22-2013 at 10:53 PM. Reason: merge posts

  2. #2
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    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
    Code:
    strConnect = "Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" & CurrentDb.Name & ";"
    or
    Code:
    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.

  3. #3
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91
    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).

    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 = "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

  4. #4
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91
    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

    Code:
    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

  5. #5
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91
    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

  6. #6
    maxmaggot is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Location
    Ireland
    Posts
    91
    Anyone have any ideas:

    Code:
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating Report using Word Mail Merge
    By JoeM in forum Reports
    Replies: 1
    Last Post: 02-20-2013, 11:32 AM
  2. Help needed with mail merge to Word
    By mhart in forum Import/Export Data
    Replies: 1
    Last Post: 08-12-2011, 12:22 PM
  3. Mail merge with word
    By alexc333 in forum Access
    Replies: 0
    Last Post: 07-26-2011, 12:06 PM
  4. Mail merge from from Access to Word
    By williamgladstone in forum Access
    Replies: 1
    Last Post: 03-22-2011, 12:00 PM
  5. Mail Merge from Access to Word
    By Rachelkm2 in forum Programming
    Replies: 1
    Last Post: 05-29-2009, 02:49 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums