A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Word 2000/2002 - Proper Mail Merge steps for ODBC?



 
 
Thread Tools Display Modes
  #1  
Old September 1st, 2004, 04:21 PM
Tony_VBACoder
external usenet poster
 
Posts: n/a
Default Word 2000/2002 - Proper Mail Merge steps for ODBC?

I am new at setting up a Word document as a Mail Merge to
an Access Database. My situation involves a secured
Access 2000/2002 Database, with a Word 2000/2002 Mail
Merge Letter. My Access Databases are secured with a MDW
Security File required to open my Database. I have
various queries within my Access database are specific for
each Word Mail Merge document. In my Access DB workgroup
security file, I have created a user called "WordUser"
whose sole purpose is to be able to run the Word Mail
Merge Letters. This user has no password and only
has "Read" permission on all the tables/queries required
for each letter and nothing else. I have had no luck
being able to get my Word 2002 letter to connect to my
secured Access database with the Mail Merge Wizard within
Word (I have posted a few questions in the newsgroups, but
have not gotten any responses). So instead, I have tried
to go the ODBC route, where I would create a new DSN entry
that points to my Access database, in hopes that I could
use this ODBC entry as my datasource for my Word Mail
Merge document. However, this method has also raised some
questions that I hope someone can answer for me or help in
the right direction.

1) To create a new ODBC entry, I do the following:
a) click on "System DSN" Tab in the ODBC Data Source
Administrator
b) click the "Add" button
c) select "Microsoft Access Driver (*.mdb)"
d) click the "Finish" button
e) In the "ODBC Microsoft Access Setup" Form I:
f) enter "MyDB" in the "Data Source Name:" text box
g) click the "Select..." button in the "Database" Frame
to located my Access Database
h) click the "Advanced..." button
i) enter my "WordUser" in the "Login name:" text box in
the "Set Advanced Options" form and click the "OK" button.
j) select the "Database:" radio button in the "System
Database" frame
k) click the "System Database..." button to select my
MDW file for my secured database
l) click the "OK" button on the ODBC Microsoft Access
Setup form to complete setting up my new ODBC entry

2) Now, once my ODBC entry has been created, I go to my
Word 2002/2000 letter and select ToolsLetters and
MailingsMail Merge Wizard. At the Wizard's Step #3, I
select "Browse" from the "Use an existing list" option.
This brings up the "Select Data Source" dialog box. Here,
the "My Data Sources" is selected in the "Look in:" combo
box and all my ODBC entries are showing EXCEPT my newly
created Access ODBC entry I created in my above steps (a-
l). However, there are 2 entries that I am not familiar
with: "+Connect to New Data Source.odc" and "+New SQL
Server Connection.odc". I selected the "+Connect to New
Data Source.odc" option and it took me into a new "Data
Connection Wizard" where I did the following:
a) Select "ODBC DSN" from the "What kind of data source
do you want to connect to?" list and clicked the "Next"
button
b) Now my newly created ODBC entry shows up in
the "ODBC data sources" list. I selected it and clicked
the "Next" button
c) At this point, all my tables and queries were
listed. From the list, I selected my query for the letter
I am creating and clicked the "Next" button.
d) Now, the "Save Data Connection File and Finish"
dialog box is shown, prompting me to save this as a
connection file (*.odc). I entered a name and clicked
the "Finish" button.

Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database, by
having to create a separate Connection File for each Mail
Merge Letter I am wanting to create?


  #2  
Old September 1st, 2004, 06:10 PM
Peter Jamieson
external usenet poster
 
Posts: n/a
Default

Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database, by
having to create a separate Connection File for each Mail
Merge Letter I am wanting to create?


IMO there is no "proper" way. At best, there may be a way that works for
your application and minimises problems if you need to distribute your
solution to mutliple systems.

There are a number of issues here and unfortunately I don't think I can give
you reliable suggestions in all cases, but here goes.

First, if you want to connect the same way in Word 2000 and Word 2002, you
have to use ODBC (or the old DDE method). OLEDB is only supported in Word
2002 and later. Unfortunately, the route you have taken in your example
actually uses OLEDB, using the ODBC provider for OLEDB. Any connection that
uses a .odc (or .udl, as we will see) is actually using OLEDB. So those
connections won't work with Word 2000. But in addition, you may find that
ODBC connections set up in Word 2000 are discarded when you open the .doc in
WOrd 2002 and/or vice versa.

This brings up the "Select Data Source" dialog box. Here,
the "My Data Sources" is selected in the "Look in:" combo
box and all my ODBC entries are showing EXCEPT my newly
created Access ODBC entry I created in my above steps (a-


The reason (probably) that some ODBC entries appear here and some do not is
that there are three types of ODBC DSN, as follows:

FIle DSNs: these are just text files in "INI" format that contain the
connection information you provided in the ODBC administrator. Typically
they have a .dsn extension. These are probably the ones you can see

Machine DSNs: these DSNs are not stored in text files. They are stored in
the Windows registry, so I do not think you will see them listed in "My Data
Sources". There are two types of Machine DSN:
a. User DSNs. These are intended to be visible to a single user on a
machine which may have several different users. They are stored in the
per-user part of the registry.
b. System DSNs (the type you created). These are intended to be visible to
all users on a particular machine.

Neither Word 2000 nor Word 2002, as far as I know, lets you open a .dsn file
directly (in the Open/Select Data Source dialog box). Word just tries to get
the data directly from the .dsn file, not from the database it refers to. If
you are trying to open a file data source such as a .mdb in Word 2000, you
can select the .mdb in the Open Data Source dialog, check "Select method",
and click Open. If the DSN is a Machine DSN you will probably see it in the
list of connection methods. Otherwise, you have to use MS Query to select
the DSN (of any type) and set up the connection. You can do the same in Word
2002, although MS Query is a little harder to find - it's on the Tools menu
in the top right hand corner of the Select Data Source dialog. (You may also
find you need to install MS Query to get what you want).

In both Word 2000 and 2002, you should only need one DSN to allow you to use
all the tables and queries in your database that are accessible via ODBC.
But you will need to go through the MS Query route to choose the table/query
or define your own.

An alternative to all this is to write, or macro record, a short macro
containing an OpenDataSource command that uses an ODBC connection string in
the Connection parameter. Then you have to work out the corrct SQL to get
the data you want, and insert it in the SQLStatement parameter, then run the
macro. You will find examples I've posted if you search this newsgroup, e.g.
using Google. One of the problems here is that Word 2002 requires an
additional parameter (Subtype) if you want to connect via ODBC.

If you are /only/ concerned with Word 2002 and prefer to use OLEDB, there
are a couple of ways you can proceed without creating an ODBC DSN.
a. use a .udl file. If you create a Notepad (.txt) file and rename it to
something.udl, then double-click on the file name, you should see the
Datalink editor. This is like, if not identical to, the editor you used to
select your DSN in your point 2. However,
a. in the Provider tab, select the Jet provider rather than the ODBC
provider
b. in the Connection tab, you may need to check Blank password and Allow
saving password
c. in the All tab, you will need to edit the valueJet OLEDB:System databse
to be the pathname of your workgroup file.
d. When you click OK, you do not see the questions that come at the end of
the dialog you used to set up your .odc

If you select this file as your data source, you should be presented with a
list of tables, so you only really need one .udl for all your documents. It
may be possible to use it to connect to a specific table or query, but
that's for you to discover if you want.

Alternatively, you can set up a .odc using much the same process you used
before in your point (2), but choose the Other/Advanced option instead of
ODBC DSN. Then fill in the datalink dialog options in the same way as for
the .udl file above. When you get to save the .odc, there is a checkbox that
lets you specify whether your .odc should cause Word to display a list of
tables/queries within your .mdb, or whether Word should connect to a
prespecified table/query. Again, if you use the former, you should be able
to use a single .odc for all your mailmerge documents.

--
Peter Jamieson

"Tony_VBACoder" wrote in message
...
I am new at setting up a Word document as a Mail Merge to
an Access Database. My situation involves a secured
Access 2000/2002 Database, with a Word 2000/2002 Mail
Merge Letter. My Access Databases are secured with a MDW
Security File required to open my Database. I have
various queries within my Access database are specific for
each Word Mail Merge document. In my Access DB workgroup
security file, I have created a user called "WordUser"
whose sole purpose is to be able to run the Word Mail
Merge Letters. This user has no password and only
has "Read" permission on all the tables/queries required
for each letter and nothing else. I have had no luck
being able to get my Word 2002 letter to connect to my
secured Access database with the Mail Merge Wizard within
Word (I have posted a few questions in the newsgroups, but
have not gotten any responses). So instead, I have tried
to go the ODBC route, where I would create a new DSN entry
that points to my Access database, in hopes that I could
use this ODBC entry as my datasource for my Word Mail
Merge document. However, this method has also raised some
questions that I hope someone can answer for me or help in
the right direction.

1) To create a new ODBC entry, I do the following:
a) click on "System DSN" Tab in the ODBC Data Source
Administrator
b) click the "Add" button
c) select "Microsoft Access Driver (*.mdb)"
d) click the "Finish" button
e) In the "ODBC Microsoft Access Setup" Form I:
f) enter "MyDB" in the "Data Source Name:" text box
g) click the "Select..." button in the "Database" Frame
to located my Access Database
h) click the "Advanced..." button
i) enter my "WordUser" in the "Login name:" text box in
the "Set Advanced Options" form and click the "OK" button.
j) select the "Database:" radio button in the "System
Database" frame
k) click the "System Database..." button to select my
MDW file for my secured database
l) click the "OK" button on the ODBC Microsoft Access
Setup form to complete setting up my new ODBC entry

