Mail Merge fails between Access 2000 and Word 2003

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?
LVL 13
LucasMS Dynamics DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nico5038Commented:
0
LucasMS Dynamics DeveloperAuthor Commented:
I dont get that dialog box.  I get one that shows you all your tables, views and makes you pick one.
0
nico5038Commented:
Hmm, running out of thoughts, will ask the other experts to join.

Nic;o)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jjafferrCommented:


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
LucasMS Dynamics DeveloperAuthor Commented:
Thanks Nico
0
LucasMS Dynamics DeveloperAuthor Commented:
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
nico5038Commented:
You can use www.ee-stuff.com to upload an image and post the link here.

Nic;o)
0
LucasMS Dynamics DeveloperAuthor Commented:
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
LucasMS Dynamics DeveloperAuthor Commented:
Any ideas??
0
nico5038Commented:
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
LucasMS Dynamics DeveloperAuthor Commented:
Damn micro$oft .... ok i'll try that.
0
jjafferrCommented:
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
nico5038Commented:
I already did jaffer, check my previous comments <LOL>

Nic;o)
0
jjafferrCommented:
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
jjafferrCommented:
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
LucasMS Dynamics DeveloperAuthor Commented:
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
nico5038Commented:
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
jjafferrCommented:
what I meant was, just do the mail merge again in Word 2003, and leave Access 2000 they way it is.

jaffer
0
LucasMS Dynamics DeveloperAuthor Commented:
Yeah, that was my next step.

I'll let you know how it goes.
0
LucasMS Dynamics DeveloperAuthor Commented:
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
Leigh PurvisDatabase DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leigh PurvisDatabase DeveloperCommented:
In fact - it's the same for Word 2002 I think - so you could probably have

If myWord.Version >= 10 Then
0
LucasMS Dynamics DeveloperAuthor Commented:
Leigh, you did it man!  It worked!  

Thanks...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.