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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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
|
|||
|
|||
Connecting to Access
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 |
#4
|
|||
|
|||
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
|
|||
|
|||
Connecting to Access
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 |
Thread Tools | |
Display Modes | |
|
|