2) Now, once my ODBC entry has been created, I go to my
Word 2002/2000 letter and select ToolsLetters and
MailingsMail Merge Wizard. At the Wizard's Step #3, I
select "Browse" from the "Use an existing list" option.
This brings up the "Select Data Source" dialog box. Here,
the "My Data Sources" is selected in the "Look in:" combo
box and all my ODBC entries are showing EXCEPT my newly
created Access ODBC entry I created in my above steps (a-
l). However, there are 2 entries that I am not familiar
with: "+Connect to New Data Source.odc" and "+New SQL
Server Connection.odc". I selected the "+Connect to New
Data Source.odc" option and it took me into a new "Data
Connection Wizard" where I did the following:
a) Select "ODBC DSN" from the "What kind of data source
do you want to connect to?" list and clicked the "Next"
button
b) Now my newly created ODBC entry shows up in
the "ODBC data sources" list. I selected it and clicked
the "Next" button
c) At this point, all my tables and queries were
listed. From the list, I selected my query for the letter
I am creating and clicked the "Next" button.
d) Now, the "Save Data Connection File and Finish"
dialog box is shown, prompting me to save this as a
connection file (*.odc). I entered a name and clicked
the "Finish" button.

Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database, by
having to create a separate Connection File for each Mail
Merge Letter I am wanting to create?




  #3  
Old September 1st, 2004, 06:37 PM
Tony_VBACoder
external usenet poster
 
Posts: n/a
Default

Peter, thank you for all your insight...it will take me a
little while to absorb it and test it out with both Word
2000 and 2002. I do have one question in regards to
something you mentioned:

First, if you want to connect the same way in Word 2000

and Word 2002, you have to use ODBC (or the old DDE
method).....But in addition, you may find that ODBC
connections set up in Word 2000 are discarded when you
open the .doc in WOrd 2002 and/or vice versa.

Here, you first said that I should go the ODBC route if I
want to get this to work in both Word 2000 and Word 2002,
but then you go onto say that an "ODBC connection set up
in Word 2000 are discarded when you open the .doc in Word
2002". I am a little confused here. Will this method
(ODBC) work in both Word 2000 and Word 2002? I will be
needing this work with both versions, where I will
probably end of creating the final Word Mail Merge .doc
using Word 2000 because about 90% of the users have Office
2000, whereas the rest of Office XP (Word 2002).

In regards to using ODBC, you mentioned "Unfortunately,
the route you have taken in your example actually uses
OLEDB, using the ODBC provider for OLEDB". If I wanted to
use the ODBC method, what should I have done so that I can
be certain that I am using the OLEDB method?

Thanks


-----Original Message-----
Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database, by
having to create a separate Connection File for each

Mail
Merge Letter I am wanting to create?


IMO there is no "proper" way. At best, there may be a way

that works for
your application and minimises problems if you need to

distribute your
solution to mutliple systems.

There are a number of issues here and unfortunately I

don't think I can give
you reliable suggestions in all cases, but here goes.

First, if you want to connect the same way in Word 2000

and Word 2002, you
have to use ODBC (or the old DDE method). OLEDB is only

supported in Word
2002 and later. Unfortunately, the route you have taken

in your example
actually uses OLEDB, using the ODBC provider for OLEDB.

Any connection that
uses a .odc (or .udl, as we will see) is actually using

OLEDB. So those
connections won't work with Word 2000. But in addition,

you may find that
ODBC connections set up in Word 2000 are discarded when

you open the .doc in
WOrd 2002 and/or vice versa.

This brings up the "Select Data Source" dialog box.

Here,
the "My Data Sources" is selected in the "Look in:"

combo
box and all my ODBC entries are showing EXCEPT my newly
created Access ODBC entry I created in my above steps

(a-

The reason (probably) that some ODBC entries appear here

and some do not is
that there are three types of ODBC DSN, as follows:

FIle DSNs: these are just text files in "INI" format that

contain the
connection information you provided in the ODBC

administrator. Typically
they have a .dsn extension. These are probably the ones

you can see

Machine DSNs: these DSNs are not stored in text files.

They are stored in
the Windows registry, so I do not think you will see them

listed in "My Data
Sources". There are two types of Machine DSN:
a. User DSNs. These are intended to be visible to a

single user on a
machine which may have several different users. They are

stored in the
per-user part of the registry.
b. System DSNs (the type you created). These are

intended to be visible to
all users on a particular machine.

Neither Word 2000 nor Word 2002, as far as I know, lets

you open a .dsn file
directly (in the Open/Select Data Source dialog box).

Word just tries to get
the data directly from the .dsn file, not from the

database it refers to. If
you are trying to open a file data source such as a .mdb

in Word 2000, you
can select the .mdb in the Open Data Source dialog,

check "Select method",
and click Open. If the DSN is a Machine DSN you will

probably see it in the
list of connection methods. Otherwise, you have to use MS

Query to select
the DSN (of any type) and set up the connection. You can

do the same in Word
2002, although MS Query is a little harder to find - it's

on the Tools menu
in the top right hand corner of the Select Data Source

dialog. (You may also
find you need to install MS Query to get what you want).

In both Word 2000 and 2002, you should only need one DSN

to allow you to use
all the tables and queries in your database that are

accessible via ODBC.
But you will need to go through the MS Query route to

choose the table/query
or define your own.

An alternative to all this is to write, or macro record,

a short macro
containing an OpenDataSource command that uses an ODBC

connection string in
the Connection parameter. Then you have to work out the

corrct SQL to get
the data you want, and insert it in the SQLStatement

parameter, then run the
macro. You will find examples I've posted if you search

this newsgroup, e.g.
using Google. One of the problems here is that Word 2002

requires an
additional parameter (Subtype) if you want to connect via

ODBC.

If you are /only/ concerned with Word 2002 and prefer to

use OLEDB, there
are a couple of ways you can proceed without creating an

ODBC DSN.
a. use a .udl file. If you create a Notepad (.txt) file

and rename it to
something.udl, then double-click on the file name, you

should see the
Datalink editor. This is like, if not identical to, the

editor you used to
select your DSN in your point 2. However,
a. in the Provider tab, select the Jet provider rather

than the ODBC
provider
b. in the Connection tab, you may need to check Blank

password and Allow
saving password
c. in the All tab, you will need to edit the valueJet

OLEDB:System databse
to be the pathname of your workgroup file.
d. When you click OK, you do not see the questions that

come at the end of
the dialog you used to set up your .odc

If you select this file as your data source, you should

be presented with a
list of tables, so you only really need one .udl for all

your documents. It
may be possible to use it to connect to a specific table

or query, but
that's for you to discover if you want.

Alternatively, you can set up a .odc using much the same

process you used
before in your point (2), but choose the Other/Advanced

option instead of
ODBC DSN. Then fill in the datalink dialog options in the

same way as for
the .udl file above. When you get to save the .odc, there

is a checkbox that
lets you specify whether your .odc should cause Word to

display a list of
tables/queries within your .mdb, or whether Word should

connect to a
prespecified table/query. Again, if you use the former,

you should be able
to use a single .odc for all your mailmerge documents.

--
Peter Jamieson

"Tony_VBACoder"

wrote in message
...
I am new at setting up a Word document as a Mail Merge

to
an Access Database. My situation involves a secured
Access 2000/2002 Database, with a Word 2000/2002 Mail
Merge Letter. My Access Databases are secured with a

MDW
Security File required to open my Database. I have
various queries within my Access database are specific

for
each Word Mail Merge document. In my Access DB

workgroup
security file, I have created a user called "WordUser"
whose sole purpose is to be able to run the Word Mail
Merge Letters. This user has no password and only
has "Read" permission on all the tables/queries required
for each letter and nothing else. I have had no luck
being able to get my Word 2002 letter to connect to my
secured Access database with the Mail Merge Wizard

within
Word (I have posted a few questions in the newsgroups,

but
have not gotten any responses). So instead, I have

tried
to go the ODBC route, where I would create a new DSN

entry
that points to my Access database, in hopes that I could
use this ODBC entry as my datasource for my Word Mail
Merge document. However, this method has also raised

some
questions that I hope someone can answer for me or help

in
the right direction.

1) To create a new ODBC entry, I do the following:
a) click on "System DSN" Tab in the ODBC Data Source
Administrator
b) click the "Add" button
c) select "Microsoft Access Driver (*.mdb)"
d) click the "Finish" button
e) In the "ODBC Microsoft Access Setup" Form I:
f) enter "MyDB" in the "Data Source Name:" text box
g) click the "Select..." button in the "Database"

Frame
to located my Access Database
h) click the "Advanced..." button
i) enter my "WordUser" in the "Login name:" text box

in
the "Set Advanced Options" form and click the "OK"

button.
j) select the "Database:" radio button in the "System
Database" frame
k) click the "System Database..." button to select my
MDW file for my secured database
l) click the "OK" button on the ODBC Microsoft Access
Setup form to complete setting up my new ODBC entry

2) Now, once my ODBC entry has been created, I go to my
Word 2002/2000 letter and select ToolsLetters and
MailingsMail Merge Wizard. At the Wizard's Step #3, I
select "Browse" from the "Use an existing list" option.
This brings up the "Select Data Source" dialog box.

Here,
the "My Data Sources" is selected in the "Look in:"

combo
box and all my ODBC entries are showing EXCEPT my newly
created Access ODBC entry I created in my above steps

(a-
l). However, there are 2 entries that I am not familiar
with: "+Connect to New Data Source.odc" and "+New SQL
Server Connection.odc". I selected the "+Connect to New
Data Source.odc" option and it took me into a new "Data
Connection Wizard" where I did the following:
a) Select "ODBC DSN" from the "What kind of data

source
do you want to connect to?" list and clicked the "Next"
button
b) Now my newly created ODBC entry shows up in
the "ODBC data sources" list. I selected it and clicked
the "Next" button
c) At this point, all my tables and queries were
listed. From the list, I selected my query for the

letter
I am creating and clicked the "Next" button.
d) Now, the "Save Data Connection File and Finish"
dialog box is shown, prompting me to save this as a
connection file (*.odc). I entered a name and clicked
the "Finish" button.

Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database, by
having to create a separate Connection File for each

Mail
Merge Letter I am wanting to create?




.

  #4  
Old September 1st, 2004, 07:03 PM
Peter Jamieson
external usenet poster
 
Posts: n/a
Default

Here, you first said that I should go the ODBC route if I
want to get this to work in both Word 2000 and Word 2002,
but then you go onto say that an "ODBC connection set up
in Word 2000 are discarded when you open the .doc in Word
2002". I am a little confused here. Will this method
(ODBC) work in both Word 2000 and Word 2002?


