I am currently running Word and Access 2003. I recently added user level
security (login and password) to one of my Databases. I implemented security
mainly to ensure that only selected users can edit data. All users have read
permissions. Now when I use a query as a datasource, Word gives me a message
that it cannot connect to the datasource. I used the query as a datasource
before I added security, but now it doesn't work.
Does anyone know what is wrong or how I can keep user level security in
Access and use this merge?
Thank you,
-josh

Here are a few notes I wrote a few months ago. These
notes definitely apply to Word 2000 but things may be significantly
different in later versions (Word 2003 in particular).

The main points a
a. I do not think you can connect to a workgroup-database secured .mdb
using DDE (i.e. the default connection method in Word 2000 and earlier)
b. you can connect using ODBC, but if you want to connect using the Word
user interface or MS Query, you must use an ODBC DSN (Data Source Name) that
specifies the workgroup database. If you do not, neither Word nor MS Query
will let you connect at all.
c. if you do not also specify a login name and password in the DSN, Word
should prompt you for your login details when you first connect.
d. you can connect programmatically using VBA and a suitable connect
string, again specifying a DSN, the login name and password. In this case
you do not have to create a new DSN - you can use the default "MS Access
Database" DSN and add the necessary information about the workgroup file in
the connection string.

As for DSNs, there are three types:
a. User DSNs, available to a particular user on a particular PC
b. System DSNs, available to all users on a particular PC
c. File DSNs, available to anyone with access to the .dsn file (e.g. it can
be on a network share) with the proviso that the ODBC driver referenced by
the .dsn must be installed on the system that is attempting to use that
..dsn.

The settings for User and File DSNs are stored in the Windows registry and
are collectively known as Machine DSNs.

All types of DSN can be created using the ODBC Administrator. This can
typically be found in Control Panel, and in Win2K/WinXP you need to look in
the Administrative Tools folder. In principal you could modify the default
DSN for Access ("MS Access Database") to specify a particular workgroup file
etc., but personally I think it is better practice to leave default values
as they are and create a new DSN.

When you create an Access DSN for use with a workgroup file, you can use the
Advanced... button to specify a login name and password. However, notice
that when you create a /file/ DSN using this method, the ODBC Administrator
saves the login name but not the login password. You can edit the file in
Notepad to add

PWD=thepassword

if you want.

You can also create /file/ DSNs in MS Query, and in this case the password
will be saved if you check the appropriate button.

