Reply
 
Thread Tools Display Modes
  #1   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default 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?

Last edited by Murray Muspratt-Rouse : March 2nd 10 at 07:51 PM Reason: Missing data
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Connecting to Access

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?




  #3   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

[quote=Peter Jamieson;459888]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.

Are you suggesting that I put the VBA code in a module in the document?

Last edited by Murray Muspratt-Rouse : March 3rd 10 at 10:01 AM
  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Connecting to Access

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.





  #5   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Smile

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
Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
re-connecting tables [email protected] Tables 7 October 27th 09 07:22 PM
Connecting lines Richard Microsoft Word Help 1 October 8th 08 01:01 PM
error messages when connecting to an MS Access query Paul Mailmerge 4 February 23rd 08 12:43 AM
MS Access Database via DDE Not connecting George M Mailmerge 3 June 27th 07 02:46 PM
connecting files Papa Jonah Microsoft Word Help 3 February 28th 07 03:40 PM


All times are GMT +1. The time now is 01:40 PM.

Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"