WHat I mean is that the OLEDB connection is simply not available in Word
2000. So the only option (other than DDE) that will work in both is ODBC.
However, that does not mean that Word 2002 will correctly recognise an ODBC
data source which has been set up in Word 2000 and vice versa. So...

I will be
needing this work with both versions, where I will
probably end of creating the final Word Mail Merge .doc
using Word 2000 because about 90% of the users have Office
2000, whereas the rest of Office XP (Word 2002).


....you /may/ need to create Word 2000 and Word 2002 versions of each mail
merge app. I can't remember off the top of my head so you will need to
check. I would also take a bit of time to ensure that your users can do what
they need - e.g. if they need to apply their own filters/sorting, you may
need to confirm that this approach allows them to do so.

In regards to using ODBC, you mentioned "Unfortunately,
the route you have taken in your example actually uses
OLEDB, using the ODBC provider for OLEDB". If I wanted to
use the ODBC method, what should I have done so that I can
be certain that I am using the OLEDB method?


Assuming you meant "certain that I am using the ODBC method", then you
either need to go via MS Query, or go the VBA OpenDataSource route, or check
Word Tools|Options|General|"Confirm conversions at open", go through the
Select Data Source dialog, pick your .mdb, then select the User/System DSN
from the list of possible connection options. If you don't see it there, you
will need to use MS Query or VBA.

--
Peter Jamieson

"Tony_VBACoder" wrote in message
...
Peter, thank you for all your insight...it will take me a
little while to absorb it and test it out with both Word
2000 and 2002. I do have one question in regards to
something you mentioned:

First, if you want to connect the same way in Word 2000

and Word 2002, you have to use ODBC (or the old DDE
method).....But in addition, you may find that ODBC
connections set up in Word 2000 are discarded when you
open the .doc in WOrd 2002 and/or vice versa.

Here, you first said that I should go the ODBC route if I
want to get this to work in both Word 2000 and Word 2002,
but then you go onto say that an "ODBC connection set up
in Word 2000 are discarded when you open the .doc in Word
2002". I am a little confused here. Will this method
(ODBC) work in both Word 2000 and Word 2002? I will be
needing this work with both versions, where I will
probably end of creating the final Word Mail Merge .doc
using Word 2000 because about 90% of the users have Office
2000, whereas the rest of Office XP (Word 2002).

In regards to using ODBC, you mentioned "Unfortunately,
the route you have taken in your example actually uses
OLEDB, using the ODBC provider for OLEDB". If I wanted to
use the ODBC method, what should I have done so that I can
be certain that I am using the OLEDB method?

Thanks


-----Original Message-----
Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database, by
having to create a separate Connection File for each

Mail
Merge Letter I am wanting to create?


IMO there is no "proper" way. At best, there may be a way

that works for
your application and minimises problems if you need to

distribute your
solution to mutliple systems.

There are a number of issues here and unfortunately I

don't think I can give
you reliable suggestions in all cases, but here goes.

First, if you want to connect the same way in Word 2000

and Word 2002, you
have to use ODBC (or the old DDE method). OLEDB is only

supported in Word
2002 and later. Unfortunately, the route you have taken

in your example
actually uses OLEDB, using the ODBC provider for OLEDB.

Any connection that
uses a .odc (or .udl, as we will see) is actually using

OLEDB. So those
connections won't work with Word 2000. But in addition,

you may find that
ODBC connections set up in Word 2000 are discarded when

you open the .doc in
WOrd 2002 and/or vice versa.

This brings up the "Select Data Source" dialog box.

Here,
the "My Data Sources" is selected in the "Look in:"

combo
box and all my ODBC entries are showing EXCEPT my newly
created Access ODBC entry I created in my above steps

(a-

The reason (probably) that some ODBC entries appear here

and some do not is
that there are three types of ODBC DSN, as follows:

FIle DSNs: these are just text files in "INI" format that

contain the
connection information you provided in the ODBC

administrator. Typically
they have a .dsn extension. These are probably the ones

you can see

Machine DSNs: these DSNs are not stored in text files.

They are stored in
the Windows registry, so I do not think you will see them

listed in "My Data
Sources". There are two types of Machine DSN:
a. User DSNs. These are intended to be visible to a

single user on a
machine which may have several different users. They are

stored in the
per-user part of the registry.
b. System DSNs (the type you created). These are

intended to be visible to
all users on a particular machine.

Neither Word 2000 nor Word 2002, as far as I know, lets

you open a .dsn file
directly (in the Open/Select Data Source dialog box).

Word just tries to get
the data directly from the .dsn file, not from the

database it refers to. If
you are trying to open a file data source such as a .mdb

in Word 2000, you
can select the .mdb in the Open Data Source dialog,

check "Select method",
and click Open. If the DSN is a Machine DSN you will

probably see it in the
list of connection methods. Otherwise, you have to use MS

Query to select
the DSN (of any type) and set up the connection. You can

do the same in Word
2002, although MS Query is a little harder to find - it's

on the Tools menu
in the top right hand corner of the Select Data Source

dialog. (You may also
find you need to install MS Query to get what you want).

In both Word 2000 and 2002, you should only need one DSN

to allow you to use
all the tables and queries in your database that are

accessible via ODBC.
But you will need to go through the MS Query route to

choose the table/query
or define your own.

An alternative to all this is to write, or macro record,

a short macro
containing an OpenDataSource command that uses an ODBC

connection string in
the Connection parameter. Then you have to work out the

corrct SQL to get
the data you want, and insert it in the SQLStatement

parameter, then run the
macro. You will find examples I've posted if you search

this newsgroup, e.g.
using Google. One of the problems here is that Word 2002

requires an
additional parameter (Subtype) if you want to connect via

ODBC.

If you are /only/ concerned with Word 2002 and prefer to

use OLEDB, there
are a couple of ways you can proceed without creating an

ODBC DSN.
a. use a .udl file. If you create a Notepad (.txt) file

and rename it to
something.udl, then double-click on the file name, you

should see the
Datalink editor. This is like, if not identical to, the

editor you used to
select your DSN in your point 2. However,
a. in the Provider tab, select the Jet provider rather

than the ODBC
provider
b. in the Connection tab, you may need to check Blank

password and Allow
saving password
c. in the All tab, you will need to edit the valueJet

OLEDB:System databse
to be the pathname of your workgroup file.
d. When you click OK, you do not see the questions that

come at the end of
the dialog you used to set up your .odc

If you select this file as your data source, you should

be presented with a
list of tables, so you only really need one .udl for all

your documents. It
may be possible to use it to connect to a specific table

or query, but
that's for you to discover if you want.

Alternatively, you can set up a .odc using much the same

process you used
before in your point (2), but choose the Other/Advanced

option instead of
ODBC DSN. Then fill in the datalink dialog options in the

same way as for
the .udl file above. When you get to save the .odc, there

is a checkbox that
lets you specify whether your .odc should cause Word to

display a list of
tables/queries within your .mdb, or whether Word should

connect to a
prespecified table/query. Again, if you use the former,

you should be able
to use a single .odc for all your mailmerge documents.

--
Peter Jamieson

"Tony_VBACoder"

wrote in message
...
I am new at setting up a Word document as a Mail Merge

to
an Access Database. My situation involves a secured
Access 2000/2002 Database, with a Word 2000/2002 Mail
Merge Letter. My Access Databases are secured with a

MDW
Security File required to open my Database. I have
various queries within my Access database are specific

for
each Word Mail Merge document. In my Access DB

workgroup
security file, I have created a user called "WordUser"
whose sole purpose is to be able to run the Word Mail
Merge Letters. This user has no password and only
has "Read" permission on all the tables/queries required
for each letter and nothing else. I have had no luck
being able to get my Word 2002 letter to connect to my
secured Access database with the Mail Merge Wizard

within
Word (I have posted a few questions in the newsgroups,

but
have not gotten any responses). So instead, I have

tried
to go the ODBC route, where I would create a new DSN

entry
that points to my Access database, in hopes that I could
use this ODBC entry as my datasource for my Word Mail
Merge document. However, this method has also raised

some
questions that I hope someone can answer for me or help

in
the right direction.

1) To create a new ODBC entry, I do the following:
a) click on "System DSN" Tab in the ODBC Data Source
Administrator
b) click the "Add" button
c) select "Microsoft Access Driver (*.mdb)"
d) click the "Finish" button
e) In the "ODBC Microsoft Access Setup" Form I:
f) enter "MyDB" in the "Data Source Name:" text box
g) click the "Select..." button in the "Database"

Frame
to located my Access Database
h) click the "Advanced..." button
i) enter my "WordUser" in the "Login name:" text box

in
the "Set Advanced Options" form and click the "OK"

button.
j) select the "Database:" radio button in the "System
Database" frame
k) click the "System Database..." button to select my
MDW file for my secured database
l) click the "OK" button on the ODBC Microsoft Access
Setup form to complete setting up my new ODBC entry

2) Now, once my ODBC entry has been created, I go to my
Word 2002/2000 letter and select ToolsLetters and
MailingsMail Merge Wizard. At the Wizard's Step #3, I
select "Browse" from the "Use an existing list" option.
This brings up the "Select Data Source" dialog box.

Here,
the "My Data Sources" is selected in the "Look in:"

combo
box and all my ODBC entries are showing EXCEPT my newly
created Access ODBC entry I created in my above steps

(a-
l). However, there are 2 entries that I am not familiar
with: "+Connect to New Data Source.odc" and "+New SQL
Server Connection.odc". I selected the "+Connect to New
Data Source.odc" option and it took me into a new "Data
Connection Wizard" where I did the following:
a) Select "ODBC DSN" from the "What kind of data

source
do you want to connect to?" list and clicked the "Next"
button
b) Now my newly created ODBC entry shows up in
the "ODBC data sources" list. I selected it and clicked
the "Next" button
c) At this point, all my tables and queries were
listed. From the list, I selected my query for the

letter
I am creating and clicked the "Next" button.
d) Now, the "Save Data Connection File and Finish"
dialog box is shown, prompting me to save this as a
connection file (*.odc). I entered a name and clicked
the "Finish" button.

Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database, by
having to create a separate Connection File for each

Mail
Merge Letter I am wanting to create?




.



  #5  
Old September 1st, 2004, 09:49 PM
Tony_VBACoder
external usenet poster
 
