Data Source Lost When Merge From Access to Word

[Sarah G]

I have an Access database that I want to use to merge data into Word
documents that I have already set up. The database and the files are located
in the same file on a server that others can access. The merges have been
working fine for me, since I created them, but they do not work the same way
for others. When they open a Word merge file and select Yes to run the
corresponding SQL command, they get another message saying the database
connection was lost. Then, they have to go through a whole set of screens to
re-establish the connection. And then, sometimes when they click the Mail
Merge Recipients button, an error message appers that says there is no
corresponding data or the connection was lost. I don't see any of this, and
I don't know how to fix it.

 

[Peter Jamieson]

I replied to your earlier question a couple of days ago but I guess you
didn't see the answer.

Which version of Word?

Are all the users using the same path to access the documents, or are some
of them using (say) a network path mapped to a drive letter?

 

[Sarah G]

Sorry. I haven't been able to see my post. We are all using Word 2002, and
we are all accessing the merge files and database through a network drive P:\
None of the files have changed, renamed, or moved from that drive.

 

[Peter Jamieson]

Can you do the following on
a. your PC
b. one of the other PCs

1. Open the mail merge main document. Go through any steps necessary to
connect to the data source
2. Use "Save As" and select Save As Type "Web Page".
3. Close the document
4. locate the .htm file you just saved and open it in Notepad
5. Look down the file until you see a block of info. that looks like it
contains the info. related to the merge (in WOrd 2003 it currently looks a
bit like the following. In Word 2002 I think the names etc. are rather
different, but it should stil lbe reasonably obvious which bit describes the
data source):

<w:MailMergeMainDocType>FormLetters</w:MailMergeMainDocType>
<w:MailMergeLinkToQuery/>
<w:MailMergeDefaultSQL/>
<w:MailMergeQueryString>SELECT * FROM C:\Documents and Settings\PeterJ\My
Documents\My Data Sources\Kt.doc</w:MailMergeQueryString>
<w:MailMergeDataSource
HRef="C:\Documents and Settings\PeterJ\My Documents\My Data
Sources\Kt.doc"></w:MailMergeDataSource>
<w:Odso>
<w:Table>:\Documents and Settings\PeterJ\My Documents\My Data
Sources\Kt.do</w:Table>
<w:Src>C:\...\Kt.doc</w:Src>

6. Copy paste that chunk of HTML into a message in thsi group. You should
only need to copy/paste (say) 10 lines maximum.

Peter Jamieson
http://tips.pjmsn.me.uk

 

[Sarah G]

The following is the code you asked for from my login account. I was unable
to save a web page from the other person's account as it created an error. I
think the problem is with security settings in the database itself, although
I don't know why that should effect merging data.

<w:MailMergeMainDocType>MailingLabels</w:MailMergeMainDocType>
<w:MailMergeLinkToQuery/>
<w:MailMergeDataType>ODSO</w:MailMergeDataType>

<w:MailMergeConnectString>Provider=Microsoft.Jet.OLEDB.4.0;Password="";User
ID=Admin;Data Source=P:\MastMail\dbBiState Mastermail.mdb;Mode=Read;Extended
Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet
OLEDBatabase Password="";Jet OLEDB:Engine Type=5;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</w:MailMergeConnectString>
<w:MailMergeQueryString>SELECT * FROM `qryCEDS` </w:MailMergeQueryString>
<w:MailMergeDataSource HRef="P:\MastMail\dbBiState
Mastermail.mdb"></w:MailMergeDataSource>
<w:Odso>
<w:Udl>Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data
Source=P:\MastMail\dbBiState Mastermail.mdb;Mode=Read;Extended
Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet
OLEDBatabase Password="";Jet OLEDB:Engine Type=5;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</w:Udl>
<w:Table>qryCEDS</w:Table>
<w:Src>P:\MastMail\dbBiState Mastermail.mdb</w:Src>
<w:FieldMapData>

 

[Peter Jamieson]

OK, I was obviously hoping to compare the two, but there is certainly
nothing unexpected in there.

I
think the problem is with security settings in the database itself,
although
I don't know why that should effect merging data.

That could well be the case> The thing that doesn't quite add up is if they
are managing to make the connection /eventually/. If it is a
security-related problem then you have to consider
a. what permissions people have to access the folder containing the .mdb
and and .mdw you may have
b. what permissions people have to access the share that P: is mapped to
c. whether you have an Access database password or workgroup level security
(with a .mdw) and what permissions the users have for the objects in the
database that the query uses
d. if you are using linked tables, what permissions the users have to
access the underlying tables.

