Suppress Confirm Datasource steps - Mail Merge- MS Word

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.

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

Try this:

DoCmd.SetWarnings False ' Beginning of code

' Your code here

DoCmd.SetWarnings True ' End of code

---

Ryan Shuell

Answer 7

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.

Answer 8

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

---

Cindy Meister, VSTO/Word MVP, my blog

Answer 9

hi cindy,

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

Thanks a lot.

Answer 10

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