[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?


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

"Sarah G" wrote in message
...
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.

[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" wrote:

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?


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

"Sarah G" wrote in message
...
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]

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:MailMergeMainDocTypeFormLetters/w:MailMergeMainDocType
w:MailMergeLinkToQuery/
w:MailMergeDefaultSQL/
w:MailMergeQueryStringSELECT * 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:SrcC:\...\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" wrote in message
news
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" wrote:

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?


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

"Sarah G" wrote in message
...
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.

[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:MailMergeMainDocTypeMailingLabels/w:MailMergeMainDocType
w:MailMergeLinkToQuery/
w:MailMergeDataTypeODSO/w:MailMergeDataType

w:MailMergeConnectStringProvider=Microsoft.Jet.O LEDB.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:MailMergeQueryStringSELECT * FROM `qryCEDS` /w:MailMergeQueryString
w:MailMergeDataSource HRef="P:\MastMail\dbBiState
Mastermail.mdb"/w:MailMergeDataSource
w:Odso
w:UdlProvider=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:TableqryCEDS/w:Table
w:SrcP:\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.
--
Peter Jamieson
http://tips.pjmsn.me.uk

"Sarah G" wrote in message
news
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:MailMergeMainDocTypeMailingLabels/w:MailMergeMainDocType
w:MailMergeLinkToQuery/
w:MailMergeDataTypeODSO/w:MailMergeDataType

w:MailMergeConnectStringProvider=Microsoft.Jet.O LEDB.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:MailMergeQueryStringSELECT * FROM `qryCEDS` /w:MailMergeQueryString
w:MailMergeDataSource HRef="P:\MastMail\dbBiState
Mastermail.mdb"/w:MailMergeDataSource
w:Odso
w:UdlProvider=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:TableqryCEDS/w:Table
w:SrcP:\MastMail\dbBiState Mastermail.mdb/w:Src
w:FieldMapData

[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" wrote:

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.
--
Peter Jamieson
http://tips.pjmsn.me.uk

"Sarah G" wrote in message
news
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:MailMergeMainDocTypeMailingLabels/w:MailMergeMainDocType
w:MailMergeLinkToQuery/
w:MailMergeDataTypeODSO/w:MailMergeDataType

w:MailMergeConnectStringProvider=Microsoft.Jet.O LEDB.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:MailMergeQueryStringSELECT * FROM `qryCEDS` /w:MailMergeQueryString
w:MailMergeDataSource HRef="P:\MastMail\dbBiState
Mastermail.mdb"/w:MailMergeDataSource
w:Odso
w:UdlProvider=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:TableqryCEDS/w:Table
w:SrcP:\MastMail\dbBiState Mastermail.mdb/w:Src
w:FieldMapData

[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.

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

"David L" wrote in message
...
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" wrote:

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.
--
Peter Jamieson
http://tips.pjmsn.me.uk

"Sarah G" wrote in message
news
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:MailMergeMainDocTypeMailingLabels/w:MailMergeMainDocType
w:MailMergeLinkToQuery/
w:MailMergeDataTypeODSO/w:MailMergeDataType

w:MailMergeConnectStringProvider=Microsoft.Jet.O LEDB.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:MailMergeQueryStringSELECT * FROM `qryCEDS`
/w:MailMergeQueryString
w:MailMergeDataSource HRef="P:\MastMail\dbBiState
Mastermail.mdb"/w:MailMergeDataSource
w:Odso
w:UdlProvider=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:TableqryCEDS/w:Table
w:SrcP:\MastMail\dbBiState Mastermail.mdb/w:Src
w:FieldMapData

[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
"Sharon L." Sharon wrote in message
...
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.

[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).

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

"Sharon L." Sharon wrote in message
...
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.

[Sharon L.[_2_]]

Thank you for so much your response Peter. Changing the Word setting allowed
the DDE connection to be selected so the queries worked just like they did in
Word 2000. (The other part that you mentioned isn't practical as there are
many different databases, tables and word documents used in the merges so
there is too much work involved in trying to fix it all). That alone solved
the problem but brought up another issue.

The situation is that some staff are using Word 2000, others are using Word
2003 and I am now starting to test Word 2007. If someone creates a new mail
merge and uses ODBC, what happens to those who try to run it in Word 2000?
Should they stick to DDE instead? People are using these database queries and
merges to get work done and really won't want to spend all their time redoing
everyhing that used to work properly.

Normally we upgrade computers and software for 1/3 of the staff each year.
This is why we are using different versions. We are still using Access 97
because the format change in Access 2000 and 2002 didn't work properly. (VBA
code didn't run etc).

Are there any resource materials that you know of that discuss these issues
and suggest an approach to upgrading the different versions of Office? For
example, If we all went to Office 2007 including Word and Access at the same
time, is there an easy way to keep what used to work in Access 97 and Word
2000 working without an incredible amount of reconstruction?