Posts: n/a
Default

Peter, thanks for all your help.

I was able to find the MS Query button and locate my
correct Query, and everything is working great.

There are still a couple of other issues that I am running
into in terms of wanting to pull data from a Parameter
Query in Access, and I am also running into the "The
Database has been placed in a state by user 'bob' on
machine 'mach123' that prevents it from being opened or
locked" error. But I will be posting these as separate
issues as soon as I do a little more testing so that I can
properly post what is going on.

Thanks again.


-----Original Message-----
Here, you first said that I should go the ODBC route if

I
want to get this to work in both Word 2000 and Word

2002,
but then you go onto say that an "ODBC connection set up
in Word 2000 are discarded when you open the .doc in

Word
2002". I am a little confused here. Will this method
(ODBC) work in both Word 2000 and Word 2002?


WHat I mean is that the OLEDB connection is simply not

available in Word
2000. So the only option (other than DDE) that will work

in both is ODBC.
However, that does not mean that Word 2002 will correctly

recognise an ODBC
data source which has been set up in Word 2000 and vice

versa. So...

I will be
needing this work with both versions, where I will
probably end of creating the final Word Mail Merge .doc
using Word 2000 because about 90% of the users have

Office
2000, whereas the rest of Office XP (Word 2002).


....you /may/ need to create Word 2000 and Word 2002

versions of each mail
merge app. I can't remember off the top of my head so you

will need to
check. I would also take a bit of time to ensure that

your users can do what
they need - e.g. if they need to apply their own

filters/sorting, you may
need to confirm that this approach allows them to do so.

In regards to using ODBC, you mentioned "Unfortunately,
the route you have taken in your example actually uses
OLEDB, using the ODBC provider for OLEDB". If I wanted

to
use the ODBC method, what should I have done so that I

can
be certain that I am using the OLEDB method?


Assuming you meant "certain that I am using the ODBC

method", then you
either need to go via MS Query, or go the VBA

OpenDataSource route, or check
Word Tools|Options|General|"Confirm conversions at open",

go through the
Select Data Source dialog, pick your .mdb, then select

the User/System DSN
from the list of possible connection options. If you

don't see it there, you
will need to use MS Query or VBA.

--
Peter Jamieson

"Tony_VBACoder"

wrote in message
...
Peter, thank you for all your insight...it will take me

a
little while to absorb it and test it out with both Word
2000 and 2002. I do have one question in regards to
something you mentioned:

First, if you want to connect the same way in Word

2000
and Word 2002, you have to use ODBC (or the old DDE
method).....But in addition, you may find that ODBC
connections set up in Word 2000 are discarded when you
open the .doc in WOrd 2002 and/or vice versa.

Here, you first said that I should go the ODBC route if

I
want to get this to work in both Word 2000 and Word

2002,
but then you go onto say that an "ODBC connection set up
in Word 2000 are discarded when you open the .doc in

Word
2002". I am a little confused here. Will this method
(ODBC) work in both Word 2000 and Word 2002? I will be
needing this work with both versions, where I will
probably end of creating the final Word Mail Merge .doc
using Word 2000 because about 90% of the users have

Office
2000, whereas the rest of Office XP (Word 2002).

In regards to using ODBC, you mentioned "Unfortunately,
the route you have taken in your example actually uses
OLEDB, using the ODBC provider for OLEDB". If I wanted

to
use the ODBC method, what should I have done so that I

can
be certain that I am using the OLEDB method?

Thanks


-----Original Message-----
Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database, by
having to create a separate Connection File for each

Mail
Merge Letter I am wanting to create?

IMO there is no "proper" way. At best, there may be a

way
that works for
your application and minimises problems if you need to

distribute your
solution to mutliple systems.

There are a number of issues here and unfortunately I

don't think I can give
you reliable suggestions in all cases, but here goes.

First, if you want to connect the same way in Word 2000

and Word 2002, you
have to use ODBC (or the old DDE method). OLEDB is only

supported in Word
2002 and later. Unfortunately, the route you have taken

in your example
actually uses OLEDB, using the ODBC provider for OLEDB.

Any connection that
uses a .odc (or .udl, as we will see) is actually using

OLEDB. So those
connections won't work with Word 2000. But in addition,

you may find that
ODBC connections set up in Word 2000 are discarded when

you open the .doc in
WOrd 2002 and/or vice versa.

This brings up the "Select Data Source" dialog box.

Here,
the "My Data Sources" is selected in the "Look in:"

combo
box and all my ODBC entries are showing EXCEPT my

newly
created Access ODBC entry I created in my above steps

(a-

The reason (probably) that some ODBC entries appear

here
and some do not is
that there are three types of ODBC DSN, as follows:

FIle DSNs: these are just text files in "INI" format

that
contain the
connection information you provided in the ODBC

administrator. Typically
they have a .dsn extension. These are probably the ones

you can see

Machine DSNs: these DSNs are not stored in text files.

They are stored in
the Windows registry, so I do not think you will see

them
listed in "My Data
Sources". There are two types of Machine DSN:
a. User DSNs. These are intended to be visible to a

single user on a
machine which may have several different users. They

are
stored in the
per-user part of the registry.
b. System DSNs (the type you created). These are

intended to be visible to
all users on a particular machine.

Neither Word 2000 nor Word 2002, as far as I know, lets

you open a .dsn file
directly (in the Open/Select Data Source dialog box).

Word just tries to get
the data directly from the .dsn file, not from the

database it refers to. If
you are trying to open a file data source such as

a .mdb
in Word 2000, you
can select the .mdb in the Open Data Source dialog,

check "Select method",
and click Open. If the DSN is a Machine DSN you will

probably see it in the
list of connection methods. Otherwise, you have to use

MS
Query to select
the DSN (of any type) and set up the connection. You

can
do the same in Word
2002, although MS Query is a little harder to find -

it's
on the Tools menu
in the top right hand corner of the Select Data Source

dialog. (You may also
find you need to install MS Query to get what you

want).

In both Word 2000 and 2002, you should only need one

DSN
to allow you to use
all the tables and queries in your database that are

accessible via ODBC.
But you will need to go through the MS Query route to

choose the table/query
or define your own.

An alternative to all this is to write, or macro

record,
a short macro
containing an OpenDataSource command that uses an ODBC

connection string in
the Connection parameter. Then you have to work out the

corrct SQL to get
the data you want, and insert it in the SQLStatement

parameter, then run the
macro. You will find examples I've posted if you search

this newsgroup, e.g.
using Google. One of the problems here is that Word

2002
requires an
additional parameter (Subtype) if you want to connect

via
ODBC.

If you are /only/ concerned with Word 2002 and prefer

to
use OLEDB, there
are a couple of ways you can proceed without creating

an
ODBC DSN.
a. use a .udl file. If you create a Notepad (.txt)

file
and rename it to
something.udl, then double-click on the file name, you

should see the
Datalink editor. This is like, if not identical to, the

editor you used to
select your DSN in your point 2. However,
a. in the Provider tab, select the Jet provider rather

than the ODBC
provider
b. in the Connection tab, you may need to check Blank

password and Allow
saving password
c. in the All tab, you will need to edit the valueJet

OLEDB:System databse
to be the pathname of your workgroup file.
d. When you click OK, you do not see the questions

that
come at the end of
the dialog you used to set up your .odc

If you select this file as your data source, you should

be presented with a
list of tables, so you only really need one .udl for

all
your documents. It
may be possible to use it to connect to a specific

table
or query, but
that's for you to discover if you want.

Alternatively, you can set up a .odc using much the

same
process you used
before in your point (2), but choose the Other/Advanced

option instead of
ODBC DSN. Then fill in the datalink dialog options in

the
same way as for
the .udl file above. When you get to save the .odc,

there
is a checkbox that
lets you specify whether your .odc should cause Word to

display a list of
tables/queries within your .mdb, or whether Word should

connect to a
prespecified table/query. Again, if you use the former,

you should be able
to use a single .odc for all your mailmerge documents.

--
Peter Jamieson

"Tony_VBACoder"

wrote in message
...
I am new at setting up a Word document as a Mail

Merge
to
an Access Database. My situation involves a secured
Access 2000/2002 Database, with a Word 2000/2002 Mail
Merge Letter. My Access Databases are secured with a

MDW
Security File required to open my Database. I have
various queries within my Access database are

specific
for
each Word Mail Merge document. In my Access DB

workgroup
security file, I have created a user

called "WordUser"
whose sole purpose is to be able to run the Word Mail
Merge Letters. This user has no password and only
has "Read" permission on all the tables/queries

required
for each letter and nothing else. I have had no luck
being able to get my Word 2002 letter to connect to

my
secured Access database with the Mail Merge Wizard

within
Word (I have posted a few questions in the

newsgroups,
but
have not gotten any responses). So instead, I have

tried
to go the ODBC route, where I would create a new DSN

entry
that points to my Access database, in hopes that I

could
use this ODBC entry as my datasource for my Word Mail
Merge document. However, this method has also raised

some
questions that I hope someone can answer for me or

help
in
the right direction.

1) To create a new ODBC entry, I do the following:
a) click on "System DSN" Tab in the ODBC Data

Source
Administrator
b) click the "Add" button
c) select "Microsoft Access Driver (*.mdb)"
d) click the "Finish" button
e) In the "ODBC Microsoft Access Setup" Form I:
f) enter "MyDB" in the "Data Source Name:" text

box
g) click the "Select..." button in the "Database"

Frame
to located my Access Database
h) click the "Advanced..." button
i) enter my "WordUser" in the "Login name:" text

box
in
the "Set Advanced Options" form and click the "OK"

button.
j) select the "Database:" radio button in

the "System
Database" frame
k) click the "System Database..." button to

select my
MDW file for my secured database
l) click the "OK" button on the ODBC Microsoft

Access
Setup form to complete setting up my new ODBC entry

2) Now, once my ODBC entry has been created, I go

to my
Word 2002/2000 letter and select ToolsLetters and
MailingsMail Merge Wizard. At the Wizard's Step

#3, I
select "Browse" from the "Use an existing list"

option.
This brings up the "Select Data Source" dialog box.

Here,
the "My Data Sources" is selected in the "Look in:"

combo
box and all my ODBC entries are showing EXCEPT my

