Could someone help on this?
I am working in A2K, and have an .MDB that I am using to launch a Word Doc with mail merge fields already made. I am using the example in Access 2003 VBA pg. 462 as my guide.

My code bombs at the opendatasource method. The error I'm getting is saying the "Server threw an error" (!!) and it references number -2147417851 (80010105). Word does launch to the correct document, so the filename property of the dialog box is at least populated. I want to merge the data, and in so doing, pass a WHERE clause into the SQL. I just can't get it to take my arguments for the opendatasource method. Does anyone know WHICH common dialog controls components to check? Am I missing an argument- perhaps they have changed between 2K and 2003?

************ my code
Private Sub cmdOpenPCL_Click()
' this is an attempt to open a Word document and start a mail merge
Dim strFilePath As String
Dim objWord As Word.Document
Me.cmdlg.DialogTitle = "Choose File Name and Location"
Me.cmdlg.ShowOpen
strFilePath = Me.cmdlg.FileName


Set objWord = GetObject(strFilePath, "Word.document")
' make word visible
objWord.Application.Visible = True

objWord.MailMerge.OpenDataSource Name:="C:\Documents and Settings\My Name\My Documents\" & _
"CTU Provider Management 11022005 1726\", _
linktosource:=True, _
Connection:="TABLE tblPatient", _
sqlstatement:="SELECT * FROM tblPatient" ' bombs here with "server threw an error"

' execute the mail merge
objWord.MailMerge.Execute

End Sub

Hi
According to the VBA Manual:
Dim docNew As Document

Set docNew = Documents.Add

With docNew.MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource _
        Name:="C:\Program Files\Microsoft Office" & _
        "\Office\Samples\Northwind.mdb", _
        LinkToSource:=True, AddToRecentFiles:=False, _
        Connection:="TABLE Orders"
End With

So, it looks everything you are doing is fine, except that
you forget the \NameofTheDatabase.mdb

I am not sure what this has to do with common dialog controls.


Database Agreements

Oops...... I did miss that. But unfortunately when I add the .mdb, it still errors with an "application or object generated"error #287.

