Access to Word (XP) Automation Problem - Driving me Crazy(ier)!

[Shannon Rotz]

I had an Access 97/2000 app. which was doing some Word automation, using the
following code, and worked fine:

With myMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=currentdb.Name, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Connection:="Query " & sQueryName
End With

However, this did not work in XP. So I took a look at the Knowledge Base,
and discovered that the default connection had changed from DDE to OLEDB.
Based on the results of that article, I changed the code to this:

With myMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=currentdb.Name, _
AddToRecentFiles:=False, _
sqlstatement:=SqlStmt
End With

where SQLStmt = "Select * from [Query]"

However, this does not work either - I keep getting a "Confirm Data Source"
dialog box. My help files are stubbornly continuing to give me Office 2000
help (I have Office 2000 installed as well), which of course is no use in
this situation.

Can anyone suggest an alternate way to link Word to a query before I tear
out all of my hair?



Shannon

 

[Ray Wei]

Mine goes like this and it works. Make an ODBC connection and replace where XXX,YYY,ZZZ in my code with yours. It works on my Office 2002

Hope this would help

Ra

'Set up the data source for the mail merge
Dim sSQL As Strin
Dim sConn As Strin
Dim sFileName As Strin

sSQL = "SELECT * FROM massmailing WHERE cvad_cvr_id=" & Text
sConn = "DSN=CTS; UID=XXX; DATABASE=YYY; PWD=ZZZ;

'Start a new main document for the mail merge
Set oMainDoc = oApp.Documents.Ad

With oMainDoc.MailMerg

.MainDocumentType = wdFormLetter
.OpenDataSource Name:="", Connection:=sConn, SQLStatement:=sSQL, SubType:=wdMergeSubTypeWord200




----- Shannon Rotz wrote: ----

I had an Access 97/2000 app. which was doing some Word automation, using th
following code, and worked fine

With myMerg
.MainDocumentType = wdFormLetter
.OpenDataSource Name:=currentdb.Name,
LinkToSource:=True,
AddToRecentFiles:=False,
Connection:="Query " & sQueryNam
End Wit

However, this did not work in XP. So I took a look at the Knowledge Base
and discovered that the default connection had changed from DDE to OLEDB
Based on the results of that article, I changed the code to this

With myMerg
.MainDocumentType = wdFormLetter
.OpenDataSource Name:=currentdb.Name,
AddToRecentFiles:=False,
sqlstatement:=SqlStm
End Wit

where SQLStmt = "Select * from [Query]

However, this does not work either - I keep getting a "Confirm Data Source
dialog box. My help files are stubbornly continuing to give me Office 200
help (I have Office 2000 installed as well), which of course is no use i
this situation

Can anyone suggest an alternate way to link Word to a query before I tea
out all of my hair



Shanno

 

[Cindy M -WordMVP-]

Hi Shannon,

1. Record the connection in a macro, linking using DDE (if that's what you
prefer. And if you don't know how to select the connection method, see the
Word 2002 section of my website's Mail Merge FAQ)

2. The macro recorder may not record exactly the correct code, most
specifically, the new SubType argument. Make sure this is set to
wdMergeSubTypeWord2000 Note that there's a KB article for VB on this subject
in case you want to read up on it.

I had an Access 97/2000 app. which was doing some Word automation, using the
following code, and worked fine:

With myMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=currentdb.Name, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Connection:="Query " & sQueryName
End With

However, this did not work in XP. So I took a look at the Knowledge Base,
and discovered that the default connection had changed from DDE to OLEDB.
Based on the results of that article, I changed the code to this:

With myMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=currentdb.Name, _
AddToRecentFiles:=False, _
sqlstatement:=SqlStmt
End With

where SQLStmt = "Select * from [Query]"

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

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