If you decide to use the Word VBA OpenDataSource method to open an ODBC
Access data source, you need 3 parameters:
a. set Name to "" for Machine DSNs, and to the full path name of the .dsn
file for file DSNs (actually, it does not have to be to this file but it is
the easiest way to do it)
b. set Connection to "DSN=theDSNname;" for Machine DSNs, and to
"FILEDSN=thefullpathnameof theDSNfile;" for File DSNs. Append any
information not contained in the DSN.
c. set SQLStatement to the SQL query string you need. If it is a long
string, you may need to use SQLStatement1 as well, in which case just split
the SQL statement at any part but ensure that the two strings concatenated
constitute valie SQL (i.e. make sure there is a space at the end of
SQLStatement or the beginning of SQLStatement1 is one is syntactically
required.
d. in Word 2002 and later you will need to add the parameter
Subtype:=wdMergeSubtypeWord2000, at least when using Machine DSNs

Examples of OpenDataSource calls:-

With Machine DSN

ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:="DSN=mydsn;DBQ=c:\mydbs\x.mdb;" _
&
"SystemDB=c:\mywgdb\Secured.mdw;UID=myloginname;PW D=mypassword", _
SQLStatement:="SELECT * FROM `mytable`"

With File DSN

ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\mydsns\x.dsn", _
Connection:="FILEDSN=c:\mydsns\x.dsn;DBQ=c:\mydbs\ x.mdb;" _
&
"SystemDB=c:\mywgdb\Secured.mdw;UID=myloginname;PW D=mypassword", _
SQLStatement:="SELECT * FROM `mytable`"

If you are using Word 2002 or later, you should still be able to use ODBC.
But you can also connect using OLEDB, and in that case what you need is
either a .udl file with an OLEDB connection string that specifies the
workgroup database etc., or a .odc file containing much the same thing. You
should be able to create a suitable .odc file using the "New Source" button
in the Word Select Data Source dialog box. If you try to open the .mdb by
specifying the .mdb as the data source, you will see a dialog box similar to
the one used to create a .odc, but beware, because filling this dialog in
will not have the same effect as creating a .odc and using that as the data
source.

Peter Jamieson
"Josh" wrote in message
...
I am currently running Word and Access 2003. I recently added user level
security (login and password) to one of my Databases. I implemented
security
mainly to ensure that only selected users can edit data. All users have
read
permissions. Now when I use a query as a datasource, Word gives me a
message
that it cannot connect to the datasource. I used the query as a
datasource
before I added security, but now it doesn't work.
Does anyone know what is wrong or how I can keep user level security in
Access and use this merge?
Thank you,
-josh

Peter,
Thanks for the tips. I was able to connect using DDE. I figured out my
problem was that my user group didn't have permission to open the database.
Thanks again,
Josh

"Peter Jamieson" wrote:

Here are a few notes I wrote a few months ago. These
notes definitely apply to Word 2000 but things may be significantly
different in later versions (Word 2003 in particular).

The main points a
a. I do not think you can connect to a workgroup-database secured .mdb
using DDE (i.e. the default connection method in Word 2000 and earlier)
b. you can connect using ODBC, but if you want to connect using the Word
user interface or MS Query, you must use an ODBC DSN (Data Source Name) that
specifies the workgroup database. If you do not, neither Word nor MS Query
will let you connect at all.
c. if you do not also specify a login name and password in the DSN, Word
should prompt you for your login details when you first connect.
d. you can connect programmatically using VBA and a suitable connect
string, again specifying a DSN, the login name and password. In this case
you do not have to create a new DSN - you can use the default "MS Access
Database" DSN and add the necessary information about the workgroup file in
the connection string.

As for DSNs, there are three types:
a. User DSNs, available to a particular user on a particular PC
b. System DSNs, available to all users on a particular PC
c. File DSNs, available to anyone with access to the .dsn file (e.g. it can
be on a network share) with the proviso that the ODBC driver referenced by
the .dsn must be installed on the system that is attempting to use that
..dsn.

The settings for User and File DSNs are stored in the Windows registry and
are collectively known as Machine DSNs.

All types of DSN can be created using the ODBC Administrator. This can
typically be found in Control Panel, and in Win2K/WinXP you need to look in
the Administrative Tools folder. In principal you could modify the default
DSN for Access ("MS Access Database") to specify a particular workgroup file
etc., but personally I think it is better practice to leave default values
as they are and create a new DSN.

When you create an Access DSN for use with a workgroup file, you can use the
Advanced... button to specify a login name and password. However, notice
that when you create a /file/ DSN using this method, the ODBC Administrator
saves the login name but not the login password. You can edit the file in
Notepad to add

PWD=thepassword

if you want.

You can also create /file/ DSNs in MS Query, and in this case the password
will be saved if you check the appropriate button.

If you decide to use the Word VBA OpenDataSource method to open an ODBC
Access data source, you need 3 parameters:
a. set Name to "" for Machine DSNs, and to the full path name of the .dsn
file for file DSNs (actually, it does not have to be to this file but it is
the easiest way to do it)
b. set Connection to "DSN=theDSNname;" for Machine DSNs, and to
"FILEDSN=thefullpathnameof theDSNfile;" for File DSNs. Append any
information not contained in the DSN.
c. set SQLStatement to the SQL query string you need. If it is a long
string, you may need to use SQLStatement1 as well, in which case just split
the SQL statement at any part but ensure that the two strings concatenated
constitute valie SQL (i.e. make sure there is a space at the end of
SQLStatement or the beginning of SQLStatement1 is one is syntactically
required.
d. in Word 2002 and later you will need to add the parameter
Subtype:=wdMergeSubtypeWord2000, at least when using Machine DSNs

Examples of OpenDataSource calls:-

With Machine DSN

ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:="DSN=mydsn;DBQ=c:\mydbs\x.mdb;" _
&
"SystemDB=c:\mywgdb\Secured.mdw;UID=myloginname;PW D=mypassword", _
SQLStatement:="SELECT * FROM `mytable`"

With File DSN

ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\mydsns\x.dsn", _
Connection:="FILEDSN=c:\mydsns\x.dsn;DBQ=c:\mydbs\ x.mdb;" _
&
"SystemDB=c:\mywgdb\Secured.mdw;UID=myloginname;PW D=mypassword", _
SQLStatement:="SELECT * FROM `mytable`"

If you are using Word 2002 or later, you should still be able to use ODBC.
But you can also connect using OLEDB, and in that case what you need is
either a .udl file with an OLEDB connection string that specifies the
workgroup database etc., or a .odc file containing much the same thing. You
should be able to create a suitable .odc file using the "New Source" button
in the Word Select Data Source dialog box. If you try to open the .mdb by
specifying the .mdb as the data source, you will see a dialog box similar to
the one used to create a .odc, but beware, because filling this dialog in
will not have the same effect as creating a .odc and using that as the data
source.

Peter Jamieson
"Josh" wrote in message
...
I am currently running Word and Access 2003. I recently added user level
security (login and password) to one of my Databases. I implemented
security
mainly to ensure that only selected users can edit data. All users have
read
permissions. Now when I use a query as a datasource, Word gives me a
message
that it cannot connect to the datasource. I used the query as a
datasource
before I added security, but now it doesn't work.
Does anyone know what is wrong or how I can keep user level security in
Access and use this merge?
Thank you,
-josh