I would like to reference an mdb file for a mail merge. However, the db is
secured with an mdw file. I've tried referencing the workgroup file, but was
unsuccessful. I would imagine it would work if I gave the Access "Admin"
userid read access. Is there a way to do the merge without making changes to
the workgroup security settings? Thanks!
--
Roger

Which version of Word, and how did you try to reference the workgroup file?

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:=wdMergeSubtypeWord200*0, at least when using Machine DSNs

Examples of OpenDataSource calls:-

With Machine DSN

ActiveDocument.MailMerge.OpenD*ataSource _
Name:="", _
Connection:="DSN=mydsn;DBQ=c:\*mydbs\x.mdb;" _
&
"SystemDB=c:\mywgdb\Secured.md*w;UID=myloginname;P WD=mypasswo*rd", _
SQLStatement:="SELECT * FROM `mytable`"

With File DSN

ActiveDocument.MailMerge.OpenD*ataSource _
Name:="c:\mydsns\x.dsn", _
Connection:="FILEDSN=c:\mydsns*\x.dsn;DBQ=c:\mydbs \x.mdb;" _
&
"SystemDB=c:\mywgdb\Secured.md*w;UID=myloginname;P WD=mypasswo*rd", _
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.

"Roger" wrote in message
...
I would like to reference an mdb file for a mail merge. However, the db is
secured with an mdw file. I've tried referencing the workgroup file, but
was
unsuccessful. I would imagine it would work if I gave the Access "Admin"
userid read access. Is there a way to do the merge without making changes
to
the workgroup security settings? Thanks!
--
Roger

Thank you for the detailed answer! I'm using Word 2003 and it is obvious I
referenced the mdw file incorrectly. You have given me enough info to move
forward. Thanks again!

"Peter Jamieson" wrote:

Which version of Word, and how did you try to reference the workgroup file?

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:=wdMergeSubtypeWord200Â*0, at least when using Machine DSNs

Examples of OpenDataSource calls:-

With Machine DSN

ActiveDocument.MailMerge.OpenDÂ*ataSource _
Name:="", _
Connection:="DSN=mydsn;DBQ=c:\Â*mydbs\x.mdb;" _
&
"SystemDB=c:\mywgdb\Secured.mdÂ*w;UID=myloginname; PWD=mypasswoÂ*rd", _
SQLStatement:="SELECT * FROM `mytable`"

With File DSN

ActiveDocument.MailMerge.OpenDÂ*ataSource _
Name:="c:\mydsns\x.dsn", _
Connection:="FILEDSN=c:\mydsnsÂ*\x.dsn;DBQ=c:\mydb s\x.mdb;" _
&
"SystemDB=c:\mywgdb\Secured.mdÂ*w;UID=myloginname; PWD=mypasswoÂ*rd", _
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.

"Roger" wrote in message
...
I would like to reference an mdb file for a mail merge. However, the db is
secured with an mdw file. I've tried referencing the workgroup file, but
was
unsuccessful. I would imagine it would work if I gave the Access "Admin"
userid read access. Is there a way to do the merge without making changes
to
the workgroup security settings? Thanks!
--
Roger