newly
created Access ODBC entry I created in my above steps

(a-
l). However, there are 2 entries that I am not

familiar
with: "+Connect to New Data Source.odc" and "+New SQL
Server Connection.odc". I selected the "+Connect to

New
Data Source.odc" option and it took me into a

new "Data
Connection Wizard" where I did the following:
a) Select "ODBC DSN" from the "What kind of data

source
do you want to connect to?" list and clicked

the "Next"
button
b) Now my newly created ODBC entry shows up in
the "ODBC data sources" list. I selected it and

clicked
the "Next" button
c) At this point, all my tables and queries were
listed. From the list, I selected my query for the

letter
I am creating and clicked the "Next" button.
d) Now, the "Save Data Connection File and Finish"
dialog box is shown, prompting me to save this as a
connection file (*.odc). I entered a name and

clicked
the "Finish" button.

Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database, by
having to create a separate Connection File for each

Mail
Merge Letter I am wanting to create?




.



.

  #6  
Old September 2nd, 2004, 09:32 AM
Peter Jamieson
external usenet poster
 
Posts: n/a
Default

FWIW,
a. for parameter queries, you have to use DDE because it is Access that
displays the pop-ups that solicit the parameter values from the user, and
DDE is the only connection method that actually starts Access.
b. although you can /probably/ specify the access mode Word will use when
using a .udl, .odc or ODBC Connection parameter in VBA (there is a tab for
this in the Datalink editor),
- it obviously won't help if someone else is opening the database in an
incompatible sharing mode (e.g. some sort of exclsive access mode)
- I get the impression that by default, when Word connects to a .mdb
using OLEDB, it uses a more exclusive mode than it really should. I'm not
completely convinced that anything you specify in a .odc will change that.
But I don't know the facts on that one.

--
Peter Jamieson

"Tony_VBACoder" wrote in message
...
Peter, thanks for all your help.

I was able to find the MS Query button and locate my
correct Query, and everything is working great.

There are still a couple of other issues that I am running
into in terms of wanting to pull data from a Parameter
Query in Access, and I am also running into the "The
Database has been placed in a state by user 'bob' on
machine 'mach123' that prevents it from being opened or
locked" error. But I will be posting these as separate
issues as soon as I do a little more testing so that I can
properly post what is going on.

Thanks again.


-----Original Message-----
Here, you first said that I should go the ODBC route if

I
want to get this to work in both Word 2000 and Word

2002,
but then you go onto say that an "ODBC connection set up
in Word 2000 are discarded when you open the .doc in

Word
2002". I am a little confused here. Will this method
(ODBC) work in both Word 2000 and Word 2002?


WHat I mean is that the OLEDB connection is simply not

available in Word
2000. So the only option (other than DDE) that will work

in both is ODBC.
However, that does not mean that Word 2002 will correctly

recognise an ODBC
data source which has been set up in Word 2000 and vice

versa. So...

I will be
needing this work with both versions, where I will
probably end of creating the final Word Mail Merge .doc
using Word 2000 because about 90% of the users have

Office
2000, whereas the rest of Office XP (Word 2002).


....you /may/ need to create Word 2000 and Word 2002

versions of each mail
merge app. I can't remember off the top of my head so you

will need to
check. I would also take a bit of time to ensure that

your users can do what
they need - e.g. if they need to apply their own

filters/sorting, you may
need to confirm that this approach allows them to do so.

In regards to using ODBC, you mentioned "Unfortunately,
the route you have taken in your example actually uses
OLEDB, using the ODBC provider for OLEDB". If I wanted

to
use the ODBC method, what should I have done so that I

can
be certain that I am using the OLEDB method?


Assuming you meant "certain that I am using the ODBC

method", then you
either need to go via MS Query, or go the VBA

OpenDataSource route, or check
Word Tools|Options|General|"Confirm conversions at open",

go through the
Select Data Source dialog, pick your .mdb, then select

the User/System DSN
from the list of possible connection options. If you

don't see it there, you
will need to use MS Query or VBA.

--
Peter Jamieson

"Tony_VBACoder"

wrote in message
...
Peter, thank you for all your insight...it will take me

a
little while to absorb it and test it out with both Word
2000 and 2002. I do have one question in regards to
something you mentioned:

First, if you want to connect the same way in Word

2000
and Word 2002, you have to use ODBC (or the old DDE
method).....But in addition, you may find that ODBC
connections set up in Word 2000 are discarded when you
open the .doc in WOrd 2002 and/or vice versa.

Here, you first said that I should go the ODBC route if

I
want to get this to work in both Word 2000 and Word

2002,
but then you go onto say that an "ODBC connection set up
in Word 2000 are discarded when you open the .doc in

Word
2002". I am a little confused here. Will this method
(ODBC) work in both Word 2000 and Word 2002? I will be
needing this work with both versions, where I will
probably end of creating the final Word Mail Merge .doc
using Word 2000 because about 90% of the users have

Office
2000, whereas the rest of Office XP (Word 2002).

In regards to using ODBC, you mentioned "Unfortunately,
the route you have taken in your example actually uses
OLEDB, using the ODBC provider for OLEDB". If I wanted

to
use the ODBC method, what should I have done so that I

can
be certain that I am using the OLEDB method?

Thanks


-----Original Message-----
Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database, by
having to create a separate Connection File for each
Mail
Merge Letter I am wanting to create?

IMO there is no "proper" way. At best, there may be a

way
that works for
your application and minimises problems if you need to
distribute your
solution to mutliple systems.

There are a number of issues here and unfortunately I
don't think I can give
you reliable suggestions in all cases, but here goes.

First, if you want to connect the same way in Word 2000
and Word 2002, you
have to use ODBC (or the old DDE method). OLEDB is only
supported in Word
2002 and later. Unfortunately, the route you have taken
in your example
actually uses OLEDB, using the ODBC provider for OLEDB.
Any connection that
uses a .odc (or .udl, as we will see) is actually using
OLEDB. So those
connections won't work with Word 2000. But in addition,
you may find that
ODBC connections set up in Word 2000 are discarded when
you open the .doc in
WOrd 2002 and/or vice versa.

This brings up the "Select Data Source" dialog box.
Here,
the "My Data Sources" is selected in the "Look in:"
combo
box and all my ODBC entries are showing EXCEPT my

newly
created Access ODBC entry I created in my above steps
(a-

The reason (probably) that some ODBC entries appear

here
and some do not is
that there are three types of ODBC DSN, as follows:

FIle DSNs: these are just text files in "INI" format

that
contain the
connection information you provided in the ODBC
administrator. Typically
they have a .dsn extension. These are probably the ones
you can see

Machine DSNs: these DSNs are not stored in text files.
They are stored in
the Windows registry, so I do not think you will see

them
listed in "My Data
Sources". There are two types of Machine DSN:
a. User DSNs. These are intended to be visible to a
single user on a
machine which may have several different users. They

are
stored in the
per-user part of the registry.
b. System DSNs (the type you created). These are
intended to be visible to
all users on a particular machine.

Neither Word 2000 nor Word 2002, as far as I know, lets
you open a .dsn file
directly (in the Open/Select Data Source dialog box).
Word just tries to get
the data directly from the .dsn file, not from the
database it refers to. If
you are trying to open a file data source such as

a .mdb
in Word 2000, you
can select the .mdb in the Open Data Source dialog,
check "Select method",
and click Open. If the DSN is a Machine DSN you will
probably see it in the
list of connection methods. Otherwise, you have to use

MS
Query to select
the DSN (of any type) and set up the connection. You

can
do the same in Word
2002, although MS Query is a little harder to find -

it's
on the Tools menu
in the top right hand corner of the Select Data Source
dialog. (You may also
find you need to install MS Query to get what you

want).

In both Word 2000 and 2002, you should only need one

DSN
to allow you to use
all the tables and queries in your database that are
accessible via ODBC.
But you will need to go through the MS Query route to
choose the table/query
or define your own.

An alternative to all this is to write, or macro

record,
a short macro
containing an OpenDataSource command that uses an ODBC
connection string in
the Connection parameter. Then you have to work out the
corrct SQL to get
the data you want, and insert it in the SQLStatement
parameter, then run the
macro. You will find examples I've posted if you search
this newsgroup, e.g.
using Google. One of the problems here is that Word

2002
requires an
additional parameter (Subtype) if you want to connect

via
ODBC.

If you are /only/ concerned with Word 2002 and prefer

to
use OLEDB, there
are a couple of ways you can proceed without creating

an
ODBC DSN.
a. use a .udl file. If you create a Notepad (.txt)

file
and rename it to
something.udl, then double-click on the file name, you
should see the
Datalink editor. This is like, if not identical to, the
editor you used to
select your DSN in your point 2. However,
a. in the Provider tab, select the Jet provider rather
than the ODBC
provider
b. in the Connection tab, you may need to check Blank
password and Allow
saving password
c. in the All tab, you will need to edit the valueJet
OLEDB:System databse
to be the pathname of your workgroup file.
d. When you click OK, you do not see the questions

that
come at the end of
the dialog you used to set up your .odc

If you select this file as your data source, you should
be presented with a
list of tables, so you only really need one .udl for

all
your documents. It
may be possible to use it to connect to a specific

table
or query, but
that's for you to discover if you want.

Alternatively, you can set up a .odc using much the

same
process you used
before in your point (2), but choose the Other/Advanced
option instead of
ODBC DSN. Then fill in the datalink dialog options in

the
same way as for
the .udl file above. When you get to save the .odc,

there
is a checkbox that
lets you specify whether your .odc should cause Word to
display a list of
tables/queries within your .mdb, or whether Word should
connect to a
prespecified table/query. Again, if you use the former,
you should be able
to use a single .odc for all your mailmerge documents.

--
Peter Jamieson

"Tony_VBACoder"
wrote in message
...
I am new at setting up a Word document as a Mail

Merge
to
an Access Database. My situation involves a secured
Access 2000/2002 Database, with a Word 2000/2002 Mail
Merge Letter. My Access Databases are secured with a
MDW
Security File required to open my Database. I have
various queries within my Access database are

specific
for
each Word Mail Merge document. In my Access DB
workgroup
security file, I have created a user

called "WordUser"
whose sole purpose is to be able to run the Word Mail
Merge Letters. This user has no password and only
has "Read" permission on all the tables/queries

required
for each letter and nothing else. I have had no luck
being able to get my Word 2002 letter to connect to

my
secured Access database with the Mail Merge Wizard
within
Word (I have posted a few questions in the

newsgroups,
but
have not gotten any responses). So instead, I have
tried
to go the ODBC route, where I would create a new DSN
entry
that points to my Access database, in hopes that I

could
use this ODBC entry as my datasource for my Word Mail
Merge document. However, this method has also raised
some
questions that I hope someone can answer for me or

help
in
the right direction.

1) To create a new ODBC entry, I do the following:
a) click on "System DSN" Tab in the ODBC Data

