Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Mail Merge fails between Access 2000 and Word 2003

Posted on 2006-04-12
23
Medium Priority
?
460 Views
Last Modified: 2008-01-09
This one really boggles my mind, but i think i know what it is.

I'm using code to open a word document that already has mail merge fields, and i'm executing the mail merge from VBA in Access.  Below is my code:

----------------------------------------
Dim myWord As Object
Dim file As String
Dim sql As String

On Error GoTo errHandler

DoCmd.Echo True, "GENERATING YOUR LETTER....Please Wait......Thank You...."

Set myWord = CreateObject("Word.Application")
'myword.Visible = True
Select Case (mLetterTitle)
   
    Case "Initial Notification Letter":

        sql = "select * from zLIFE_general where cid = '" & Form_LIFE_Letters.txtCID & "'"
        file = "C:\Letters\Life and Death\NYL\Final Proof.doc"
        myWord.Documents.Open file, , True
        myWord.Application.DisplayAlerts = wdAlertsNone
   
        With myWord.ActiveDocument.MailMerge
            .MainDocumentType = wdFormLetters
            .OpenDataSource Name:=CurrentDb.Name, SQLStatement:=sql, ReadOnly:=True
            .ViewMailMergeFieldCodes = False
           .Execute False
        End With
        myWord.Documents(file).Close SaveChanges:=wdDoNotSaveChanges
       
        myWord.Application.Visible = True
        'myword.Application.SetFocus
        myWord.Application.WindowState = wdWindowStateMaximize
   
        myWord.ActiveDocument.PageSetup.FirstPageTray = wdPrinterLowerBin
        myWord.ActiveDocument.PageSetup.OtherPagesTray = wdPrinterDefaultBin
' More code is below but i cut it out here
End Select

set myWord = nothing
---------------------------------------------------------------------------

This code works great between access 2000 and word 2000.  When i try to run this code on a windows XP machine with word 2003 installed and access 2000 installed, when the merge happens word asks me for a friggin datasource via a dialog box.  WHY?? Is it a security feature of some sort, and how can i disable it?
0
Comment
Question by:Lucas
  • 10
  • 6
  • 5
  • +1
23 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 16439161
0
 
LVL 13

Author Comment

by:Lucas
ID: 16440096
I dont get that dialog box.  I get one that shows you all your tables, views and makes you pick one.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16449402
Hmm, running out of thoughts, will ask the other experts to join.

Nic;o)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 27

Expert Comment

by:jjafferr
ID: 16449456


Here is another way to transfer data from Access to Word, using Bookmarks:
http://www.experts-exchange.com/Databases/MS_Access/Q_21023384.html

jaffer
0
 
LVL 13

Author Comment

by:Lucas
ID: 16449462
Thanks Nico
0
 
LVL 13

Author Comment

by:Lucas
ID: 16554838
Jaffer, i will have to use your link as a last resort.  But i still don't see why this would work in Access 2000 and Word 2000 and all of a sudden fail in word 2003?  

I'm stumped, and i don't really want to call up Microsoft and ask them what the heck is up with this.  It's probably a security issue of some sort but i just cant figure it out.  I have a screen shot of what i get after i run the code so you can see what i'm talking about.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16555024
You can use www.ee-stuff.com to upload an image and post the link here.

Nic;o)
0
 
LVL 13

Author Comment

by:Lucas
ID: 16555141
That's sweeeet Nico..

Here is the link:
https://filedb.experts-exchange.com/incoming/ee-stuff/31-performing-Mailmerge.JPG 

This happens when a user clicks on one of my buttons to start up the mail merge.  

It gets to this line in my code:
.OpenDataSource Name:=CurrentDb.Name, SQLStatement:=sql, ReadOnly:=True

and then i get that window.

0
 
LVL 13

Author Comment

by:Lucas
ID: 16565172
Any ideas??
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16565272
Nope, I get the impression it has to do with the increased security that has been build in, but this one is new for me.
A shot in the dark might be to try to "protect" the database by a password and add that to the opening of the datasource.

Nic;o)
0
 
LVL 13

Author Comment

by:Lucas
ID: 16565397
Damn micro$oft .... ok i'll try that.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 16565453
I have posted the link to this Q to the Experts notifying thread,
so I hope someone jumps in who can help :o)

jaffer
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16565467
I already did jaffer, check my previous comments <LOL>

Nic;o)
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 16565493
oops, I didn't notice that,
so if for the 2nd time no one reponds, then it is a new error that no one have seen before, like you said.

jaffer
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 16565721
Lucas

One thing came to my mind,
I helped a friend with Bookmarking on Word 2000 and Access 2000, it worked ok.
then from scratch I worked on the same project but on Word 2003 and Access 2003, which worked ok too, but when my friend tried it on his system which had Access 2000 and Word 2000, the code acted funny on some lines of code.

It might be the same case you are facing,
so I would recomend you work on the mail merge from scratch on Word 2003 (you start with a new document, then paste parts of the old mail merge document).

jaffer
0
 
LVL 13

Author Comment

by:Lucas
ID: 16565748
Thanks Jaffer, but i can't because the corporation is only switching their MS office products to 2003.  They're leaving MS Access 2000.

I don't want to do bookmarks either because this works great right now, PLUS i have over 100 letters that i'm using this code for and i haven't run into any problems.  Right now i'm fine because everyone is using office 2000 and access 2000.  It will be a problem when the switch comes.

I'm gonna try and maybe dig on micro$oft'$ site about this.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16565768
Hmm, one other thing you could try is to switch from late binding to early binding, check:
http://support.microsoft.com/kb/245115/
It's not recommended, but who knows...

Nic;o)
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 16565870
what I meant was, just do the mail merge again in Word 2003, and leave Access 2000 they way it is.

jaffer
0
 
LVL 13

Author Comment

by:Lucas
ID: 16565871
Yeah, that was my next step.

I'll let you know how it goes.
0
 
LVL 13

Author Comment

by:Lucas
ID: 16616478
Well, i've tried it, but i still don't get passed that dialog box.

I've used MS Word Object Library 11 but it still doesn't work.  I'm going to dig on microsoft's site.
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 2000 total points
ID: 16725306
Hello.
I remember seeing this in the Notify thread and meant to come by - but you know... things get in the way.

Word 2003 is a bugger for this.  Apparently it's an advancement! :-S  
Not to worry though.

You can try changing the With block of your code to the following.

    With myWord.ActiveDocument.MailMerge
        .MainDocumentType = wdFormLetters
        If Fix(myWord.Version) > 10 Then
            .OpenDataSource Name:=CurrentDb.Name, SQLStatement:=sql, ReadOnly:=True, SubType:=8
        Else
            .OpenDataSource Name:=CurrentDb.Name, SQLStatement:=sql, ReadOnly:=True
        End If
        .ViewMailMergeFieldCodes = False
        .Execute False
    End With

It makes it use DDE still - a la Word 2000 and 97.  Whereas the newer versions try to use the more efficient OLEDB providor.  Which is a good idea - in principal!
But since you've been happily using DDE (whether you know it or not ;-) in 2000 then you shouldn't mind continuing to do so.
Hope this helps.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16726514
In fact - it's the same for Word 2002 I think - so you could probably have

If myWord.Version >= 10 Then
0
 
LVL 13

Author Comment

by:Lucas
ID: 16798926
Leigh, you did it man!  It worked!  

Thanks...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question