Migrating Problem Word 2000 to Word 2003

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

root - 23 Jun 2004 08:43 GMT

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

Reply to this Message

Doug Robbins - Word MVP - 23 Jun 2004 10:23 GMT

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

Signature

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

> Hi all,
>
[quoted text clipped - 9 lines]
> Any answer appreciated
> Günter

Reply to this Message

root - 23 Jun 2004 12:06 GMT

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:
>
[quoted text clipped - 12 lines]
> SQLStatement:="SELECT * FROM Customers"
> End With

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

> This is the example from the vba help file:
>
[quoted text clipped - 12 lines]
> SQLStatement:="SELECT * FROM Customers"
> End With

Reply to this Message

Cindy M -WordMVP- - 23 Jun 2004 15:32 GMT

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 :-)

Reply to this Message

G?nter Brandst?tter - 23 Jun 2004 19:51 GMT

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-" <C.Meister-C@hispeed.ch> schrieb im Newsbeitrag
news:VA.00009c07.016b6924@speedy...

> Hi Root,
>
[quoted text clipped - 23 lines]
> This reply is posted in the Newsgroup; please post any follow question or
> reply in the newsgroup and not by e-mail :-)

Reply to this Message

Cindy M -WordMVP- - 24 Jun 2004 10:34 GMT

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 :-)

Reply to this Message

G?nter Brandst?tter - 29 Jun 2004 17:27 GMT

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-" <C.Meister-C@hispeed.ch> schrieb im Newsbeitrag
news:VA.00009c10.004b3115@speedy...

> Hi G?nter,
>
[quoted text clipped - 12 lines]
> This reply is posted in the Newsgroup; please post any follow question or
> reply in the newsgroup and not by e-mail :-)

Reply to this Message

Cindy M -WordMVP- - 30 Jun 2004 13:12 GMT

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 :-)

Reply to this Message

G?nter Brandst?tter - 02 Jul 2004 10:34 GMT

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

"Cindy M -WordMVP-" <C.Meister-C@hispeed.ch> schrieb im Newsbeitrag
news:VA.00009c98.00d2a942@speedy...

> Hi G?nter,
>
[quoted text clipped - 15 lines]
> This reply is posted in the Newsgroup; please post any follow question or
> reply in the newsgroup and not by e-mail :-)

Reply to this Message

G?nter Brandst?tter - 02 Jul 2004 11:05 GMT

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 OLEDB:Database 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-" <C.Meister-C@hispeed.ch> schrieb im Newsbeitrag
news:VA.00009c98.00d2a942@speedy...

> Hi G?nter,
>
[quoted text clipped - 15 lines]
> This reply is posted in the Newsgroup; please post any follow question or
> reply in the newsgroup and not by e-mail :-)

Reply to this Message

Cindy M -WordMVP- - 05 Jul 2004 10:04 GMT

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 :-)

Reply to this Message