(I'm using the Dialog control to allow the user to select which .doc they want to open.) I want to get at the SQL statement so I can pass in a WHERE clause......

Well, which line now gives the error?

If you don't mind me saying so, why not create a Reference to the Word Object Model, and then just use the Word Object Model instead of creating objects.
Then you can use Intellisense to make sure you are entering info properly.

I think this is a tough problem, without me having to test it out completely on my machine.
One thing that comes to mind is the way that Word is going to tap into the data in MS Access.
Does Word have permission?
Does the table actually exist? Can you run the SQL Statement in MS Access.
Is the Database table protected with a password.
This would make it fail, and then give you some obscure error which makes no sense.
Then you would most likely have to use a different way to use the mail Merge, using the ODBC connection to the database in which a password is taken.
One thing I've been wondering about is how does Word access the MS Database? Does it use DAO, ODBC, ADO, etc...

Hope this helps,


Database Agreements

I'm still getting the "Server threw an error" (!!) and it references number -2147417851 (80010105)coming from the opendatasource statement. (I believe the other error probably came from asking Word to open when it already was open).
As far as permissions go, I have not set regular Access security on the mdb. The table does exist and is healthy, and my SQL does execute within Access. The example from the book did not give an explicit reference to DAO nor ODBC, so I'm assuming Access is connecting directly (?) or perhaps using DAO beneath it's layers (???). The example does not have an ODBC connection string in it like some of the other examples. My husband took a look and checked the path to the MDB and did find an extra space I had not typed in, but we fixed it, and tested it by making a shortcut with the path and it worked, so the path is correct. Intellisense is working on the opendatasource statement as I type.

When you suggest making a reference to the Word Object Model, I'm not sure what you mean..... I did add the Word Objects under the References Tab when I first started working with this. The reference still stands. I even re-installed Office (ODE 2K)to be sure it was clean, and it still errors.

I'm trying to open a Word template by allowing the user to select the document. The template already has the mail merge fields set up in it. My code is launching Word and selecting the correct document. It is just not completing the merge, and I would like to get to the SQL so I can use my user's input in the WHERE clause. ( have no experience with using VBA with Word.) Any suggestions or examples are very much appreciated.

Thanks, Loralee

I tested this out for myself, created a Word Document, and a simple Access Database with a single table.
I put 3 fields in the Mail Merge Document.
For someone who doesn't know much about mail merge there seems to be a lot to getting this to work.

The problem seems to be that in the OpenDataSource, if you add the .SqlStatement, it doesn't work on my computer.
I don't get your undescriptive error, I just get "Word is unable to open the DataSource."
So, if I uncomment the .SqlStatement it work!

Here's the code I used to get this to work.
I'm sure there is more to getting it to do what you want.
So, what I said before may be correct that you need to use an ODBC connection, or figure out why its getting this on my computer, not ODBC, not Access, but wdMergeInfoFromODSO

I used a File Dialog to open up a Word Doc.

----------------------code ----------------------------------

Function lfuCreateWordMerge()
' this is an attempt to open a Word document and start a mail merge
Dim strFilePath As String
Dim objWord As Word.Document

Dim msoDl As Office.FileDialog

Const ctxAccessDb = "C:\Documents and Settings\OnWardsGoXP\Desktop\Word Access\Word2.mdb"

    Set msoDl = Application.FileDialog(msoFileDialogFilePicker)
    With msoDl

        .ButtonName = "Merge"
        .Title = "Begin Word Doc Merge"
        .AllowMultiSelect = False
        .InitialFileName = "C:\Documents and Settings\OnWardsGoXP\Desktop\Word Access\"
        .Filters.Clear

        .Filters.Add "Word Docs", "*.doc"

        If .Show = False Then
        Else
            strFilePath = .SelectedItems(1)
        End If
    End With



    MsgBox "The File Name Selected is" & strFilePath


    Set objWord = GetObject(strFilePath, "Word.document")
    ' make word visible
    objWord.Application.Visible = True

    With objWord.MailMerge
        .MainDocumentType = wdFormLetters
        'MsgBox "Data Source = " & .DataSource.Name, vbInformation, "Data"


        If objWord.MailMerge.State = wdMainAndSourceAndHeader Or _
                objWord.MailMerge.State = wdMainAndDataSource Then
            MsgBox "Mail Merge Stage Ok = " & objWord.MailMerge.State
        Else
            MsgBox "Mail Merge Stage Not Ok" & objWord.MailMerge.State
        ' With myMerge.DataSource
        ' .FirstRecord = 1
        ' .LastRecord = 3
        ' End With
        End If


        .OpenDataSource Name:=ctxAccessDb, _
            LinkToSource:=True, _
            Connection:="Table tblPatient"
            'sqlstatement:="SELECT * FROM tblPatient"
            ' OpenExclusive:=True, _
            ' ReadOnly:=True, _


        .Destination = wdSendToNewDocument
        MsgBox "Mail Merge State" & .State
        .Execute (True)
        'MsgBox "Data Source.ConnectString = " & .DataSource.ConnectString, vbInformation, "Data"
        MsgBox "Data Source Type " & .DataSource.Type & vbCrLf & _
        "1 is MS Access(wdMergeInfoFromAccessDDE" & vbCrLf & _
        "5 is ? (wdMergeInfoFromODSO)" 'WdMailMergeDataSource - 1 is MS Access"


        .DataSource.Close
    End With

    MsgBox "Data Source Closed"
End Function



--------------------------------------------

Database Agreements

Thank you!

I'm going to work on this over the weekend. I'm curious as to whether mine is failing at the SQL as I didn't try it line by line....... I'll post results. Also wondering if there may be another reference I need to select (for Automation or SQL).

Thank you, Salvatore,

I've been working with this more on than off today. I did find a way to finally do what I wanted, but no matter what I did the OPENDATASOURCE just errors out with the same "server threw exception" error. When I comment out my SQL statement it throws "argument not optional"- but VBA help says it IS optional!

Anyway, I ended up setting up my SQL statement then sending it to a query through QueryDefs. My Word Templates are all set up for mailmerge, and apparently the OPENDATASOURCE gets what it wants the way it wants from the Word Doc.

BTW, which file dialog were you referencing? I'm trying it your way and I'm not sure which reference to check.

Thanks,
Loralee

Loralee,

Word merges are completely alien to me but from what you asked you wanted a way to build an option into the sql statement. Presuming you are doing this from a form what you could do is something like this:-

Dim WName as String
Dim SSQl as String

WName = Me.Woption
SSQl ="SELECT * FROM tblPatient WHERE tblPatient.FieldyourLookingfor =" &WName


Woption would be the name of the control on the form which would have to be set up as a look up on posibley another table containing the names of the options and obviously FieldyourLookingfor would be replaced with the field name within the table.

Hope that makes sense.


Jim