Loosing access to data source in Word 2003

[Franz Weghaus]

Hello!
I try to create a mailmerge file. So I create a connection to the data
source (xls or txt). I can save the file and everything seens to be OK.
So far so good. But if I reopen the merge file, the connection is lost.
This only happens if the data source is located in a network drive. I
tried to solve the problem described here

http://support.microsoft.com/kb/834699/en-us

(Method 1) but it didn't work. So are there other possibilities? Thanks.
Greets
Franz
p.s.: Sorry for my bad english.

 

[Peter Jamieson]

Could it be this one?

http://support.microsoft.com/kb/885832/en-us

I do not know if the above article solves the problem I am about to describe
or not:
a. By default, Word connects to several types of data source (including
..xls and .txt) using OLE DB
b. To do that, it constructs an OLE DB connection string that includes the
full path name of the "database", which is either a file name (e.g. if it is
a .mdb) or a folder name (e.g. if it is a .txt file). The connection string
also contains a lot of other stuff that you cannot eliminate.
c. When it saves the connect string, it truncates it to 255 characters.
d. If the pathname in the connect string is too long, the connection string
may be truncated in the middle of the pathname. In that case, Word will be
unable to connect to the data source when it re-opens.

 

[Franz Weghaus]

Hello Peter,

this article didn't solve our problem. Do you have another idea? Thanks.

Greets
Franz Weghaus

 

[Peter Jamieson]

Do you have another idea? Thanks.

Not really, but...

1. Can you open your mail merge main document, connect to your data source,
and
a. use VBE to display the values of
ActiveDocument.MailMerge.Datasource.Name
ActiveDocument.MailMerge.Datasource.Connectstring
ActiveDocument.MailMerge.Datasource.Querystring

and post the values here?

2. Are you running Word via Terminal Server?

 

[Franz Weghaus]

Hello!

This is the result of the macro
****
Datacource.Name = X:\001 - Innere Verwaltung\001 -
Organisationsmanagement\003 - Presse -
Oeffentlichkeit\PresseEinladungen\Datenbank Outlook.doc
Datasource.Connectstring =
Datasource.QueryString = SELECT * FROM X:\001 - Innere Verwaltung\001 -
Organisationsmanagement\003 - Presse -
Oeffentlichkeit\PresseEinladungen\Datenbank Outlook.doc
****

Word is NOT running via Terminal Server.

Thank you for your trouble.

Franz Weghaus

 

[Peter Jamieson]

OK, so it's going wrong even with a .doc data source.

Does the user (you?) have write access to both the folder and the share that
contains the .doc? (Actually, I am not sure what permissions they need, but
it is likely that Word is trying to create temp. versions of the data files
in the "X:\001 - Innere Verwaltung\001 - Organisationsmanagement\003 -
Presse - Oeffentlichkeit\PresseEinladungen\" folder. However, I do not know
why the user would be able to connect manually but not programmatically in
that case.)

 

[Franz Weghaus]

Hello!

The connection to the data source file is made manually but it is lost
when the merge file is opened again. The user does NOT have the
permission to write in the folder where the data source is to be found.
The folder is on a Windows 2003 Server.

Today I found out an interesting symptom when I use an Excel file as
data source. If I use a DDE connection the error occurs but if I use a
OLE connection it works. Here are the results of the macro you sent me:

*****DDE*****
Datacource.Name = X:\009 - Raeuml. Planung u. Entwickl., Geoinform\006 -
Grundstueckswertermittlung\001
Grundstueckswertermittlung\Gutachterausschuss\Vordrucke\Datenbank
Outlook.xls
Datasource.ConnectString = Gesamtes Tabellenblatt
Datasource.QueryString = SELECT * FROM X:\009 - Raeuml. Planung u.
Entwickl., Geoinform\006 - Grundstueckswertermittlung\001
Grundstueckswertermittlung\Gutachterausschuss\Vordrucke\Datenbank
Outlook.xls

*****OLE*****
Datacource.Name = X:\009 - Raeuml. Planung u. Entwickl., Geoinform\006 -
Grundstueckswertermittlung\001
Grundstueckswertermittlung\Gutachterausschuss\Vordrucke\Datenbank
Outlook.xls
Datasource.ConnectString =
Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data
Source=X:\009 - Raeuml. Planung u. Entwickl., Geoinform\006 -
Grundstueckswertermittlung\001
Grundstueckswertermittlung\Gutachterausschuss\Vordrucke\Datenbank
Outlook.xls;Mode=Read;Extended Properties="HDR=YES;IMEX=1;";Jet
OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDBatabase
Password="";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk
Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create
System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't
Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;Jet OLEDB:SFP=False
Datasource.QueryString = SELECT * FROM `Tabelle1$`


So do you see a reason why the DDE connection is not available when I
open the merge file again? Thanks again!!!

Franz Weghaus

 

[Peter Jamieson]

The connection to the data source file is made manually but it is lost

when the merge file is opened again. The user does NOT have the permission
to write in the folder where the data source is to be found. The folder is
on a Windows 2003 Server.

Different versions of Word use different methods, by default, to open the
various file types. In Word 2002 and later, Word generally tries to use OLE
DB. If that fails, it may try ODBC. If that fails, it may try DDE (for Excel
and .mdb files) or it may try an internal or external file converter. For
example, if a text file has more than 255 data columns, Word cannot use OLE
DB to open it and will (probably) use its internal .txt file converter.

In Word 2000 and earlier, Word may try to open a .txt file using ODBC if it
is available, or using its internal text converter.

Every version of Word will use the internal converter to read native formats
..doc and .rtf (and perhaps the newer .xml formats).

My guess is that in most cases when Word uses an internal converter, it
wants to create and write a temp. file to the folder containing the data
source (just as it does when it opens a Word .doc in the usual way). I'm not
sure what you can do about this other than check whether or not that does
make a difference, and either change the settings for the users or create
another folder where they can create/write files.

I am pretty sure the same thing will happen if you open an Excel data source
using DDE, because in that case, Excel has to open the file and probably
does something rather similar. However, when you open using OLE DB, I don't
think the provider tries to create or write anything in the file's folder.

OK, so it still does not explain why a user can open the data source
manually but it is my best shot!

I had a look at the connection string length issue again and it appears that
this problem has now been fixed in Word 2003. I don't know when - perhaps
SP3. It isn't there in Word 2007 either. But I think it is still there in
Word 2002.

 

[jpccol]

Has anyone found a resolution of this issue. Everything was working fine
until about a year ago after running MS (Office 2003) and Windows (XP)
updates. Then the problems started:

- My server has the data (W2K Server)
- My 2 XP machines can connect the data to my mail merge forms manually, but
must re-connect each time.
- My W2K office laptop finds the data file and does not need to be manually
recoonected.
- When the merge file and data file is loaded to my standalone XP machine at
home, the data (XL) file is not disconnected after the file is closed, and
when re-opened, is found automatically

This has been a continuing problem and unfortuantely I have so many of my
forms connected to XL via DDE, it has become a significent loss of time. Any
suggestions would be greatly appreciated

Jim Calamis
feel free to email to (e-mail address removed)
Thanks