Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 7th, 2005, 12:41 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default Access launching Word for mail merge

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

 
Old November 7th, 2005, 01:08 AM
Authorized User
 
Join Date: Sep 2004
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 7th, 2005, 01:28 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

 
Old November 7th, 2005, 02:54 PM
Authorized User
 
Join Date: Sep 2004
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 8th, 2005, 02:33 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

 
Old November 9th, 2005, 01:23 AM
Authorized User
 
Join Date: Sep 2004
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 11th, 2005, 12:07 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

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).

 
Old November 13th, 2005, 09:37 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

 
Old November 14th, 2005, 04:24 PM
Authorized User
 
Join Date: Oct 2005
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Figgis
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
"Pushing" from Access to Word via Mail Merge Loralee BOOK: Access 2003 VBA Programmer's Reference 2 March 8th, 2006 10:20 PM
Mail Merge with MS Word bezaman Classic ASP Basics 1 October 6th, 2005 04:13 PM
Access/Vba mail merge to word help alfonse Access VBA 0 July 28th, 2005 05:11 AM
Access to Word Mail Merge Automation kareltje Access VBA 6 June 9th, 2005 04:54 AM
Word E-Mail Merge using Access Tables Richard Lally Access 2 February 7th, 2005 04:48 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.