Mailmerge and protected Database

[Marion]

Word 2002 Access 2002

I have no problem merging from the unprotected DB

With the protected I can select the query I need but in
the next step it says "Word was unable to open the data
source"
Has it anything to to with the length of the connection
string?

Any comments? Thanks

Marion

 

[Peter Jamieson]

Has it anything to to with the length of the connection

string?

It could do. It can be difficult to tell, because Word may try a sequence of
connection methods to get at the data (OLEDB, then ODBC, then DDE) and
presents different dialog boxes for each method.

When you select the table, do you just see a dialog box saying "Select
Table", a list of tables and queries (VIEWS, as they are described in this
dialog), and OK/Cancel? If so, the connection is via OLEDB and
a. I would normally expect it to work, because Word appears capable of
storing and using a long connection string, but
b. if you try to print the value of ActiveDocument.DataSource.ConnectString
in the VBA editor immediate pane, you will typically see Error 9121 "Buffer
for return string is too small" so you can only easily see the connection
string by saving your mail merge main document as HTML, then opening the
..htm file as a text file and looking for <w:MailMergeConnectString>

If the connection is via ODBC, you will probably see a login screen before
the table/query selection dialog. In the selection dialog you will see a
list, a database selection drop-down (which is likely to show a different
database from the one you selected), and Options, OK and Cancel buttons. If
the connection string is less than 255 characters, you will probably be OK.
If not, it will be truncated, and if the authentication information is lost
you will probably see yet another login dialog box. This is quite likely to
happen IMO if the database and the security database both have long path
names (which they typically will if they are both within a user's Documents
and Settings folders. If you can use short path names for one or bth, there
is a much better chance the string won't be truncated.

However, from what you say, I'd have to guess that there is a different
problem, which could be that you are using OLEDB and the default
authentication details (username Admin, no password, and no system database)
are still allowing access to the .mdb and the list of tables/queries, but
are not allowing access to the data in them. If you are /just/ seeing the
"Select Table" dialog I mentioned above, and are not seeing any dialog
requesting security info., this is probably what is happening.

 

[Marion]

In fact this is what happens
I moved the DB to a server and protected it
When trying to open a Word document who has to merge with
the DB it says it can not find the DB and lets me search
for it. A window opens where I can select the data source.
Once selected a window "Data Link Properties" opens with
the selected DB . I fill in a valid user name and
password. The provider is set to Microsoft Jet 4.0 OLE DB
Provider. I fill in the system database (.mdw) in "All"
(propertie Jet OLEDB: System Database). When I do a "test
connection" it works and after I pushed "OK" I can select
the query I need. However when I click "OK" there is an
error saying it can't find the database

Marion

 

[Peter Jamieson]

OK, I've just been looking at this again and remembered that I don't
actually know why OLEDB does not connect in these circumstances. What I do
know is that you should be able to set up the connection by creating a .odc
file and using that to connect. The security-related information is
/identical/ so it isn't completely clear why the connection works in these
circumstances. To create the .odc, when you get to the Select Data Source
dialog box,
a. click the New Source... button
b. Select Other/Advanced and click Next
c. Select the Microsoft Jet 4.0 OLE DB Provider then Next
d. Select the database, then fill in the appropriate authentication
settings as you describe below
e. When you save the .odc, if you check the the box that lets you save the
user/password info you should not be prompted for it again, but it will be
stored in the .odc. If you do not check that box, what should happen is that
when you use the .odc to select the data source, you should be prompted for
the authentication details again - in this case, you have to enter the
password and check the "Allow saving password" checkbox. Then the password
is saved within your Word document.
f. use the .odc you just created to select the data source.

I'm not completely convinced that that works in all circumstances but it may
solve your current problem.

 

[Marion]

Thanks Peter
I made an .odc and it works!

Marion