Migrating Problem Word 2000 to Word 2003

[root]

Hi all,

I have a serious problem migrating an Access-application I wrote form
Access 2000 to AccessXP. I had integrated a mailmerge fuction in my
database which opened a WORD object and did all the merging stuff in it.
Since I installed OfficeXP, my mailmerge wont work anymore.
I used code like objDoc.MailMerge.OpenDataSource xxxxxxxx, but this will
open a window asking to select a table in the new Version. Microsoft seems
to have changed their access to the MailMerge-DataSource completely.
Can anyone tell me what to do, how to migrate correctly??

Any answer appreciated
Günter

 

[Doug Robbins - Word MVP]

This is the example from the vba help file:

This example uses ODBC to attach the Microsoft Access database named
"Northwind.mdb" to the active document. The SQLStatement argument selects
the records in the Customers table.
Dim strConnection As String

With ActiveDocument.MailMerge
.MainDocumentType = wdFormLetters
strConnection = "DSN=MS Access Databases;" _
& "DBQ=C:\Northwind.mdb;" _
& "FIL=RedISAM;"
.OpenDataSource Name:="C:\NorthWind.mdb", _
Connection:=strConnection, _
SQLStatement:="SELECT * FROM Customers"
End With

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[root]

Hi Doug,
thank you for your prompt answer.
I tried this way also, but I have the Problem that my SQLStatement is too
long. It exceeds the 256 characters.
When I try to use .OpenDataSource Name:="C:\NorthWind.mdb", _
Connection:="QUERY MyQuery"
it worked in WORD 2000 but does not work in WORD 2003. In my query, I use
a procedure to concatenate a string, and that is why this query does not
appear in the "Tables/Views" list of the WORD 2003 MailMerge assistant.

Thanks again
Günter



Am Wed, 23 Jun 2004 19:23:16 +1000 schrieb Doug Robbins - Word MVP:

This is the example from the vba help file:

This example uses ODBC to attach the Microsoft Access database named
"Northwind.mdb" to the active document. The SQLStatement argument selects
the records in the Customers table.
Dim strConnection As String

With ActiveDocument.MailMerge
.MainDocumentType = wdFormLetters
strConnection = "DSN=MS Access Databases;" _
& "DBQ=C:\Northwind.mdb;" _
& "FIL=RedISAM;"
.OpenDataSource Name:="C:\NorthWind.mdb", _
Connection:=strConnection, _
SQLStatement:="SELECT * FROM Customers"
End With

Am Wed, 23 Jun 2004 19:23:16 +1000 schrieb Doug Robbins - Word MVP:

 

[Cindy M -WordMVP-]

Hi Root,

When I try to use .OpenDataSource Name:="C:\NorthWind.mdb", _
Connection:="QUERY MyQuery"
it worked in WORD 2000 but does not work in WORD 2003. In my query, I use
a procedure to concatenate a string, and that is why this query does not
appear in the "Tables/Views" list of the WORD 2003 MailMerge assistant.

Right, user-defined functions can only be used in queries for mail merge if
the query is run in the Access interface. This means a DDE connection.

By default, Word 2002/2003 use the new OLE DB connection, which only links
to the data, and not the interface.

you basically have two choices:
- connect using DDE, as before (see the Word 2002 section of my
website's mail merge FAQ, or read a number of posts here)
- turn your query into a Make Table type of query, link the mail merge
to that, and run the query before using the merge

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
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

 

[Günter Brandstätter]

Thank you Cindy,

I was thinking of your second solution also, but what will I have to do when
I want to connect by code? Does my coding rest the same except the
Connection:="QUERY xxxxxx"??

best regards
Günter

 

[Cindy M -WordMVP-]

Hi Günter,

I was thinking of your second solution also, but what will I have to do when
I want to connect by code? Does my coding rest the same except the
Connection:="QUERY xxxxxx"??

Probably (varies from version to version). Best is to record a macro in Word
while connecting to the data source and see what (if anything) changes.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
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

 

[Günter Brandstätter]

Hi Cindy,

took me a time to do what you proposed.
Macro works fine, but in Word only. When I use the same code in Access, it
does not ask me for a table or view now, but it asks me what kind of
connection I want (ODBC, DDE).
What else could I do wrong in this case??

thanks in advance
Günter

 

[Cindy M -WordMVP-]

Hi Günter,

took me a time to do what you proposed.
Macro works fine, but in Word only. When I use the same code in Access, it
does not ask me for a table or view now, but it asks me what kind of
connection I want (ODBC, DDE).
What else could I do wrong in this case??

Please show me the OpenDataSource code, as well as the code used to "talk"
to the Word application (this kind of thing: Dim wd as Word.Application, set
wd = ) and mail merge document.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
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

 

[Günter Brandstätter]

Thank you Cindy,

I resolved it. I've been too stupid. In the OpenDataSource statement I left
SQLStatement:="SELECT * FROM `QUERY MyQuery`" insterad of coding
SQLStatement:="SELECT * FROM `MyQuery`" .

thanks again for helping me out
Günter

 

[Günter Brandstätter]

Hi Cindy,
there is another thing I needed to know. When I use a query that contains a
function, my code won't work. Is there another solution except creating a
new table and then getting the data form this newly created table??

How should this code be in Office97, I have two versions of Office installed
on my machine, and I'm writing code for Office2003 and Office97, but when I
run a code in Access97 it always launches Word97.

I use the code I gereated using a macro
objDoc.MailMerge.OpenDataSource Name:=WhereIsDB _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="",
_
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False,
_
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=" & WhereIsDB & ";Mode=Read;Extended Properties="""";Jet OLEDB:System
database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase P" _
, SQLStatement:="SELECT * FROM `Antwort noch nicht gedruckt alt`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess

what do I have to use in "Provider:=" for the Office97??

thank you in advance
Günter

 

[Cindy M -WordMVP-]

Hi Günter,

there is another thing I needed to know. When I use a query that contains a
function, my code won't work. Is there another solution except creating a
new table and then getting the data form this newly created table??

You can only link to queries containing user-defined functions using a DDE
connection. The code you show at the bottom of your last message indicates an
OLE DB connection.

And OLE DB connections were introduced in Word2002; won't work in earlier
versions.

You have to go back to the macro-recording drawing board. Activate "Confirm
conversions on open" in Tools/Options/General. Start the macro recorder. Link
to the data source *.mdb and select DDE this time.

How should this code be in Office97, I have two versions of Office installed
on my machine, and I'm writing code for Office2003 and Office97, but when I
run a code in Access97 it always launches Word97.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
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