Source
Administrator
b) click the "Add" button
c) select "Microsoft Access Driver (*.mdb)"
d) click the "Finish" button
e) In the "ODBC Microsoft Access Setup" Form I:
f) enter "MyDB" in the "Data Source Name:" text

box
g) click the "Select..." button in the "Database"
Frame
to located my Access Database
h) click the "Advanced..." button
i) enter my "WordUser" in the "Login name:" text

box
in
the "Set Advanced Options" form and click the "OK"
button.
j) select the "Database:" radio button in

the "System
Database" frame
k) click the "System Database..." button to

select my
MDW file for my secured database
l) click the "OK" button on the ODBC Microsoft

Access
Setup form to complete setting up my new ODBC entry

2) Now, once my ODBC entry has been created, I go

to my
Word 2002/2000 letter and select ToolsLetters and
MailingsMail Merge Wizard. At the Wizard's Step

#3, I
select "Browse" from the "Use an existing list"

option.
This brings up the "Select Data Source" dialog box.
Here,
the "My Data Sources" is selected in the "Look in:"
combo
box and all my ODBC entries are showing EXCEPT my

newly
created Access ODBC entry I created in my above steps
(a-
l). However, there are 2 entries that I am not

familiar
with: "+Connect to New Data Source.odc" and "+New SQL
Server Connection.odc". I selected the "+Connect to

New
Data Source.odc" option and it took me into a

new "Data
Connection Wizard" where I did the following:
a) Select "ODBC DSN" from the "What kind of data
source
do you want to connect to?" list and clicked

the "Next"
button
b) Now my newly created ODBC entry shows up in
the "ODBC data sources" list. I selected it and

clicked
the "Next" button
c) At this point, all my tables and queries were
listed. From the list, I selected my query for the
letter
I am creating and clicked the "Next" button.
d) Now, the "Save Data Connection File and Finish"
dialog box is shown, prompting me to save this as a
connection file (*.odc). I entered a name and

clicked
the "Finish" button.

Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database, by
having to create a separate Connection File for each
Mail
Merge Letter I am wanting to create?




.



.



  #7  
Old September 2nd, 2004, 08:43 PM
Tony_VBACoder
external usenet poster
 
Posts: n/a
Default

After doing a little testing (though not enough to be
completely satisfied yet...), I did find that if I go into
my ODBC entry and click the "Configure" button, and if I
check the "Read Only" check box from the "Options" button,
I don't get the "Database has been placed in a state by
user 'bob' on machine 'mach123' that prevents it from
being opened or locked" error.

As for the Parameter Query method and using DDE, how do I
go about setting this up?

Thank you for all your insight.

-----Original Message-----
FWIW,
a. for parameter queries, you have to use DDE because it

is Access that
displays the pop-ups that solicit the parameter values

from the user, and
DDE is the only connection method that actually starts

Access.
b. although you can /probably/ specify the access mode

Word will use when
using a .udl, .odc or ODBC Connection parameter in VBA

(there is a tab for
this in the Datalink editor),
- it obviously won't help if someone else is opening

the database in an
incompatible sharing mode (e.g. some sort of exclsive

access mode)
- I get the impression that by default, when Word

connects to a .mdb
using OLEDB, it uses a more exclusive mode than it really

should. I'm not
completely convinced that anything you specify in a .odc

will change that.
But I don't know the facts on that one.

--
Peter Jamieson

"Tony_VBACoder"

wrote in message
...
Peter, thanks for all your help.

I was able to find the MS Query button and locate my
correct Query, and everything is working great.

There are still a couple of other issues that I am

running
into in terms of wanting to pull data from a Parameter
Query in Access, and I am also running into the "The
Database has been placed in a state by user 'bob' on
machine 'mach123' that prevents it from being opened or
locked" error. But I will be posting these as separate
issues as soon as I do a little more testing so that I

can
properly post what is going on.

Thanks again.


-----Original Message-----
Here, you first said that I should go the ODBC route

if
I
want to get this to work in both Word 2000 and Word

2002,
but then you go onto say that an "ODBC connection

set up
in Word 2000 are discarded when you open the .doc in

Word
2002". I am a little confused here. Will this

method
(ODBC) work in both Word 2000 and Word 2002?

WHat I mean is that the OLEDB connection is simply not

available in Word
2000. So the only option (other than DDE) that will

work
in both is ODBC.
However, that does not mean that Word 2002 will

correctly
recognise an ODBC
data source which has been set up in Word 2000 and vice

versa. So...

I will be
needing this work with both versions, where I will
probably end of creating the final Word Mail

Merge .doc
using Word 2000 because about 90% of the users have

Office
2000, whereas the rest of Office XP (Word 2002).

....you /may/ need to create Word 2000 and Word 2002

versions of each mail
merge app. I can't remember off the top of my head so

you
will need to
check. I would also take a bit of time to ensure that

your users can do what
they need - e.g. if they need to apply their own

filters/sorting, you may
need to confirm that this approach allows them to do

so.

In regards to using ODBC, you

mentioned "Unfortunately,
the route you have taken in your example actually

uses
OLEDB, using the ODBC provider for OLEDB". If I

wanted
to
use the ODBC method, what should I have done so that

I
can
be certain that I am using the OLEDB method?

Assuming you meant "certain that I am using the ODBC

method", then you
either need to go via MS Query, or go the VBA

OpenDataSource route, or check
Word Tools|Options|General|"Confirm conversions at

open",
go through the
Select Data Source dialog, pick your .mdb, then select

the User/System DSN
from the list of possible connection options. If you

don't see it there, you
will need to use MS Query or VBA.

--
Peter Jamieson

"Tony_VBACoder"

wrote in message
...
Peter, thank you for all your insight...it will take

me
a
little while to absorb it and test it out with both

Word
2000 and 2002. I do have one question in regards to
something you mentioned:

First, if you want to connect the same way in Word

2000
and Word 2002, you have to use ODBC (or the old DDE
method).....But in addition, you may find that ODBC
connections set up in Word 2000 are discarded when

you
open the .doc in WOrd 2002 and/or vice versa.

Here, you first said that I should go the ODBC route

if
I
want to get this to work in both Word 2000 and Word

2002,
but then you go onto say that an "ODBC connection

set up
in Word 2000 are discarded when you open the .doc in

Word
2002". I am a little confused here. Will this

method
(ODBC) work in both Word 2000 and Word 2002? I will

be
needing this work with both versions, where I will
probably end of creating the final Word Mail

Merge .doc
using Word 2000 because about 90% of the users have

Office
2000, whereas the rest of Office XP (Word 2002).

In regards to using ODBC, you

mentioned "Unfortunately,
the route you have taken in your example actually

uses
OLEDB, using the ODBC provider for OLEDB". If I

wanted
to
use the ODBC method, what should I have done so that

I
can
be certain that I am using the OLEDB method?

Thanks


-----Original Message-----
Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database,

by
having to create a separate Connection File for

each
Mail
Merge Letter I am wanting to create?

IMO there is no "proper" way. At best, there may be

a
way
that works for
your application and minimises problems if you need

to
distribute your
solution to mutliple systems.

There are a number of issues here and unfortunately

I
don't think I can give
you reliable suggestions in all cases, but here

goes.

First, if you want to connect the same way in Word

2000
and Word 2002, you
have to use ODBC (or the old DDE method). OLEDB is

only
supported in Word
2002 and later. Unfortunately, the route you have

taken
in your example
actually uses OLEDB, using the ODBC provider for

OLEDB.
Any connection that
uses a .odc (or .udl, as we will see) is actually

using
OLEDB. So those
connections won't work with Word 2000. But in

addition,
you may find that
ODBC connections set up in Word 2000 are discarded

when
you open the .doc in
WOrd 2002 and/or vice versa.

This brings up the "Select Data Source" dialog

box.
Here,
the "My Data Sources" is selected in the "Look

in:"
combo
box and all my ODBC entries are showing EXCEPT my

newly
created Access ODBC entry I created in my above

