Word unable to open data source (database converted from Acc97 to Acc2002)

[Scott Bellinger]

I'm in the process of converting several mission critical databases from
Access 97 to Access 2002 and in the process of testing some of the first
conversions, ran into the error "Word is unable to open data source" when
attempting to create a simple mail merge into Word 2002.

Details:
Access 97 database that was converted was a split (back-end/front-end),
replicated database. It was secured using an Access 97 system.mdb. I joined
the existing workgroup with the workgroup administrator in Access 2002.

Any help would be greatly appreciated.

[Lee Myers]

Hi Scott,

I've been struggling with a similar problem, moving from Access 2000 to
Access 2002 - though I did not convert the database, I'm just using Access
2002 to work with it. I get the same error message: Run-time error '5922':
Word was unable to open the data source

I posted this problem in access.interopeledde, but they suggested I could
get more help here. I hope someone can help with this. Perhaps the little
I've figured out will help solve the problem

To summarize:
First I made sure that the Application Title is blank in Startup. Didn't
help.
Then I made very sure that Access was not open Exclusively.

The only progress I made is this:

I can consistently get the whole thing to work repeatedly only if I

- comment out objWord.Save (in the Access proceedure)
- comment out objWord.Close (in the Access proceedure)
- close the Access Database to be sure certain it is not open exclusively
- open the merge document with Word
- find the Data Source
- save the merge document
- open the the Access database and run the proceedure from a form that calls
the proceedure.

Someone (I wish I knew who) suggested I use an Application object with
GetObject,
and set an additional Document object variable to objApp.Documents.Add, but
I couldn't figure out how to do that.

Here's the code I have now:

' Merge portion adapted from Microsoft KB Article # Q159328

Dim objWord As Word.Document

Set objWord =
GetObject("V:\Membership\Thankyou\ThankyouMergeLowDonor.doc",
"Word.Document")

' Make Word visible.
objWord.Application.Visible = True

objWord.MailMerge.OpenDataSource _
Name:="C:\Membership\Front End\DEV\Membership2K_FE_DEV.mdb", _
LinkToSource:=True, _
Connection:="TABLE tblBatchThankyouTemp", _
SQLStatement:="SELECT * FROM [tblBatchThankyouTemp]" _
& " ORDER BY fldThankyouKind,
fldLastName,fldFirstName,fldAffiliation", _
OpenExclusive:=False

' Execute the mail merge.
objWord.MailMerge.Execute

' objWord.Save
' objWord.Close

Set objWord = Nothing

Thanks, Lee

"Scott Bellinger" <scott_b...@michoilandgasnews.com> wrote in message
news:7Mia8.2772$P21.2...@newsread1.prod.itd.earthlink.net...

[Cindy Meister -WordMVP-]

Hi Scott,

Word 2002 uses OLEDB as the link method, and I suspect that it may be running
into some problems with the database security. It may be worth a try using
the older merge methods DDE or ODBC. That should at least tell us if it's the
Access 2002 environment, or the OLEDB connection method.

Go into Word's Tools/Options/General dialog box and activate "Confirm
conversions on opening". Now, when you select the Access data source, try DDE
and ODBC - and hope it works.

If it doesn't, the only other thing I know to try would be to click "New
Source" in the "Open data source dialog box". This leads you through setting
up the OLEDB connection, with being able to specify things like passwords.
The result is an ODC file that stores all this information. In the future,
you simply choose the ODC file, rather than the data base.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

[Cindy Meister -WordMVP-]

Hi Lee,

> Someone (I wish I knew who) suggested I use an Application object with
> GetObject,
> and set an additional Document object variable to objApp.Documents.Add, but
> I couldn't figure out how to do that.
>

I don't know, either, but I was part of a discussion in a private group where
this question was brought up. I suggested you try adding the parameter
SubType:=wdMergeSubTypeWord2000 to the OpenDataSource method so that you
could merge the same as in previous versions. Did you try that?

[Lee Myers]

Thank you Cindy!

Indeed, SubType:=wdMergeSubTypeWord2000 turned the trick!

I got a similar suggestion from Rita Nikas in the access.interopoledde
group.

I really appreciate all the help. I apologize for the cross posting, however
it seemed at the time like I should move the thread here.

Thanks Again,
Lee

"Cindy Meister -WordMVP-" <CindyM...@swissonline.ch> wrote in message
news:VA.00005548.00418e69@speedy...

[Scott Bellinger]

Thanks, Cindy.

Just before checking your post I had gone into the ODBC Data Source
Administrator and configured the data source I had attempted to use in my
test, specifying my system database, login name and password, and, voila(!)
it worked fine.

I appreciate you taking time to respond to my post and will keep your
information on hand should the technique I used not work in other
situations.

"Cindy Meister -WordMVP-" <CindyM...@swissonline.ch> wrote in message

news:VA.00005547.00417949@speedy...

[Cindy Meister -WordMVP-]

Hi Scott,

> Just before checking your post I had gone into the ODBC Data Source
> Administrator and configured the data source I had attempted to use in my
> test, specifying my system database, login name and password, and, voila(!)
> it worked fine.
>

Cool :-) Thanks for letting me know what worked!

[Cindy Meister -WordMVP-]

Hi Lee,

> I really appreciate all the help. I apologize for the cross posting, however
> it seemed at the time like I should move the thread here.
>

Not a problem :-) I told them to have you post here; go straight to the
source, as it were.

Cindy

[Lee Myers]

Thank you very much Cindy. I must say I'm amazed to witness such a
coordinated effort!

Can you direct me to where I can get more information on Subtypes? Q285332
suggests I can find it in VBA Help, but I didn't see anything about subtypes
in the OpenDataSource Method topic.

Thanks again, Lee

"Cindy Meister -WordMVP-" <CindyM...@swissonline.ch> wrote in message

news:VA.00005563.00292a15@speedy...

[Cindy Meister -WordMVP-]

Hi Lee,

> Can you direct me to where I can get more information on Subtypes? Q285332
> suggests I can find it in VBA Help, but I didn't see anything about subtypes
> in the OpenDataSource Method topic.
>

No, neiter have I. I've complained about this a number of times in the past 18
months. What's worse (from my POV as a Wordie) is that the KB article you have
isn't even tagged for Wd2002! I've also asked for more information a number of
times from MS, but nothing has been forthcoming...

This KB article is, as far as I know, the only publicly available
documentation.

[Lee Myers]

Hello CIndy,

Well, thank you. Too bad it's so hard to find - I'd like to understand more.
All I can say is that wdMergeSubTypeWord2000 = 8, which I picked up from
Rita Nikas.

I guess it's just onward we go!

Cheers
Lee

"Cindy Meister -WordMVP-" <CindyM...@swissonline.ch> wrote in message

news:VA.00005580.0019ec24@speedy...