Connecting to Access

I have documents set up and working for mail merge run from Access 2003
through VBA to Word 2003. I want to set up a new document but cannot
get Word to connect to the Access database to pick up the table in
which the data will be stored.. When I go through the Open Data Source/
New Source dialogue, specifying the database to be used, the sign on is
rejected because the workgroup file is not found. How do I tell the
system where the workgroup file is held, or where can I put it so that
it will be found?




--
Murray Muspratt-Rouse

You need to do one of the following:
a. specify the workgroup database name, and the appropriate user name
and password, in a connection string in VBA, e.g.

ActiveDocument.MailMerge.OPenDataSource _
Name:="the pathname of your DB", _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password=thepass word;" & _
"User ID=theuserID;Data Source=the pathname of your DB;" & _
"Jet OLEDB:System Database=the pathname of your workgroup DB;", _
SQLStatement:="SELECT * FROM [your table]

b. (you can probably do it by putting the same information in a .odc file)

Notice that in either case, the username/password info will probably end
up being rather easy to discover (in plain text in the .odc, and inside
the Word file in case (a))

Peter Jamieson

http://tips.pjmsn.me.uk

On 02/03/2010 18:55, Murray Muspratt-Rouse wrote:
I have documents set up and working for mail merge run from Access 2003
through VBA to Word 2003. I want to set up a new document but cannot
get Word to connect to the Access database to pick up the table in
which the data will be stored.. When I go through the Open Data Source/
New Source dialogue, specifying the database to be used, the sign on is
rejected because the workgroup file is not found. How do I tell the
system where the workgroup file is held, or where can I put it so that
it will be found?

Peter Jamieson;459888 Wrote:
You need to do one of the following:
a. specify the workgroup database name, and the appropriate user name

and password, in a connection string in VBA, e.g.

ActiveDocument.MailMerge.OPenDataSource _
Name:="the pathname of your DB", _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password=thepass word;" & _
"User ID=theuserID;Data Source=the pathname of your DB;" & _
"Jet OLEDB:System Database=the pathname of your workgroup DB;", _
SQLStatement:="SELECT * FROM [your table]

b. (you can probably do it by putting the same information in a .odc
file)

Notice that in either case, the username/password info will probably
end
up being rather easy to discover (in plain text in the .odc, and inside

the Word file in case (a))

Peter Jamieson

http://tips.pjmsn.me.uk

Peter, I already have VBA working in Access to run the merge. My
problem is in setting up a document to point at the Access table as the
source of the mail merge data. I have tried copying one of the documents
that does point at the same table in the hope that it would still do so,
but the copy points at an Excel spreadsheet of the same name.

Do I need to rename my workgroup information file, Secured.mdw, to
System.mdw? I suspect that the New Data Source process will only look
at a file of that name.




--
Murray Muspratt-Rouse

Peter, I already have VBA working in Access to run the merge. My
problem is in setting up a document to point at the Access table as the
source of the mail merge data. I have tried copying one of the documents
that does point at the same table in the hope that it would still do so,
but the copy points at an Excel spreadsheet of the same name.

Do I need to rename my workgroup information file, Secured.mdw, to
System.mdw? I suspect that the New Data Source process will only look
at a file of that name.

Since Access is definitely open when you merge, your options a
a. connect using DDE. DDE communicates with Access, which already has
the DB open, so Word does not need to know anything about workgroup
security in that case. But I do not think it is possible to force Word
2007 to use DDE with Access programmatically
b. connect via ODBC/OLE DB, supplying the workgroup name, login and
password, as I have suggested. OLE DB is probably better. I do not
believe that the name of the workgroup DB makes any difference - you
still have to supply it. However, I have not verified that recently.
c. (I would have to re-check this) ensure that the table/query you
wanted to use was /not secured/. In that case you probably do not need
to provide the workgroup DB details, and Word will /probably/ try to log
in as Admin.

Peter Jamieson

http://tips.pjmsn.me.uk

On 03/03/2010 08:02, Murray Muspratt-Rouse wrote:
Peter Jamieson;459888 Wrote:
You need to do one of the following:
a. specify the workgroup database name, and the appropriate user name

and password, in a connection string in VBA, e.g.

ActiveDocument.MailMerge.OPenDataSource _
Name:="the pathname of your DB", _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password=thepass word;"& _
"User ID=theuserID;Data Source=the pathname of your DB;"& _
"Jet OLEDB:System Database=the pathname of your workgroup DB;", _
SQLStatement:="SELECT * FROM [your table]

b. (you can probably do it by putting the same information in a .odc
file)

Notice that in either case, the username/password info will probably
end
up being rather easy to discover (in plain text in the .odc, and inside

the Word file in case (a))

Peter Jamieson

http://tips.pjmsn.me.uk

Peter, I already have VBA working in Access to run the merge. My
problem is in setting up a document to point at the Access table as the
source of the mail merge data. I have tried copying one of the documents
that does point at the same table in the hope that it would still do so,
but the copy points at an Excel spreadsheet of the same name.

Do I need to rename my workgroup information file, Secured.mdw, to
System.mdw? I suspect that the New Data Source process will only look
at a file of that name.

Since Access is definitely open when you merge, your options a
a. connect using DDE. DDE communicates with Access, which already has

the DB open, so Word does not need to know anything about workgroup
security in that case. But I do not think it is possible to force Word

2007 to use DDE with Access programmatically
b. connect via ODBC/OLE DB, supplying the workgroup name, login and
password, as I have suggested. OLE DB is probably better. I do not
believe that the name of the workgroup DB makes any difference - you
still have to supply it. However, I have not verified that recently.
c. (I would have to re-check this) ensure that the table/query you
wanted to use was /not secured/. In that case you probably do not need

to provide the workgroup DB details, and Word will /probably/ try to
log
in as Admin.

Peter Jamieson

[url]http://tips.pjmsn.me.uk[
In the interval I have resorted to DDE and have managed to export an
Excel spreadsheet containing the merge data. I did try to complete the
automation by opening the document from Access VBA but only got one
letter instead of many. Since envelopes are to be printed from the same
data I will leave it like that for now - the user will run the data
collection in Access and then open the documents in Word for printing.

Thanks again for your help.

Murray




--
Murray Muspratt-Rouse