steps
(a-

The reason (probably) that some ODBC entries appear

here
and some do not is
that there are three types of ODBC DSN, as follows:

FIle DSNs: these are just text files in "INI" format

that
contain the
connection information you provided in the ODBC
administrator. Typically
they have a .dsn extension. These are probably the

ones
you can see

Machine DSNs: these DSNs are not stored in text

files.
They are stored in
the Windows registry, so I do not think you will see

them
listed in "My Data
Sources". There are two types of Machine DSN:
a. User DSNs. These are intended to be visible to a
single user on a
machine which may have several different users. They

are
stored in the
per-user part of the registry.
b. System DSNs (the type you created). These are
intended to be visible to
all users on a particular machine.

Neither Word 2000 nor Word 2002, as far as I know,

lets
you open a .dsn file
directly (in the Open/Select Data Source dialog

box).
Word just tries to get
the data directly from the .dsn file, not from the
database it refers to. If
you are trying to open a file data source such as

a .mdb
in Word 2000, you
can select the .mdb in the Open Data Source dialog,
check "Select method",
and click Open. If the DSN is a Machine DSN you will
probably see it in the
list of connection methods. Otherwise, you have to

use
MS
Query to select
the DSN (of any type) and set up the connection. You

can
do the same in Word
2002, although MS Query is a little harder to find -

it's
on the Tools menu
in the top right hand corner of the Select Data

Source
dialog. (You may also
find you need to install MS Query to get what you

want).

In both Word 2000 and 2002, you should only need one

DSN
to allow you to use
all the tables and queries in your database that are
accessible via ODBC.
But you will need to go through the MS Query route

to
choose the table/query
or define your own.

An alternative to all this is to write, or macro

record,
a short macro
containing an OpenDataSource command that uses an

ODBC
connection string in
the Connection parameter. Then you have to work out

the
corrct SQL to get
the data you want, and insert it in the SQLStatement
parameter, then run the
macro. You will find examples I've posted if you

search
this newsgroup, e.g.
using Google. One of the problems here is that Word

2002
requires an
additional parameter (Subtype) if you want to

connect
via
ODBC.

If you are /only/ concerned with Word 2002 and

prefer
to
use OLEDB, there
are a couple of ways you can proceed without

creating
an
ODBC DSN.
a. use a .udl file. If you create a Notepad (.txt)

file
and rename it to
something.udl, then double-click on the file name,

you
should see the
Datalink editor. This is like, if not identical to,

the
editor you used to
select your DSN in your point 2. However,
a. in the Provider tab, select the Jet provider

rather
than the ODBC
provider
b. in the Connection tab, you may need to check

Blank
password and Allow
saving password
c. in the All tab, you will need to edit the

valueJet
OLEDB:System databse
to be the pathname of your workgroup file.
d. When you click OK, you do not see the questions

that
come at the end of
the dialog you used to set up your .odc

If you select this file as your data source, you

should
be presented with a
list of tables, so you only really need one .udl for

all
your documents. It
may be possible to use it to connect to a specific

table
or query, but
that's for you to discover if you want.

Alternatively, you can set up a .odc using much the

same
process you used
before in your point (2), but choose the

Other/Advanced
option instead of
ODBC DSN. Then fill in the datalink dialog options

in
the
same way as for
the .udl file above. When you get to save the .odc,

there
is a checkbox that
lets you specify whether your .odc should cause

Word to
display a list of
tables/queries within your .mdb, or whether Word

should
connect to a
prespecified table/query. Again, if you use the

former,
you should be able
to use a single .odc for all your mailmerge

documents.

--
Peter Jamieson

"Tony_VBACoder"


wrote in message
...
I am new at setting up a Word document as a Mail

Merge
to
an Access Database. My situation involves a

secured
Access 2000/2002 Database, with a Word 2000/2002

Mail
Merge Letter. My Access Databases are secured

with a
MDW
Security File required to open my Database. I

have
various queries within my Access database are

specific
for
each Word Mail Merge document. In my Access DB
workgroup
security file, I have created a user

called "WordUser"
whose sole purpose is to be able to run the Word

Mail
Merge Letters. This user has no password and only
has "Read" permission on all the tables/queries

required
for each letter and nothing else. I have had no

luck
being able to get my Word 2002 letter to connect

to
my
secured Access database with the Mail Merge Wizard
within
Word (I have posted a few questions in the

newsgroups,
but
have not gotten any responses). So instead, I

have
tried
to go the ODBC route, where I would create a new

DSN
entry
that points to my Access database, in hopes that I

could
use this ODBC entry as my datasource for my Word

Mail
Merge document. However, this method has also

raised
some
questions that I hope someone can answer for me or

help
in
the right direction.

1) To create a new ODBC entry, I do the following:
a) click on "System DSN" Tab in the ODBC Data

Source
Administrator
b) click the "Add" button
c) select "Microsoft Access Driver (*.mdb)"
d) click the "Finish" button
e) In the "ODBC Microsoft Access Setup" Form I:
f) enter "MyDB" in the "Data Source Name:" text

box
g) click the "Select..." button in

the "Database"
Frame
to located my Access Database
h) click the "Advanced..." button
i) enter my "WordUser" in the "Login name:"

text
box
in
the "Set Advanced Options" form and click the "OK"
button.
j) select the "Database:" radio button in

the "System
Database" frame
k) click the "System Database..." button to

select my
MDW file for my secured database
l) click the "OK" button on the ODBC Microsoft

Access
Setup form to complete setting up my new ODBC

entry

2) Now, once my ODBC entry has been created, I go

to my
Word 2002/2000 letter and select ToolsLetters and
MailingsMail Merge Wizard. At the Wizard's Step

#3, I
select "Browse" from the "Use an existing list"

option.
This brings up the "Select Data Source" dialog

box.
Here,
the "My Data Sources" is selected in the "Look

in:"
combo
box and all my ODBC entries are showing EXCEPT my

newly
created Access ODBC entry I created in my above

steps
(a-
l). However, there are 2 entries that I am not

familiar
with: "+Connect to New Data Source.odc" and "+New

SQL
Server Connection.odc". I selected the "+Connect

to
New
Data Source.odc" option and it took me into a

new "Data
Connection Wizard" where I did the following:
a) Select "ODBC DSN" from the "What kind of

data
source
do you want to connect to?" list and clicked

the "Next"
button
b) Now my newly created ODBC entry shows up in
the "ODBC data sources" list. I selected it and

clicked
the "Next" button
c) At this point, all my tables and queries

were
listed. From the list, I selected my query for

the
letter
I am creating and clicked the "Next" button.
d) Now, the "Save Data Connection File and

Finish"
dialog box is shown, prompting me to save this as

a
connection file (*.odc). I entered a name and

clicked
the "Finish" button.

Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database,

by
having to create a separate Connection File for

each
Mail
Merge Letter I am wanting to create?




.



.



.

  #8  
Old September 2nd, 2004, 09:21 PM
Peter Jamieson
external usenet poster
 
Posts: n/a
Default

After doing a little testing (though not enough to be
completely satisfied yet...), I did find that if I go into
my ODBC entry and click the "Configure" button, and if I
check the "Read Only" check box from the "Options" button,
I don't get the "Database has been placed in a state by
user 'bob' on machine 'mach123' that prevents it from
being opened or locked" error.


Thanks, useful to know that.

As for the Parameter Query method and using DDE, how do I
go about setting this up?


In Word 2000 and earlier, check the Select method option in Open Data Source
and select the DDE method when offered. In Word 2002 and later, check
Tools|Options|General|"Confirm conversions at open" (as before), select the
..mdb in Select Data Source and the DDE option when offered.

When Word opens the document, it will start Access and request it to open
the database. Access should prompt for login/password as usual, but notice
that the Access dialog boxes may appear "behind" Word so you may need to
Alt-tab to reach them). Access runs the query and will prompt for any
parameters.

The trouble with this is that you now have two applications open and
communicating and that usually spells additional trouble if you are creating
an application for others to use.

That assumes you are using normal Access parameter queries. Although that's
the only way you can use such queries in mailmerge using the built-in
facilities, it isn't the only possible way you chould do things if you are
using VBA. With VBA you could decide what information to get from the user,
prompt using a Userform (say), construct your own query and issue it via
OpenDataSource. Using ADO/ADOX or DAO, you could probably retrieve the text
of an Access query (including the question marks used as placeholders for
parameters) and use userforms or inputboxes to solicit the relevant values
from the user, then issue that query using OpenDataSource. Never tried it
myself and can see that in the general case a lot of work might be involved,
but as long as the queries do not exceed Word's length limits (around
255/511 characters) I think the approach could be made to work if DDE won't
do what you really need.

--
Peter Jamieson

"Tony_VBACoder" wrote in message
...
After doing a little testing (though not enough to be
completely satisfied yet...), I did find that if I go into
my ODBC entry and click the "Configure" button, and if I
check the "Read Only" check box from the "Options" button,
I don't get the "Database has been placed in a state by
user 'bob' on machine 'mach123' that prevents it from
being opened or locked" error.

As for the Parameter Query method and using DDE, how do I
go about setting this up?

Thank you for all your insight.

-----Original Message-----
FWIW,
a. for parameter queries, you have to use DDE because it

is Access that
displays the pop-ups that solicit the parameter values

from the user, and
DDE is the only connection method that actually starts

Access.
b. although you can /probably/ specify the access mode

Word will use when
using a .udl, .odc or ODBC Connection parameter in VBA

(there is a tab for
this in the Datalink editor),
- it obviously won't help if someone else is opening

the database in an
incompatible sharing mode (e.g. some sort of exclsive

access mode)
- I get the impression that by default, when Word

connects to a .mdb
using OLEDB, it uses a more exclusive mode than it really

should. I'm not
completely convinced that anything you specify in a .odc

will change that.
But I don't know the facts on that one.

--
Peter Jamieson

"Tony_VBACoder"

wrote in message
...
Peter, thanks for all your help.

I was able to find the MS Query button and locate my
correct Query, and everything is working great.

There are still a couple of other issues that I am

running
into in terms of wanting to pull data from a Parameter
Query in Access, and I am also running into the "The
Database has been placed in a state by user 'bob' on
machine 'mach123' that prevents it from being opened or
locked" error. But I will be posting these as separate
issues as soon as I do a little more testing so that I

can
properly post what is going on.

Thanks again.


-----Original Message-----
Here, you first said that I should go the ODBC route

if
I
want to get this to work in both Word 2000 and Word
2002,
but then you go onto say that an "ODBC connection

set up
in Word 2000 are discarded when you open the .doc in
Word
2002". I am a little confused here. Will this

method
(ODBC) work in both Word 2000 and Word 2002?

WHat I mean is that the OLEDB connection is simply not
available in Word
2000. So the only option (other than DDE) that will

work
in both is ODBC.
However, that does not mean that Word 2002 will

correctly
recognise an ODBC
data source which has been set up in Word 2000 and vice
versa. So...

I will be
needing this work with both versions, where I will
probably end of creating the final Word Mail

Merge .doc
using Word 2000 because about 90% of the users have
Office
2000, whereas the rest of Office XP (Word 2002).

....you /may/ need to create Word 2000 and Word 2002
versions of each mail
merge app. I can't remember off the top of my head so

you
will need to
check. I would also take a bit of time to ensure that
your users can do what
they need - e.g. if they need to apply their own
filters/sorting, you may
need to confirm that this approach allows them to do

so.

In regards to using ODBC, you

mentioned "Unfortunately,
the route you have taken in your example actually

uses
OLEDB, using the ODBC provider for OLEDB". If I

wanted
to
use the ODBC method, what should I have done so that

I
can
be certain that I am using the OLEDB method?

