Mailmerge to access + linked tables to SQL + Office 2003

[P. Scaram]

I have a question to ask you about the mailmerge in Word2003.
I am supporting an application originally developed in Access 2000 with
ability to mailmerge a query to a word document. The access application uses
queries to tables linked to an SQLServer database. I opened the word template
with VB with predefined data source the query and everything worked fine.
Wit office 2003 this would not work.
I tried to reset the data source in 2003 for the word template and I got the
following problems.
I used the data source connection MSACCESS, I found the query but it could
not identify the columns. It was returning a message too few columns and it
gave the columns M , M-1. The tables are linked with the ODBC to SQL Server
and the query is defined in Access.
I also tried to link to the access file directly , but it only gave me on
the table list a few tables from the many defined in Access. It was not clear
what were the criteria for eliminating the rest.

I am sure there must be a simple solution to that since all the more complex
code written in access2000 did not present any problems in 2003.

If anybody can help I would be obliged.

Thank you
P.Scaram

 

[Peter Jamieson]

In this case try the following:
a. check Word Tools|Options|General|"Confirm conversions at open"
b. Go through the process of connecting to your Access database again. You
should see an additional dialog box which lets you choose a "connection
method" - probably either OLE DB, Access via ODBC, Access via DDE.
c. Select the Access via DDE option. (Or, if you were using ODBC before,
you can try the ODBC option).

The DDE option was the default connection method in Word 2000. It works the
same way Word 2003. OLE DB is the default option in Word 2003 and does not
see all the queries etc. in an Access table. (In particular, it does not
"see" parameter queries, some types of linked tables, and so on)

If you still have problems, you may have to consider creating the Views you
need in SQL Server and connecting directly to them.

Peter Jamieson

 

[P. Scaram]

Thank you for your answer. I tried it and connecting to the access database
when I used the DDE connection it returned an error "Could not establish a
DDE connection to Microsoft Access to complete the current task". Is there
something missing from the installation that would correct this problem.?

The only way to see some data was to connect directly to the SQL SERVER.
This is not entirely satsfactory because I use the currentuser() function
from access to filter data.
There was also another problem. I used a word template .dot where with
Word2000 I would save the data source and the connection would happen take
place automatically when the template opened.
Now I open the datasource, I save the template but when i open it again I
need to open the datasource again.

Sorry for the trouble and hope you will be able to help again.

Philip

 

[Peter Jamieson]

Sorry for the delayed reply.

Thank you for your answer. I tried it and connecting to the access
database
when I used the DDE connection it returned an error "Could not establish a
DDE connection to Microsoft Access to complete the current task". Is there
something missing from the installation that would correct this problem.?

This is not entirely satsfactory because I use the currentuser() function
from access to filter data.

This suggests that your Access database is secured using workgroup security.
(Otherwise, you would typically see "Admin" as the currentuser). If so, what
is probably happening is that if Access (and your database) are not already
open, Word has to try to start Access, then open the database. When it does
so, Access will display the login dialog. Unfortunately, these dialogs (and
parameter dialogs) do not necessarily appear in front of Word. You don't see
them, and Word "times out". You can try using alt-tab to cycle through the
windows then fill in the security login dialog.

Otherwise, try starting Access and opening the database before you open the
Word Mail Merge Main Document.

If you still cannot make a connection, can you please try creatying a local
table in Access and connecting a mail merge main document to it, using DDE?
If that works, then the problem is /likely/ to be that Word 2003 is having
difficulty connecting to queries that reference linked tables. I'm not sure
what can be done in that case.

The only way to see some data was to connect directly to the SQL SERVER.
This is not entirely satsfactory because I use the currentuser() function
from access to filter data.

Let's hope no workaround is needed for this. As far as I am aware,
currentuser() will return the username supplied to Access's login dialog. If
you are using SQL SERVER< the only user name you are likely to be able to
access is the logged in SQL Server username (which would be the Windows
domain username if you are using SQL Server integrated secuirty) and you may
or may not have arranged that the Access username is the same.

There was also another problem. I used a word template .dot where with
Word2000 I would save the data source and the connection would happen take
place automatically when the template opened.
Now I open the datasource, I save the template but when i open it again I
need to open the datasource again.

My guess is that you need to take account of the following article:

http://support.microsoft.com/kb/825765

 

[P. Scaram]

I do not know if you got my reply, I cannot see it anyway.

I wanted to thank you for your detailed help.

The kb article solved the problem for doing a mailmerge from a vb script.

The DDE connection was OK in the end, the PC I was doing the tests had the
peculiarity of having Access2000 with Word 2003 installed. I had not realised
that.

Thanks again

Philip