none
Suppress Confirm Datasource steps - Mail Merge- MS Word RRS feed

  • Question

  • Hi all,

    i have ms access  2010 database. From access form, i am opening mail merged document which is made in Word 2010.

    while opening the document its prompting "Confirm datasource" dailogbox.

    how can i suppress this dialogbox?

    Pls reply.

    - Thanks & Regards,

    Prajakta.

    Friday, January 18, 2013 10:24 AM

Answers

  • Have you tried my recommendation about recording a macro and using that syntax?

    Cindy Meister, VSTO/Word MVP, my blog

    Monday, January 21, 2013 4:35 PM
    Moderator
  • You still haven't given us the exact error message, but looking at your code, I think that the arguments in OpenDataSource aren't correct for a *.accdb database. I don't think that would have SubType:=wdMergeSubTypeWord2000. There may also be a problem with the SQLStatement parameter.

    My recommendation would be to record a macro while linking up to the database as a user. Compare the code Word wants to use with what you have and adjust yours to match.


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, January 18, 2013 4:16 PM
    Moderator

All replies

  • Hi all,

    i have ms access  2010 database. From access form, i am opening mail merged document which is made in Word 2010.

    while opening the document its prompting "Confirm datasource" dailogbox.

    how can i suppress this dialogbox?

    Pls reply.

    - Thanks & Regards,

    Prajakta.

    Friday, January 18, 2013 10:26 AM
  • Hi Prajakta

    Is it a confirm data source question? Or is it a question about whether to allow the execution of an SQL query? Can you please give us

    1. The exact wording of the message

    2. The steps or code being used to opne the mail merge document


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, January 18, 2013 10:47 AM
    Moderator
  • Hi Cindy,

    thanks for your swift reply.

    This is the code which i have written on Access form's command button to open mail merged document.

    On Error GoTo ErrorHandler
    
        Dim Word As Word.Application
        Dim Form As Word.Document
    
        Set Word = CreateObject("Word.Application")
        Set Form = Word.Documents.Open("Q:\Working_Folder\Prajakta\R & D\Call\Try\Try.docx")
    
    
        With Word
            Word.Visible = True
    
            With .ActiveDocument.MailMerge
                .OpenDataSource Name:="Q:\Working_Folder\Prajakta\R & D\Call\Try\Try.accdb", _
                ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=False, _
                AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
                WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
                Format:=wdOpenFormatAuto, SQLStatement:="employee", _
                SQLStatement1:="SELECT * FROM employee", SubType:=wdMergeSubTypeWord2000, OpenExclusive:=False
                .Destination = wdSendToNewDocument
                .Execute
                .MainDocumentType = wdNotAMergeDocument
            End With
        End With
    
        Form.Close False
        Set Form = Nothing
        Set Word = Nothing
    
    Exit_Error:
        Exit Sub
    ErrorHandler:
        
        Word.Quit (False)
        Set Word = Nothing

    Friday, January 18, 2013 1:04 PM
  • You still haven't given us the exact error message, but looking at your code, I think that the arguments in OpenDataSource aren't correct for a *.accdb database. I don't think that would have SubType:=wdMergeSubTypeWord2000. There may also be a problem with the SQLStatement parameter.

    My recommendation would be to record a macro while linking up to the database as a user. Compare the code Word wants to use with what you have and adjust yours to match.


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, January 18, 2013 4:16 PM
    Moderator
  • I'd second Cindy's comments. This should be enough in Office 2010, unless you are having problems with Word finding that the database is opened exclusively by something else, which will almost certainly happen if you are debugging from the Access VB Editor and you do not save all your VB edits before running the code.

    With .ActiveDocument.MailMerge
                .OpenDataSource Name:="Q:\Working_Folder\Prajakta\R & D\Call\Try\Try.accdb", _
                SQLStatement:="SELECT * FROM [employee]"
    


    Peter Jamieson

    Friday, January 18, 2013 5:09 PM
  • Try this:

    DoCmd.SetWarnings False ' Beginning of code

    ' Your code here

    DoCmd.SetWarnings True ' End of code

    Ryan Shuell


    • Edited by ryguy72 Saturday, January 19, 2013 1:02 PM
    Saturday, January 19, 2013 1:01 PM
  • Cindy,

    Its not showing any error message. But i have to go through so many steps. Is there any method to suppress all steps programatically?

    Steps are as follows:-

    1) Step 1-"Confirm Data source" - In this step, i have to select Show all check box. then It shows data source list. out of them i select MS access Database via ODBC (*.mdb, .accdb)

    2) Step 2- Then it gives "ODBC Microsoft Access Driver Login Failed" dialog box. After clicking on Ok.

    3) Step 3- "Login" Window display. i have to give access database path again.

    4) Step 4- Then Select table. (In my case i have created access query. so i have to click on "Options..." tick to views.Then i can select access query as data source. )

    After doing all these steps, i can have the updated data in mail merge document.

    Monday, January 21, 2013 5:21 AM
  • Have you tried my recommendation about recording a macro and using that syntax?

    Cindy Meister, VSTO/Word MVP, my blog

    Monday, January 21, 2013 4:35 PM
    Moderator
  • hi cindy,

    yes. i tried this. and now its working fine... :)

    Thanks a lot.

    Tuesday, January 22, 2013 10:47 AM
  • I think you are probably seeing the type of problem I mentioned - if you try to run your VBA macro, when Word tries to open the data source, it finds that the database is in a state which will not allow that to happen. This is a very irritating problem when working with Access VBA and Word, and it appears to be worse in Access 2010 than before. Perhaps Access experts know how to solve it.

    What I find is that I have to create a Public VBA Function to run my Sub, then an Access Macro with a RunCode command that runs that function. At the very least, I have to make sure all the objects are saved and the VBA editor is closed. Usually, it's simpler to close and reopen the database. However, that's during development. I would hope that when you no longer need to modify the Macro or the VBA, things will be a little easier. You may also find that the location of the Access database (whether it is trusted or not) also has an impact on the number of questions you have to answer.

    The trouble is that once Word has failed to connect using OLE DB, it probably will display either the ODBC dialog or the "Confirm File" dialog. At that point you may be able to achieve a connection via ODBC or DDE, but an OLE DB connection (the default) is what you should be aiming for unless there is a very good reason to use one of the other methods.

    Finally, if you have applied any form of database security to your accdb (e.g. a database password), you will need to include that in a Connection string (and you may need to create a .odc to do it), unless you connect via DDE. The fact that the database is already open is irrelevant.



    Peter Jamieson

    Tuesday, January 22, 2013 11:01 AM