[Peter Jamieson]

OK, glad we seem to have got somewhere useful.

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

"KMikaela" wrote in message
...
Dear Peter,

So far (knock wood) this has been very successful. What I did was save
the
data connection file created to the database folder and created a dummy
user
so all users could open templates with out the password issue. This
connection is much faster and seems far more stable. Thank you so much
for
your help, I appreciate it greatly as do the many users.

"KMikaela" wrote:

Dear Peter,

Thank you so much for your time and expertise. I will try this process
today and let you know how it goes.

Mikaela

"Peter Jamieson" wrote:

It is amazing that so much has
changed from 2000 to 2003, as the workgroup was not problem, nor was
the
number of records when we were using 2000.

Yes, I can only guess but suspect that when the developers tried to
incorporate OLE DB, they probably found themselves with a lot more
problems
than they bargained for and probably too little time to design a better
way
through them. The vast majority of the changes occurred between Word
2000
and Word 2002, BTW.

For an OLE DB data source you really need a .udl or .odc file rather
than a
DSN (which are for ODBC data sources). You don't actually have to have
either, as long as your users do not need to set up their data sources
from
scratch (and you may find that they have to) - in that case, using a
.odc
can make things rather easier for them. .udl s can also be used but
cannot
embed a table or query name.

Whether either ODBC or OLE DB can "see" your query depends on the type
of
query. For OLE DB, I have verified that a simple TOP 10 query that
references a single local table in a .mdb is visible to OLE DB, but if
your
query has other complications it might well be "invisible". You
obviously
need to ensure that whatever Access login you use has all the necessary
permissions to run the query successfully.

If you want to try to connect with ODBC, you usually have to ensure
that
a. the database name is correctly selected when you see the
tables/queries
dialog box. It can be hard to tell because you can't inspect long file
names
within the database name text box.
b. you have clicked the "options" button and selected all the options
in
there

Both OLE DB and ODBC connections from Word can also suffer from another
problem - if the pathnames of the access database and the workgroup
security
database (mdw) are too long, you may have problems when you try to
connect,
or more likely, when you try to re-open the mail merge main document.
This
is because Word can only save a 255/256-character connection string,
and the
way Word works with OLEDB, there is no way to keep that string short by
specifying only the settings you need either in VBA or a .odc- Word
always
adds in a bunch of standard settings as well. If you encounter that
problem
then I suspect that the only way through it is either to ensure that
your
..mdb and .mdw are located at short path names or perhaps to create a
further
..mdb which (e) has a short path name, (d) uses the same .mdw and (e)
links
to your first .mdb.

Anyway, let's step through the basic approach needed to make the
connection
in OLE DB /without/ setting up a .odc:

a. ensure Word Tools|Options|General|Confirm conversions at open is
checked
b. start the process of connecting to a data source
c. when the Select Data Source dialog opens, select the .mdb and click
Open. A Confirm Data Source dialog box should display - probably
showing
OLE DB, ODBC and DDE options.
d. Select OLE DB Database files and click OK A Data Link Properties
dialog
box should display, with the COnnection tab open.
e. (The first time you do this, just so you fully understand what's
going
on, click the Provider tab and ensure that "Microsoft Jet 4.0 OLE DB
Provider" is selected. Then click the Connection tab again
f. enter the name of your .mdb or select it using the ... button
g. enter the user name
h. uncheck blank password and enter the Password.
i. (If you click Test connection at this point, the connection will
probably fail. If it does not, it suggests that someone has configured
some
Jet registry properties so that the workgroup security database for
your
database is the default "system database". However, for the purposes of
this
experiment, you should probably enter the database name as shown in the
next
few steps)
j. click the All tab and double-click on the property named "Jet
OLEDB:System database". Enter the full path name of your workgroup
security
file. Select the pathname and use ctrl-C to copy it to the clipboard.
k. go back to the Connection tab and click the Test connection button.
If
you don't see "Test connection succeeded" then please let us know what
message you do see (and I may not be able to get you any further)
l. Click OK. For the benefit of anyone else reading this, this is the
point
where it is easy to get into difficulties and which I have never
understood
very well before. What happens is that Word, or OLE DB (not sure which)
tries to retrieve the list of tables and queries in the specified
database.
However, what is potentially very confusing is that if the database
only
contains a single visible table, the process selects that table without
tellng you, and the dialog box redisplays, but with your login info
wiped
out. What's more, filling in your login info again doesn't help. But if
that
is what happens, skip to step (). Otherwise, if there is more than one
visible table/query, you should see a Select Table dialog box.
m. If the query you need to connect to is not listed, we may not be
able to
use this approach. But for now it may be worth completing this process
and
selecting one that is listed. Click OK
n. The Data Link Properties dialog box is redisplayed. However, most
of the
stuff we entered before has now been tossed away - i.e. it was only
there to
allow us to select a specific database and table.
o. fill in the correct user name again, uncheck blank password again
and
fill in the password again
p. click the All tab and double-click on the property named "Jet
OLEDB:System database" again. Use ctrl-v to insert the full path name
of
your workgroup security file that you copied to the clipboard in step
(j).
Or re-enter the pathname the hard way...
q. click Test connection again if you like. Let's hope you see
"Success"
r. You now have one final choice to make. You can either check "Allow
saving password" or leave it unchecked. Either way, when you check OK,
Word
should connect tot he data source. However, when you save and close and
re-open the mail merge main document, if you /do/ check this box then
the
login info. will be stored in the .doc and the user should not see the
Data
Link Properties dialog box. If you do not check the box, the
information is
not stored and the user wil be faced with the data link properties
dialog
when they re-open the mail merge main document. However, at that stage,
they
should only have to enter the user name, uncheck blank password, and
enter
their password - all the other info (provider name, database name,
security
database name) should have been retained. But I think you need to
verify
this on your own system before subjecting users to it. Also, since it
appears that the user could easily check thiis box when they open the
document and then save the document and transmit it, there may be
further
security implications.
s. Click OK. If all has gone well, you should be connected to the data
source and be able to proceed inthe usual way.

Phew!

If you got that far, well done! If you still couldn't see the query in
step
(m), it is still just about possible that the same thing can be done
using a
..odc or VBA (i.e. I have sometimes discovered that even though the
query/table is not listed, you can still connect to it). But maybe you
could
post the query code here?

If you want or need to create a .odc, let's leave that for another
time.

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

"KMikaela" wrote in message
...
Dear Peter,

Yes the security thing is why I haven't used OLE DB as I wasn't
seeing the
query when I used this, despite setting up a DSN. It walks through
the
process, and sees the database, and then in the end does not connect,
and
doesn't give an error.

We are using a workgroup file, and not a user password, and opening
the
database does bring up the user log in. It is amazing that so much
has
changed from 2000 to 2003, as the workgroup was not problem, nor was
the
number of records when we were using 2000. All other features work,
although
there have been some issues with 2000 users, as we have had a partial
roll
out of 2003. In testing, all worked well, I believe it was because
the
data
was static.

"Peter Jamieson" wrote:

sorry for adding the other threads, wasn't sure of
the process.

No problem - people sometimes do it when they don't get a reply, but
sometimes that can be because the original responder is away for a
while,
as
in this case.

Yes this is still a DDE

OK, I don't think the situation has changed for DDE from the one I
described
in my earlier message. i.e. what you probably have to do to avoid
all the
problems is both the "TOP 1000" /and/ change to OLE DB. I suspect in
your
case the problem then is that you are using a secure database and in
that
case there is an additional problem because you may have to use a
.odc
file
to get Word to connect to the database, and/or end up embedding the
necessary security informaiton somewhere inside the Word document.

But can you confirm/deny that the secure database thing is the
reason why
you haven't moved to OLE DB? If not, what is? Are you using
workgroup
security rather than a database password?
--
Peter Jamieson
http://tips.pjmsn.me.uk

"KMikaela" wrote in message
news Yes this is still a DDE, sorry for adding the other threads,
wasn't
sure
of
the process.

Mikaela

"Peter Jamieson" wrote:

Is this still DDE (as your other messages suggest) or is it OLE
DB
now?

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

"KMikaela" wrote in message
...
Dear Peter,

It seems the "Find Entry" button on the Mail merge tool bar
only
works
after
you open the "mail merge recipeients" Is this because the
items in
the
data
source have changed and it is a query? Is there a way to set
this
to
do
automatically or are users going to have to refresh this each
time
they
enter
a template? I have well over 200 templates in use, and I am at
a
loss
for
why there are so many issues with mail merge in Word 2003.

Many thanks,

Mikaela

"KMikaela" wrote:

Hi Peter,

Have you tried defining an Access query that selects the
necessary
1000
records and using that as the data source?

This is exactly what I'm doing, so it is working well.

Thank you.

"Peter Jamieson" wrote:

Hello Mikaela

Also, I have been
using the Find record button on the Mail Merge tool bar,
sorry
for
not
being
clearer.

FWIW It is not your fault or my fault that it does not work,
and
that
this
kind of predefined limit is not specified.


as I had tried the OLE
DB connection and it doesn't see the table in Access at
all So
I
will
just
need to filter all tables as we only search the
most recently entered records.

Have you tried defining an Access query that selects the
necessary
1000
records and using that as the data source?

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

"KMikaela" wrote in
message
...
Dear Peter,

Thank you so much for your detailed and quick reply. I
had
thought
it
might
be the number of records in Word 2003 but had not found
any
data
to
indicate
the total capacity of records (they really should write
that
down
some
where
- but I gues most people don't use mail merge for more
than
10,000
records),
although it worked beautifully in Word 2000, ah well.
Also, I
have
been
using the Find record button on the Mail Merge tool bar,
sorry
for
not
being