Word mail merge data source

[Markus]

I am trying to use VB using OLE to change the datasource in a Word mail merge
file to a server drive from the C: drive. When I try to make this change to
another location on the C drive, there is no problem. When I try to use the
UNC path to the server drive, I get an OLE Exception error.

Anyone have a similar problem, or have any ideas on why Word will not let me
make this change to the datasource location? Any ideas on how to get around
the problem?

Any and all ideas appreciated,
Mark

 

[Peter Jamieson]

The chances are that it's a permissions issue, probably to do with the
permission to create a file on the network drive - e.g. if your database is
a .mdb, Jet will try to create a .ldb file in the same folder as the .mdb
and will fail if you don't have the necessary permissions.

Peter Jamieson

 

[Markus]

Peter,

Yes, I also thought that, but we have tried running it with max admin
privileges.

I know newer versions of word have a security check that can cause mail
merge files to produce a message when they open regarding the running of an
sql command. Could be that affects a programmatic change as well (makes
sense) but does not return an interpretable errror. We'll try disengaging
this security check and see if that gets by the problem (or maybe someone
else has already tried this and will check in here).

Thanks,
Mark

 

[Peter Jamieson]

Yes, setting SQLSecurityCheck may do the trick, but I would also inspect
the permissions anyway.

Peter Jamieson

 

[Markus]

Peter,

You're right. It must be permissions since SQLSecurityCheck did not solve
it. But the user is logged in with full adminstrative rights. Is there some
other security/privilages I am overlooking?

Any ideas much appreciated.
Mark

 

[Peter Jamieson]

Permissions is one possibility. The other two I can think of are:
a. if you're actually working on a Windows 2003 server box (i.e. running
Word on it) then there may be a problem if the folder containing the data
source is not trusted by IE. That may be a WOrd 2002-only problem, but for
further info. see

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

b. (most likely of all I suppose) if the full path name of the file is
longer than when the file is sitting on your c:\ drive there may be
problems. When that happens, it's not because the Name parameter to your
OpenDataSource call is too long, but because for many types of data source,
Word builds a connection string (even if you don't provide one). If for
example, it's an OLE DB Jet provider connection string, it will contain
either the path name of the containing folder or the full path name of the
file you're trying to open. If the connection string exceeds 255 characters,
Word may truncate it at a point that makes the string invalid and causes the
method call to fail.

As for the pemissions issue, it's not an area I'm particularly familiar
with, but just because a user has full admin rights does not necessarily
mean that they have a right to do /everything/, e.g. create files in a
read-only folder. Nor is a full admin user's permissions necessarily a
superset of other users' permissions - for example, an admin may have no
rights to a folder created by a non-admin. They may be able to take
ownership and/or grant themselves the rights (I don't know) but that ability
wouldn't actually give them the permissions they need right now.

In this case if the folder was created by a domain admin, and you're logged
in as a domain admin when you try to connect to the data source, then I'd
say it's likely the user has the necessary permissions and the problem lies
elsewhere. But I'd still go to the machine where the relevant folder
resides, open its parent folder, right-click on the folder, open the
properties then the security tab and see what permissions the admin user
actually has, just to be sure. Alternatively, you might be able to create a
new folder on the server, assign all the necessary permissions there, put
your data source in there and try that.

From what you said, you're not using shares, but if you are, the permissions
on the share may be different from the permissions on the underlying folder.

FWIW my own experience in this area is also with a Win2003-based server. I
don't remember having problems with my shared folder on the server box
itself (but it's a long time since I set it up) but there have definitely
been permisions problems with folders on other machines in the domain.

If it isn't any of those, I don't know what the problem might be.

Peter Jamieson

 

[Markus]

Peter,

Many, many thanks for your help on this. We spent hours today tracking it
down. Ultimately, we still did not get it working using UNC. But when we
mapped the server drive to a drive letter, the datasource was able to be
changed without a problem.

I figure it could be a couple things:
- your suggestoin about the length of the path. Although, the total length
of the path, both the destination UNC and source, does not come close to 255
char. so not sure that was it.
- Any use of a UNC path probably creates the potential of additional
security issues and somehow Word, interacting with Server2003, must have seen
that as a risk and would not save the changes being made to the .doc files
when the datasource was changed programmatically. This seems possible since
when it failed on the UNC path, there were a series of .doc temp files that
were locked indicating the file was still open and unsaved.

Setting the registry security did not make any difference. It was only
using a mapped drive instead of UNC that did the trick.

Again, thanks for giving us a direction on this. My brain is still spinning
from trying to figure out a solution.

Mark

 

[Peter Jamieson]

Glad you found a solution anyway!

Just as background...

On the "connection string length" problem, the thing is that there's quite a
lot of other stuff in the connection strings that Word builds, as you will
(probably!) see if you macro record the process of selecting a data source,
or display the value of

ActiveDocument.MailMerge.DataSource.ConnectString

Unfortunately, there's no way to reduce the length of this string (as far as
I know) when the connection uses OLE DB, other than reducing the length of
the various standard values, including the database pathname. If you connect
using anything other than a .odc file, Word ignores anything you put in the
Connection parameter of the OpenDataSource method. If you use a .odc, Word
uses the values you specify in there, but reinserts the default values of
any of the standard connection properties, even if you deleted them from
your .odc.

Peter Jamieson

 

[Markus]

Peter,

Thanks again. We'll have to check the datasources to see just how long
those paths actually are. Just a relief to have it working (you know that
feeling I am sure).

Mark