Not sure if that gives you anything that can help you get any further, but
perhaps some of the possibilities can be eliminated.

 

[Sarah G]

Thanks for your help! I suspected that it was a security issue. I'll have to
go back and look at the database itself.

 

[David L]

Hello Peter and Sarah,

I am having similar problems with mail merge to word from an access database
located on a server, but the security settings haven't been created yet.
Everyone on the network is running XP, Word 2003 and Access 2003. The
linkage between word and access used DDE because the merged data comes fom
queries, not tables. After the user generates the query, they press a button
on the form with a hyperlink to the pre-linked word file. Word opens up but
then tries to open Access again on its own, and can't find the link it was
saved with.

The crazy part is that everything worked fine yesterday. I am wondering if
the problem is created by security updates that installed automatically
during shutdown yesterday.

I welcome your suggestions.

Sincere regards,

David

 

[Peter Jamieson]

Can you still connect manually using DDE in a simpler scenario where you
aren't initiating the thing from Access? FWIW I probably get much the same
updates as you and I can still do that...

FWIW with normal merges the fact that the data is coming from a query is not
in itself a reason why you have to connect using DDE. If the query is a
parameter query (doesn't sound like it) or uses old-style wildcards, or
several other things, you might have to use DDE. I just wonder whether you
should try an OLE DB connection.

 

[Sharon L.]

I am also using an Access 97 database as the data source for a Word mail
merge. The data originally comes from SQL tables using ODBC links. This
currently works fine when I use Word 2000 but when I use Word 2003 I get
"Word was unable to open the data source" when I try to connect to a
database query. It does seem to work if I create an Access temp table from my
queries. I have hundreds of different queries in different Access 97
databases linked to Word (2000) documents. I don't see any option to select a
DDE link instead of ODBC.

 

[Microsoft Public Groups]

To see the option you have to check Word Tools|Options|General|Confirm
conversions at open. Then you get the same "Confirm" dialog after you select
the .mdb.

Peter Jamieson

 

[Peter Jamieson]

In Word 97/2000, DDE was the default connection method and ODBC was the
alternative as far as Access/Jet sources were concerned.

In Word 2003, OLE DB is the default, but unfortunately when you have
selected your database, Word does not display the Access tables that are
linked to ODBC data sources (which is what you would have even if your
Access database is Access 2000 format or later). It isn't that OLE DB cannot
"see" the tables: it can, but Word seems to ignore them.

So what can you do? Well, assuming there are no additional problems related
to the fact that it's an Access 97 format database rather than (say) Access
2000 format, you can
a. check Word Tools|Options|General|Confirm conversions at open, go through
the connection process again, and select either a DDE or ODBC connection
from the additional dialog box that's displayed. If you chose ODBC, you will
need to check that Word has selected the correct .mdb (it's impossible to
see if the pathname is long), click Options... then select all the boxes.
You should see the list of linked tables. Or
b. create one query in your Access database for each linked table you want
to use, with SELECT * FROM [thattablename], and use that as the data source
(which I think you are probably already doing). Or
c. connect using Word VBA and the OpenDataSource method. All you really
need in this case is

Sub ConnectToAccess()
ActiveDocument.MailMerge.OpenDataSource _
Name:="the full path name of your .mdb", _
SQLStatement:= "SELECT * FROM [theAccesstablename]"
End Sub

or
d. create a .odc file for each linked table and use those when you select
your data source. (in Word's "Select Data Source" dialog box
- click New Source
- Other/Advanced
- select the Microsoft Jet 4.0 OLE DB Provider. Click "Next"
- enter the full path name of the .mdb. Click Yes (test the connection
if you want)
- you should then see a list of tables. Select the one you want, then
name and save the .odc file
- Word then prompts for a data source - select the .odc you just
created, and select the OLE DB Databases connection method if word prompts
you for that.

or
e. connect directly to your SQL database (typically you also have to create
a .odc for that cf. point (d) above, but using the appropriate provider
instead of the Microsoft Jet one.

The different ways of connecting have their advantages and disadvatages so
find out whether there are any importnat things you cannot do (e.g. you
might not be able to sort/filter or edit data source records in Word, and so
on).