Assuming you meant "certain that I am using the ODBC
method", then you
either need to go via MS Query, or go the VBA
OpenDataSource route, or check
Word Tools|Options|General|"Confirm conversions at

open",
go through the
Select Data Source dialog, pick your .mdb, then select
the User/System DSN
from the list of possible connection options. If you
don't see it there, you
will need to use MS Query or VBA.

--
Peter Jamieson

"Tony_VBACoder"
wrote in message
...
Peter, thank you for all your insight...it will take

me
a
little while to absorb it and test it out with both

Word
2000 and 2002. I do have one question in regards to
something you mentioned:

First, if you want to connect the same way in Word
2000
and Word 2002, you have to use ODBC (or the old DDE
method).....But in addition, you may find that ODBC
connections set up in Word 2000 are discarded when

you
open the .doc in WOrd 2002 and/or vice versa.

Here, you first said that I should go the ODBC route

if
I
want to get this to work in both Word 2000 and Word
2002,
but then you go onto say that an "ODBC connection

set up
in Word 2000 are discarded when you open the .doc in
Word
2002". I am a little confused here. Will this

method
(ODBC) work in both Word 2000 and Word 2002? I will

be
needing this work with both versions, where I will
probably end of creating the final Word Mail

Merge .doc
using Word 2000 because about 90% of the users have
Office
2000, whereas the rest of Office XP (Word 2002).

In regards to using ODBC, you

mentioned "Unfortunately,
the route you have taken in your example actually

uses
OLEDB, using the ODBC provider for OLEDB". If I

wanted
to
use the ODBC method, what should I have done so that

I
can
be certain that I am using the OLEDB method?

Thanks


-----Original Message-----
Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database,

by
having to create a separate Connection File for

each
Mail
Merge Letter I am wanting to create?

IMO there is no "proper" way. At best, there may be

a
way
that works for
your application and minimises problems if you need

to
distribute your
solution to mutliple systems.

There are a number of issues here and unfortunately

I
don't think I can give
you reliable suggestions in all cases, but here

goes.

First, if you want to connect the same way in Word

2000
and Word 2002, you
have to use ODBC (or the old DDE method). OLEDB is

only
supported in Word
2002 and later. Unfortunately, the route you have

taken
in your example
actually uses OLEDB, using the ODBC provider for

OLEDB.
Any connection that
uses a .odc (or .udl, as we will see) is actually

using
OLEDB. So those
connections won't work with Word 2000. But in

addition,
you may find that
ODBC connections set up in Word 2000 are discarded

when
you open the .doc in
WOrd 2002 and/or vice versa.

This brings up the "Select Data Source" dialog

box.
Here,
the "My Data Sources" is selected in the "Look

in:"
combo
box and all my ODBC entries are showing EXCEPT my
newly
created Access ODBC entry I created in my above

steps
(a-

The reason (probably) that some ODBC entries appear
here
and some do not is
that there are three types of ODBC DSN, as follows:

FIle DSNs: these are just text files in "INI" format
that
contain the
connection information you provided in the ODBC
administrator. Typically
they have a .dsn extension. These are probably the

ones
you can see

Machine DSNs: these DSNs are not stored in text

files.
They are stored in
the Windows registry, so I do not think you will see
them
listed in "My Data
Sources". There are two types of Machine DSN:
a. User DSNs. These are intended to be visible to a
single user on a
machine which may have several different users. They
are
stored in the
per-user part of the registry.
b. System DSNs (the type you created). These are
intended to be visible to
all users on a particular machine.

Neither Word 2000 nor Word 2002, as far as I know,

lets
you open a .dsn file
directly (in the Open/Select Data Source dialog

box).
Word just tries to get
the data directly from the .dsn file, not from the
database it refers to. If
you are trying to open a file data source such as
a .mdb
in Word 2000, you
can select the .mdb in the Open Data Source dialog,
check "Select method",
and click Open. If the DSN is a Machine DSN you will
probably see it in the
list of connection methods. Otherwise, you have to

use
MS
Query to select
the DSN (of any type) and set up the connection. You
can
do the same in Word
2002, although MS Query is a little harder to find -
it's
on the Tools menu
in the top right hand corner of the Select Data

Source
dialog. (You may also
find you need to install MS Query to get what you
want).

In both Word 2000 and 2002, you should only need one
DSN
to allow you to use
all the tables and queries in your database that are
accessible via ODBC.
But you will need to go through the MS Query route

to
choose the table/query
or define your own.

An alternative to all this is to write, or macro
record,
a short macro
containing an OpenDataSource command that uses an

ODBC
connection string in
the Connection parameter. Then you have to work out

the
corrct SQL to get
the data you want, and insert it in the SQLStatement
parameter, then run the
macro. You will find examples I've posted if you

search
this newsgroup, e.g.
using Google. One of the problems here is that Word
2002
requires an
additional parameter (Subtype) if you want to

connect
via
ODBC.

If you are /only/ concerned with Word 2002 and

prefer
to
use OLEDB, there
are a couple of ways you can proceed without

creating
an
ODBC DSN.
a. use a .udl file. If you create a Notepad (.txt)
file
and rename it to
something.udl, then double-click on the file name,

you
should see the
Datalink editor. This is like, if not identical to,

the
editor you used to
select your DSN in your point 2. However,
a. in the Provider tab, select the Jet provider

rather
than the ODBC
provider
b. in the Connection tab, you may need to check

Blank
password and Allow
saving password
c. in the All tab, you will need to edit the

valueJet
OLEDB:System databse
to be the pathname of your workgroup file.
d. When you click OK, you do not see the questions
that
come at the end of
the dialog you used to set up your .odc

If you select this file as your data source, you

should
be presented with a
list of tables, so you only really need one .udl for
all
your documents. It
may be possible to use it to connect to a specific
table
or query, but
that's for you to discover if you want.

Alternatively, you can set up a .odc using much the
same
process you used
before in your point (2), but choose the

Other/Advanced
option instead of
ODBC DSN. Then fill in the datalink dialog options

in
the
same way as for
the .udl file above. When you get to save the .odc,
there
is a checkbox that
lets you specify whether your .odc should cause

Word to
display a list of
tables/queries within your .mdb, or whether Word

should
connect to a
prespecified table/query. Again, if you use the

former,
you should be able
to use a single .odc for all your mailmerge

documents.

--
Peter Jamieson

"Tony_VBACoder"


wrote in message
...
I am new at setting up a Word document as a Mail
Merge
to
an Access Database. My situation involves a

secured
Access 2000/2002 Database, with a Word 2000/2002

Mail
Merge Letter. My Access Databases are secured

with a
MDW
Security File required to open my Database. I

have
various queries within my Access database are
specific
for
each Word Mail Merge document. In my Access DB
workgroup
security file, I have created a user
called "WordUser"
whose sole purpose is to be able to run the Word

Mail
Merge Letters. This user has no password and only
has "Read" permission on all the tables/queries
required
for each letter and nothing else. I have had no

luck
being able to get my Word 2002 letter to connect

to
my
secured Access database with the Mail Merge Wizard
within
Word (I have posted a few questions in the
newsgroups,
but
have not gotten any responses). So instead, I

have
tried
to go the ODBC route, where I would create a new

DSN
entry
that points to my Access database, in hopes that I
could
use this ODBC entry as my datasource for my Word

Mail
Merge document. However, this method has also

raised
some
questions that I hope someone can answer for me or
help
in
the right direction.

1) To create a new ODBC entry, I do the following:
a) click on "System DSN" Tab in the ODBC Data
Source
Administrator
b) click the "Add" button
c) select "Microsoft Access Driver (*.mdb)"
d) click the "Finish" button
e) In the "ODBC Microsoft Access Setup" Form I:
f) enter "MyDB" in the "Data Source Name:" text
box
g) click the "Select..." button in

the "Database"
Frame
to located my Access Database
h) click the "Advanced..." button
i) enter my "WordUser" in the "Login name:"

text
box
in
the "Set Advanced Options" form and click the "OK"
button.
j) select the "Database:" radio button in
the "System
Database" frame
k) click the "System Database..." button to
select my
MDW file for my secured database
l) click the "OK" button on the ODBC Microsoft
Access
Setup form to complete setting up my new ODBC

entry

2) Now, once my ODBC entry has been created, I go
to my
Word 2002/2000 letter and select ToolsLetters and
MailingsMail Merge Wizard. At the Wizard's Step
#3, I
select "Browse" from the "Use an existing list"
option.
This brings up the "Select Data Source" dialog

box.
Here,
the "My Data Sources" is selected in the "Look

in:"
combo
box and all my ODBC entries are showing EXCEPT my
newly
created Access ODBC entry I created in my above

steps
(a-
l). However, there are 2 entries that I am not
familiar
with: "+Connect to New Data Source.odc" and "+New

SQL
Server Connection.odc". I selected the "+Connect

to
New
Data Source.odc" option and it took me into a
new "Data
Connection Wizard" where I did the following:
a) Select "ODBC DSN" from the "What kind of

data
source
do you want to connect to?" list and clicked
the "Next"
button
b) Now my newly created ODBC entry shows up in
the "ODBC data sources" list. I selected it and
clicked
the "Next" button
c) At this point, all my tables and queries

were
listed. From the list, I selected my query for

the
letter
I am creating and clicked the "Next" button.
d) Now, the "Save Data Connection File and

Finish"
dialog box is shown, prompting me to save this as

a
connection file (*.odc). I entered a name and
clicked
the "Finish" button.

Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database,

by
having to create a separate Connection File for

each
Mail
Merge Letter I am wanting to create?




.



.



.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using a WORD document as a subreport or report with mail merge Bob Reynolds General Discussion 3 August 15th, 2004 07:59 PM
Access 2000 to 2003 mail merge to word JJ Running & Setting Up Queries 1 June 23rd, 2004 06:41 AM
Mail Merge to Fax - Word 2003 Paul General Discussion 1 June 9th, 2004 02:19 PM
mail merge - how to insert a word file in main document as a filed Manikrishna Mailmerge 1 May 7th, 2004 10:13 AM
Word 2003 is adding duplicate records to my mail merge Doug Robbins - Word MVP Mailmerge 0 April 24th, 2004 03:58 AM


All times are GMT +1. The time now is 04:53 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.