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
|
|||
|
|||
Word 2000/2002 - Proper Mail Merge steps for ODBC?
I am new at setting up a Word document as a Mail Merge to
an Access Database. My situation involves a secured Access 2000/2002 Database, with a Word 2000/2002 Mail Merge Letter. My Access Databases are secured with a MDW Security File required to open my Database. I have various queries within my Access database are specific for each Word Mail Merge document. In my Access DB workgroup security file, I have created a user called "WordUser" whose sole purpose is to be able to run the Word Mail Merge Letters. This user has no password and only has "Read" permission on all the tables/queries required for each letter and nothing else. I have had no luck being able to get my Word 2002 letter to connect to my secured Access database with the Mail Merge Wizard within Word (I have posted a few questions in the newsgroups, but have not gotten any responses). So instead, I have tried to go the ODBC route, where I would create a new DSN entry that points to my Access database, in hopes that I could use this ODBC entry as my datasource for my Word Mail Merge document. However, this method has also raised some questions that I hope someone can answer for me or help in the right direction. 1) To create a new ODBC entry, I do the following: a) click on "System DSN" Tab in the ODBC Data Source Administrator b) click the "Add" button c) select "Microsoft Access Driver (*.mdb)" d) click the "Finish" button e) In the "ODBC Microsoft Access Setup" Form I: f) enter "MyDB" in the "Data Source Name:" text box g) click the "Select..." button in the "Database" Frame to located my Access Database h) click the "Advanced..." button i) enter my "WordUser" in the "Login name:" text box in the "Set Advanced Options" form and click the "OK" button. j) select the "Database:" radio button in the "System Database" frame k) click the "System Database..." button to select my MDW file for my secured database l) click the "OK" button on the ODBC Microsoft Access Setup form to complete setting up my new ODBC entry 2) Now, once my ODBC entry has been created, I go to my Word 2002/2000 letter and select ToolsLetters and MailingsMail Merge Wizard. At the Wizard's Step #3, I select "Browse" from the "Use an existing list" option. This brings up the "Select Data Source" dialog box. Here, the "My Data Sources" is selected in the "Look in:" combo box and all my ODBC entries are showing EXCEPT my newly created Access ODBC entry I created in my above steps (a- l). However, there are 2 entries that I am not familiar with: "+Connect to New Data Source.odc" and "+New SQL Server Connection.odc". I selected the "+Connect to New Data Source.odc" option and it took me into a new "Data Connection Wizard" where I did the following: a) Select "ODBC DSN" from the "What kind of data source do you want to connect to?" list and clicked the "Next" button b) Now my newly created ODBC entry shows up in the "ODBC data sources" list. I selected it and clicked the "Next" button c) At this point, all my tables and queries were listed. From the list, I selected my query for the letter I am creating and clicked the "Next" button. d) Now, the "Save Data Connection File and Finish" dialog box is shown, prompting me to save this as a connection file (*.odc). I entered a name and clicked the "Finish" button. Question: Is this the proper way of getting a Word Document to connect to a Secured Access Database, by having to create a separate Connection File for each Mail Merge Letter I am wanting to create? |
#2
|
|||
|
|||
Question: Is this the proper way of getting a Word
Document to connect to a Secured Access Database, by having to create a separate Connection File for each Mail Merge Letter I am wanting to create? IMO there is no "proper" way. At best, there may be a way that works for your application and minimises problems if you need to distribute your solution to mutliple systems. There are a number of issues here and unfortunately I don't think I can give you reliable suggestions in all cases, but here goes. First, if you want to connect the same way in Word 2000 and Word 2002, you have to use ODBC (or the old DDE method). OLEDB is only supported in Word 2002 and later. Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB. Any connection that uses a .odc (or .udl, as we will see) is actually using OLEDB. So those connections won't work with Word 2000. But in addition, you may find that ODBC connections set up in Word 2000 are discarded when you open the .doc in WOrd 2002 and/or vice versa. This brings up the "Select Data Source" dialog box. Here, the "My Data Sources" is selected in the "Look in:" combo box and all my ODBC entries are showing EXCEPT my newly created Access ODBC entry I created in my above steps (a- The reason (probably) that some ODBC entries appear here and some do not is that there are three types of ODBC DSN, as follows: FIle DSNs: these are just text files in "INI" format that contain the connection information you provided in the ODBC administrator. Typically they have a .dsn extension. These are probably the ones you can see Machine DSNs: these DSNs are not stored in text files. They are stored in the Windows registry, so I do not think you will see them listed in "My Data Sources". There are two types of Machine DSN: a. User DSNs. These are intended to be visible to a single user on a machine which may have several different users. They are stored in the per-user part of the registry. b. System DSNs (the type you created). These are intended to be visible to all users on a particular machine. Neither Word 2000 nor Word 2002, as far as I know, lets you open a .dsn file directly (in the Open/Select Data Source dialog box). Word just tries to get the data directly from the .dsn file, not from the database it refers to. If you are trying to open a file data source such as a .mdb in Word 2000, you can select the .mdb in the Open Data Source dialog, check "Select method", and click Open. If the DSN is a Machine DSN you will probably see it in the list of connection methods. Otherwise, you have to use MS Query to select the DSN (of any type) and set up the connection. You can do the same in Word 2002, although MS Query is a little harder to find - it's on the Tools menu in the top right hand corner of the Select Data Source dialog. (You may also find you need to install MS Query to get what you want). In both Word 2000 and 2002, you should only need one DSN to allow you to use all the tables and queries in your database that are accessible via ODBC. But you will need to go through the MS Query route to choose the table/query or define your own. An alternative to all this is to write, or macro record, a short macro containing an OpenDataSource command that uses an ODBC connection string in the Connection parameter. Then you have to work out the corrct SQL to get the data you want, and insert it in the SQLStatement parameter, then run the macro. You will find examples I've posted if you search this newsgroup, e.g. using Google. One of the problems here is that Word 2002 requires an additional parameter (Subtype) if you want to connect via ODBC. If you are /only/ concerned with Word 2002 and prefer to use OLEDB, there are a couple of ways you can proceed without creating an ODBC DSN. a. use a .udl file. If you create a Notepad (.txt) file and rename it to something.udl, then double-click on the file name, you should see the Datalink editor. This is like, if not identical to, the editor you used to select your DSN in your point 2. However, a. in the Provider tab, select the Jet provider rather than the ODBC provider b. in the Connection tab, you may need to check Blank password and Allow saving password c. in the All tab, you will need to edit the valueJet OLEDB:System databse to be the pathname of your workgroup file. d. When you click OK, you do not see the questions that come at the end of the dialog you used to set up your .odc If you select this file as your data source, you should be presented with a list of tables, so you only really need one .udl for all your documents. It may be possible to use it to connect to a specific table or query, but that's for you to discover if you want. Alternatively, you can set up a .odc using much the same process you used before in your point (2), but choose the Other/Advanced option instead of ODBC DSN. Then fill in the datalink dialog options in the same way as for the .udl file above. When you get to save the .odc, there is a checkbox that lets you specify whether your .odc should cause Word to display a list of tables/queries within your .mdb, or whether Word should connect to a prespecified table/query. Again, if you use the former, you should be able to use a single .odc for all your mailmerge documents. -- Peter Jamieson "Tony_VBACoder" wrote in message ... I am new at setting up a Word document as a Mail Merge to an Access Database. My situation involves a secured Access 2000/2002 Database, with a Word 2000/2002 Mail Merge Letter. My Access Databases are secured with a MDW Security File required to open my Database. I have various queries within my Access database are specific for each Word Mail Merge document. In my Access DB workgroup security file, I have created a user called "WordUser" whose sole purpose is to be able to run the Word Mail Merge Letters. This user has no password and only has "Read" permission on all the tables/queries required for each letter and nothing else. I have had no luck being able to get my Word 2002 letter to connect to my secured Access database with the Mail Merge Wizard within Word (I have posted a few questions in the newsgroups, but have not gotten any responses). So instead, I have tried to go the ODBC route, where I would create a new DSN entry that points to my Access database, in hopes that I could use this ODBC entry as my datasource for my Word Mail Merge document. However, this method has also raised some questions that I hope someone can answer for me or help in the right direction. 1) To create a new ODBC entry, I do the following: a) click on "System DSN" Tab in the ODBC Data Source Administrator b) click the "Add" button c) select "Microsoft Access Driver (*.mdb)" d) click the "Finish" button e) In the "ODBC Microsoft Access Setup" Form I: f) enter "MyDB" in the "Data Source Name:" text box g) click the "Select..." button in the "Database" Frame to located my Access Database h) click the "Advanced..." button i) enter my "WordUser" in the "Login name:" text box in the "Set Advanced Options" form and click the "OK" button. j) select the "Database:" radio button in the "System Database" frame k) click the "System Database..." button to select my MDW file for my secured database l) click the "OK" button on the ODBC Microsoft Access Setup form to complete setting up my new ODBC entry 2) Now, once my ODBC entry has been created, I go to my Word 2002/2000 letter and select ToolsLetters and MailingsMail Merge Wizard. At the Wizard's Step #3, I select "Browse" from the "Use an existing list" option. This brings up the "Select Data Source" dialog box. Here, the "My Data Sources" is selected in the "Look in:" combo box and all my ODBC entries are showing EXCEPT my newly created Access ODBC entry I created in my above steps (a- l). However, there are 2 entries that I am not familiar with: "+Connect to New Data Source.odc" and "+New SQL Server Connection.odc". I selected the "+Connect to New Data Source.odc" option and it took me into a new "Data Connection Wizard" where I did the following: a) Select "ODBC DSN" from the "What kind of data source do you want to connect to?" list and clicked the "Next" button b) Now my newly created ODBC entry shows up in the "ODBC data sources" list. I selected it and clicked the "Next" button c) At this point, all my tables and queries were listed. From the list, I selected my query for the letter I am creating and clicked the "Next" button. d) Now, the "Save Data Connection File and Finish" dialog box is shown, prompting me to save this as a connection file (*.odc). I entered a name and clicked the "Finish" button. Question: Is this the proper way of getting a Word Document to connect to a Secured Access Database, by having to create a separate Connection File for each Mail Merge Letter I am wanting to create? |
#3
|
|||
|
|||
Peter, thank you for all your insight...it will take me a
little while to absorb it and test it out with both Word 2000 and 2002. I do have one question in regards to something you mentioned: First, if you want to connect the same way in Word 2000 and Word 2002, you have to use ODBC (or the old DDE method).....But in addition, you may find that ODBC connections set up in Word 2000 are discarded when you open the .doc in WOrd 2002 and/or vice versa. Here, you first said that I should go the ODBC route if I want to get this to work in both Word 2000 and Word 2002, but then you go onto say that an "ODBC connection set up in Word 2000 are discarded when you open the .doc in Word 2002". I am a little confused here. Will this method (ODBC) work in both Word 2000 and Word 2002? I will be needing this work with both versions, where I will probably end of creating the final Word Mail Merge .doc using Word 2000 because about 90% of the users have Office 2000, whereas the rest of Office XP (Word 2002). In regards to using ODBC, you mentioned "Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB". If I wanted to use the ODBC method, what should I have done so that I can be certain that I am using the OLEDB method? Thanks -----Original Message----- Question: Is this the proper way of getting a Word Document to connect to a Secured Access Database, by having to create a separate Connection File for each Merge Letter I am wanting to create? IMO there is no "proper" way. At best, there may be a way that works for your application and minimises problems if you need to distribute your solution to mutliple systems. There are a number of issues here and unfortunately I don't think I can give you reliable suggestions in all cases, but here goes. First, if you want to connect the same way in Word 2000 and Word 2002, you have to use ODBC (or the old DDE method). OLEDB is only supported in Word 2002 and later. Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB. Any connection that uses a .odc (or .udl, as we will see) is actually using OLEDB. So those connections won't work with Word 2000. But in addition, you may find that ODBC connections set up in Word 2000 are discarded when you open the .doc in WOrd 2002 and/or vice versa. This brings up the "Select Data Source" dialog box. Here, the "My Data Sources" is selected in the "Look in:" combo box and all my ODBC entries are showing EXCEPT my newly created Access ODBC entry I created in my above steps (a- The reason (probably) that some ODBC entries appear here and some do not is that there are three types of ODBC DSN, as follows: FIle DSNs: these are just text files in "INI" format that contain the connection information you provided in the ODBC administrator. Typically they have a .dsn extension. These are probably the ones you can see Machine DSNs: these DSNs are not stored in text files. They are stored in the Windows registry, so I do not think you will see them listed in "My Data Sources". There are two types of Machine DSN: a. User DSNs. These are intended to be visible to a single user on a machine which may have several different users. They are stored in the per-user part of the registry. b. System DSNs (the type you created). These are intended to be visible to all users on a particular machine. Neither Word 2000 nor Word 2002, as far as I know, lets you open a .dsn file directly (in the Open/Select Data Source dialog box). Word just tries to get the data directly from the .dsn file, not from the database it refers to. If you are trying to open a file data source such as a .mdb in Word 2000, you can select the .mdb in the Open Data Source dialog, check "Select method", and click Open. If the DSN is a Machine DSN you will probably see it in the list of connection methods. Otherwise, you have to use MS Query to select the DSN (of any type) and set up the connection. You can do the same in Word 2002, although MS Query is a little harder to find - it's on the Tools menu in the top right hand corner of the Select Data Source dialog. (You may also find you need to install MS Query to get what you want). In both Word 2000 and 2002, you should only need one DSN to allow you to use all the tables and queries in your database that are accessible via ODBC. But you will need to go through the MS Query route to choose the table/query or define your own. An alternative to all this is to write, or macro record, a short macro containing an OpenDataSource command that uses an ODBC connection string in the Connection parameter. Then you have to work out the corrct SQL to get the data you want, and insert it in the SQLStatement parameter, then run the macro. You will find examples I've posted if you search this newsgroup, e.g. using Google. One of the problems here is that Word 2002 requires an additional parameter (Subtype) if you want to connect via ODBC. If you are /only/ concerned with Word 2002 and prefer to use OLEDB, there are a couple of ways you can proceed without creating an ODBC DSN. a. use a .udl file. If you create a Notepad (.txt) file and rename it to something.udl, then double-click on the file name, you should see the Datalink editor. This is like, if not identical to, the editor you used to select your DSN in your point 2. However, a. in the Provider tab, select the Jet provider rather than the ODBC provider b. in the Connection tab, you may need to check Blank password and Allow saving password c. in the All tab, you will need to edit the valueJet OLEDB:System databse to be the pathname of your workgroup file. d. When you click OK, you do not see the questions that come at the end of the dialog you used to set up your .odc If you select this file as your data source, you should be presented with a list of tables, so you only really need one .udl for all your documents. It may be possible to use it to connect to a specific table or query, but that's for you to discover if you want. Alternatively, you can set up a .odc using much the same process you used before in your point (2), but choose the Other/Advanced option instead of ODBC DSN. Then fill in the datalink dialog options in the same way as for the .udl file above. When you get to save the .odc, there is a checkbox that lets you specify whether your .odc should cause Word to display a list of tables/queries within your .mdb, or whether Word should connect to a prespecified table/query. Again, if you use the former, you should be able to use a single .odc for all your mailmerge documents. -- Peter Jamieson "Tony_VBACoder" wrote in message ... I am new at setting up a Word document as a Mail Merge to an Access Database. My situation involves a secured Access 2000/2002 Database, with a Word 2000/2002 Mail Merge Letter. My Access Databases are secured with a MDW Security File required to open my Database. I have various queries within my Access database are specific for each Word Mail Merge document. In my Access DB workgroup security file, I have created a user called "WordUser" whose sole purpose is to be able to run the Word Mail Merge Letters. This user has no password and only has "Read" permission on all the tables/queries required for each letter and nothing else. I have had no luck being able to get my Word 2002 letter to connect to my secured Access database with the Mail Merge Wizard within Word (I have posted a few questions in the newsgroups, but have not gotten any responses). So instead, I have tried to go the ODBC route, where I would create a new DSN entry that points to my Access database, in hopes that I could use this ODBC entry as my datasource for my Word Mail Merge document. However, this method has also raised some questions that I hope someone can answer for me or help in the right direction. 1) To create a new ODBC entry, I do the following: a) click on "System DSN" Tab in the ODBC Data Source Administrator b) click the "Add" button c) select "Microsoft Access Driver (*.mdb)" d) click the "Finish" button e) In the "ODBC Microsoft Access Setup" Form I: f) enter "MyDB" in the "Data Source Name:" text box g) click the "Select..." button in the "Database" Frame to located my Access Database h) click the "Advanced..." button i) enter my "WordUser" in the "Login name:" text box in the "Set Advanced Options" form and click the "OK" button. j) select the "Database:" radio button in the "System Database" frame k) click the "System Database..." button to select my MDW file for my secured database l) click the "OK" button on the ODBC Microsoft Access Setup form to complete setting up my new ODBC entry 2) Now, once my ODBC entry has been created, I go to my Word 2002/2000 letter and select ToolsLetters and MailingsMail Merge Wizard. At the Wizard's Step #3, I select "Browse" from the "Use an existing list" option. This brings up the "Select Data Source" dialog box. Here, the "My Data Sources" is selected in the "Look in:" combo box and all my ODBC entries are showing EXCEPT my newly created Access ODBC entry I created in my above steps (a- l). However, there are 2 entries that I am not familiar with: "+Connect to New Data Source.odc" and "+New SQL Server Connection.odc". I selected the "+Connect to New Data Source.odc" option and it took me into a new "Data Connection Wizard" where I did the following: a) Select "ODBC DSN" from the "What kind of data source do you want to connect to?" list and clicked the "Next" button b) Now my newly created ODBC entry shows up in the "ODBC data sources" list. I selected it and clicked the "Next" button c) At this point, all my tables and queries were listed. From the list, I selected my query for the letter I am creating and clicked the "Next" button. d) Now, the "Save Data Connection File and Finish" dialog box is shown, prompting me to save this as a connection file (*.odc). I entered a name and clicked the "Finish" button. Question: Is this the proper way of getting a Word Document to connect to a Secured Access Database, by having to create a separate Connection File for each Merge Letter I am wanting to create? . |
#4
|
|||
|
|||
Here, you first said that I should go the ODBC route if I
want to get this to work in both Word 2000 and Word 2002, but then you go onto say that an "ODBC connection set up in Word 2000 are discarded when you open the .doc in Word 2002". I am a little confused here. Will this method (ODBC) work in both Word 2000 and Word 2002? WHat I mean is that the OLEDB connection is simply not available in Word 2000. So the only option (other than DDE) that will work in both is ODBC. However, that does not mean that Word 2002 will correctly recognise an ODBC data source which has been set up in Word 2000 and vice versa. So... I will be needing this work with both versions, where I will probably end of creating the final Word Mail Merge .doc using Word 2000 because about 90% of the users have Office 2000, whereas the rest of Office XP (Word 2002). ....you /may/ need to create Word 2000 and Word 2002 versions of each mail merge app. I can't remember off the top of my head so you will need to check. I would also take a bit of time to ensure that your users can do what they need - e.g. if they need to apply their own filters/sorting, you may need to confirm that this approach allows them to do so. In regards to using ODBC, you mentioned "Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB". If I wanted to use the ODBC method, what should I have done so that I can be certain that I am using the OLEDB method? Assuming you meant "certain that I am using the ODBC method", then you either need to go via MS Query, or go the VBA OpenDataSource route, or check Word Tools|Options|General|"Confirm conversions at open", go through the Select Data Source dialog, pick your .mdb, then select the User/System DSN from the list of possible connection options. If you don't see it there, you will need to use MS Query or VBA. -- Peter Jamieson "Tony_VBACoder" wrote in message ... Peter, thank you for all your insight...it will take me a little while to absorb it and test it out with both Word 2000 and 2002. I do have one question in regards to something you mentioned: First, if you want to connect the same way in Word 2000 and Word 2002, you have to use ODBC (or the old DDE method).....But in addition, you may find that ODBC connections set up in Word 2000 are discarded when you open the .doc in WOrd 2002 and/or vice versa. Here, you first said that I should go the ODBC route if I want to get this to work in both Word 2000 and Word 2002, but then you go onto say that an "ODBC connection set up in Word 2000 are discarded when you open the .doc in Word 2002". I am a little confused here. Will this method (ODBC) work in both Word 2000 and Word 2002? I will be needing this work with both versions, where I will probably end of creating the final Word Mail Merge .doc using Word 2000 because about 90% of the users have Office 2000, whereas the rest of Office XP (Word 2002). In regards to using ODBC, you mentioned "Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB". If I wanted to use the ODBC method, what should I have done so that I can be certain that I am using the OLEDB method? Thanks -----Original Message----- Question: Is this the proper way of getting a Word Document to connect to a Secured Access Database, by having to create a separate Connection File for each Merge Letter I am wanting to create? IMO there is no "proper" way. At best, there may be a way that works for your application and minimises problems if you need to distribute your solution to mutliple systems. There are a number of issues here and unfortunately I don't think I can give you reliable suggestions in all cases, but here goes. First, if you want to connect the same way in Word 2000 and Word 2002, you have to use ODBC (or the old DDE method). OLEDB is only supported in Word 2002 and later. Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB. Any connection that uses a .odc (or .udl, as we will see) is actually using OLEDB. So those connections won't work with Word 2000. But in addition, you may find that ODBC connections set up in Word 2000 are discarded when you open the .doc in WOrd 2002 and/or vice versa. This brings up the "Select Data Source" dialog box. Here, the "My Data Sources" is selected in the "Look in:" combo box and all my ODBC entries are showing EXCEPT my newly created Access ODBC entry I created in my above steps (a- The reason (probably) that some ODBC entries appear here and some do not is that there are three types of ODBC DSN, as follows: FIle DSNs: these are just text files in "INI" format that contain the connection information you provided in the ODBC administrator. Typically they have a .dsn extension. These are probably the ones you can see Machine DSNs: these DSNs are not stored in text files. They are stored in the Windows registry, so I do not think you will see them listed in "My Data Sources". There are two types of Machine DSN: a. User DSNs. These are intended to be visible to a single user on a machine which may have several different users. They are stored in the per-user part of the registry. b. System DSNs (the type you created). These are intended to be visible to all users on a particular machine. Neither Word 2000 nor Word 2002, as far as I know, lets you open a .dsn file directly (in the Open/Select Data Source dialog box). Word just tries to get the data directly from the .dsn file, not from the database it refers to. If you are trying to open a file data source such as a .mdb in Word 2000, you can select the .mdb in the Open Data Source dialog, check "Select method", and click Open. If the DSN is a Machine DSN you will probably see it in the list of connection methods. Otherwise, you have to use MS Query to select the DSN (of any type) and set up the connection. You can do the same in Word 2002, although MS Query is a little harder to find - it's on the Tools menu in the top right hand corner of the Select Data Source dialog. (You may also find you need to install MS Query to get what you want). In both Word 2000 and 2002, you should only need one DSN to allow you to use all the tables and queries in your database that are accessible via ODBC. But you will need to go through the MS Query route to choose the table/query or define your own. An alternative to all this is to write, or macro record, a short macro containing an OpenDataSource command that uses an ODBC connection string in the Connection parameter. Then you have to work out the corrct SQL to get the data you want, and insert it in the SQLStatement parameter, then run the macro. You will find examples I've posted if you search this newsgroup, e.g. using Google. One of the problems here is that Word 2002 requires an additional parameter (Subtype) if you want to connect via ODBC. If you are /only/ concerned with Word 2002 and prefer to use OLEDB, there are a couple of ways you can proceed without creating an ODBC DSN. a. use a .udl file. If you create a Notepad (.txt) file and rename it to something.udl, then double-click on the file name, you should see the Datalink editor. This is like, if not identical to, the editor you used to select your DSN in your point 2. However, a. in the Provider tab, select the Jet provider rather than the ODBC provider b. in the Connection tab, you may need to check Blank password and Allow saving password c. in the All tab, you will need to edit the valueJet OLEDB:System databse to be the pathname of your workgroup file. d. When you click OK, you do not see the questions that come at the end of the dialog you used to set up your .odc If you select this file as your data source, you should be presented with a list of tables, so you only really need one .udl for all your documents. It may be possible to use it to connect to a specific table or query, but that's for you to discover if you want. Alternatively, you can set up a .odc using much the same process you used before in your point (2), but choose the Other/Advanced option instead of ODBC DSN. Then fill in the datalink dialog options in the same way as for the .udl file above. When you get to save the .odc, there is a checkbox that lets you specify whether your .odc should cause Word to display a list of tables/queries within your .mdb, or whether Word should connect to a prespecified table/query. Again, if you use the former, you should be able to use a single .odc for all your mailmerge documents. -- Peter Jamieson "Tony_VBACoder" wrote in message ... I am new at setting up a Word document as a Mail Merge to an Access Database. My situation involves a secured Access 2000/2002 Database, with a Word 2000/2002 Mail Merge Letter. My Access Databases are secured with a MDW Security File required to open my Database. I have various queries within my Access database are specific for each Word Mail Merge document. In my Access DB workgroup security file, I have created a user called "WordUser" whose sole purpose is to be able to run the Word Mail Merge Letters. This user has no password and only has "Read" permission on all the tables/queries required for each letter and nothing else. I have had no luck being able to get my Word 2002 letter to connect to my secured Access database with the Mail Merge Wizard within Word (I have posted a few questions in the newsgroups, but have not gotten any responses). So instead, I have tried to go the ODBC route, where I would create a new DSN entry that points to my Access database, in hopes that I could use this ODBC entry as my datasource for my Word Mail Merge document. However, this method has also raised some questions that I hope someone can answer for me or help in the right direction. 1) To create a new ODBC entry, I do the following: a) click on "System DSN" Tab in the ODBC Data Source Administrator b) click the "Add" button c) select "Microsoft Access Driver (*.mdb)" d) click the "Finish" button e) In the "ODBC Microsoft Access Setup" Form I: f) enter "MyDB" in the "Data Source Name:" text box g) click the "Select..." button in the "Database" Frame to located my Access Database h) click the "Advanced..." button i) enter my "WordUser" in the "Login name:" text box in the "Set Advanced Options" form and click the "OK" button. j) select the "Database:" radio button in the "System Database" frame k) click the "System Database..." button to select my MDW file for my secured database l) click the "OK" button on the ODBC Microsoft Access Setup form to complete setting up my new ODBC entry 2) Now, once my ODBC entry has been created, I go to my Word 2002/2000 letter and select ToolsLetters and MailingsMail Merge Wizard. At the Wizard's Step #3, I select "Browse" from the "Use an existing list" option. This brings up the "Select Data Source" dialog box. Here, the "My Data Sources" is selected in the "Look in:" combo box and all my ODBC entries are showing EXCEPT my newly created Access ODBC entry I created in my above steps (a- l). However, there are 2 entries that I am not familiar with: "+Connect to New Data Source.odc" and "+New SQL Server Connection.odc". I selected the "+Connect to New Data Source.odc" option and it took me into a new "Data Connection Wizard" where I did the following: a) Select "ODBC DSN" from the "What kind of data source do you want to connect to?" list and clicked the "Next" button b) Now my newly created ODBC entry shows up in the "ODBC data sources" list. I selected it and clicked the "Next" button c) At this point, all my tables and queries were listed. From the list, I selected my query for the letter I am creating and clicked the "Next" button. d) Now, the "Save Data Connection File and Finish" dialog box is shown, prompting me to save this as a connection file (*.odc). I entered a name and clicked the "Finish" button. Question: Is this the proper way of getting a Word Document to connect to a Secured Access Database, by having to create a separate Connection File for each Merge Letter I am wanting to create? . |
#5
|
|||
|
|||
Peter, thanks for all your help.
I was able to find the MS Query button and locate my correct Query, and everything is working great. There are still a couple of other issues that I am running into in terms of wanting to pull data from a Parameter Query in Access, and I am also running into the "The Database has been placed in a state by user 'bob' on machine 'mach123' that prevents it from being opened or locked" error. But I will be posting these as separate issues as soon as I do a little more testing so that I can properly post what is going on. Thanks again. -----Original Message----- Here, you first said that I should go the ODBC route if I want to get this to work in both Word 2000 and Word 2002, but then you go onto say that an "ODBC connection set up in Word 2000 are discarded when you open the .doc in Word 2002". I am a little confused here. Will this method (ODBC) work in both Word 2000 and Word 2002? WHat I mean is that the OLEDB connection is simply not available in Word 2000. So the only option (other than DDE) that will work in both is ODBC. However, that does not mean that Word 2002 will correctly recognise an ODBC data source which has been set up in Word 2000 and vice versa. So... I will be needing this work with both versions, where I will probably end of creating the final Word Mail Merge .doc using Word 2000 because about 90% of the users have Office 2000, whereas the rest of Office XP (Word 2002). ....you /may/ need to create Word 2000 and Word 2002 versions of each mail merge app. I can't remember off the top of my head so you will need to check. I would also take a bit of time to ensure that your users can do what they need - e.g. if they need to apply their own filters/sorting, you may need to confirm that this approach allows them to do so. In regards to using ODBC, you mentioned "Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB". If I wanted to use the ODBC method, what should I have done so that I can be certain that I am using the OLEDB method? Assuming you meant "certain that I am using the ODBC method", then you either need to go via MS Query, or go the VBA OpenDataSource route, or check Word Tools|Options|General|"Confirm conversions at open", go through the Select Data Source dialog, pick your .mdb, then select the User/System DSN from the list of possible connection options. If you don't see it there, you will need to use MS Query or VBA. -- Peter Jamieson "Tony_VBACoder" wrote in message ... Peter, thank you for all your insight...it will take me a little while to absorb it and test it out with both Word 2000 and 2002. I do have one question in regards to something you mentioned: First, if you want to connect the same way in Word 2000 and Word 2002, you have to use ODBC (or the old DDE method).....But in addition, you may find that ODBC connections set up in Word 2000 are discarded when you open the .doc in WOrd 2002 and/or vice versa. Here, you first said that I should go the ODBC route if I want to get this to work in both Word 2000 and Word 2002, but then you go onto say that an "ODBC connection set up in Word 2000 are discarded when you open the .doc in Word 2002". I am a little confused here. Will this method (ODBC) work in both Word 2000 and Word 2002? I will be needing this work with both versions, where I will probably end of creating the final Word Mail Merge .doc using Word 2000 because about 90% of the users have Office 2000, whereas the rest of Office XP (Word 2002). In regards to using ODBC, you mentioned "Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB". If I wanted to use the ODBC method, what should I have done so that I can be certain that I am using the OLEDB method? Thanks -----Original Message----- Question: Is this the proper way of getting a Word Document to connect to a Secured Access Database, by having to create a separate Connection File for each Merge Letter I am wanting to create? IMO there is no "proper" way. At best, there may be a way that works for your application and minimises problems if you need to distribute your solution to mutliple systems. There are a number of issues here and unfortunately I don't think I can give you reliable suggestions in all cases, but here goes. First, if you want to connect the same way in Word 2000 and Word 2002, you have to use ODBC (or the old DDE method). OLEDB is only supported in Word 2002 and later. Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB. Any connection that uses a .odc (or .udl, as we will see) is actually using OLEDB. So those connections won't work with Word 2000. But in addition, you may find that ODBC connections set up in Word 2000 are discarded when you open the .doc in WOrd 2002 and/or vice versa. This brings up the "Select Data Source" dialog box. Here, the "My Data Sources" is selected in the "Look in:" combo box and all my ODBC entries are showing EXCEPT my newly created Access ODBC entry I created in my above steps (a- The reason (probably) that some ODBC entries appear here and some do not is that there are three types of ODBC DSN, as follows: FIle DSNs: these are just text files in "INI" format that contain the connection information you provided in the ODBC administrator. Typically they have a .dsn extension. These are probably the ones you can see Machine DSNs: these DSNs are not stored in text files. They are stored in the Windows registry, so I do not think you will see them listed in "My Data Sources". There are two types of Machine DSN: a. User DSNs. These are intended to be visible to a single user on a machine which may have several different users. They are stored in the per-user part of the registry. b. System DSNs (the type you created). These are intended to be visible to all users on a particular machine. Neither Word 2000 nor Word 2002, as far as I know, lets you open a .dsn file directly (in the Open/Select Data Source dialog box). Word just tries to get the data directly from the .dsn file, not from the database it refers to. If you are trying to open a file data source such as a .mdb in Word 2000, you can select the .mdb in the Open Data Source dialog, check "Select method", and click Open. If the DSN is a Machine DSN you will probably see it in the list of connection methods. Otherwise, you have to use MS Query to select the DSN (of any type) and set up the connection. You can do the same in Word 2002, although MS Query is a little harder to find - it's on the Tools menu in the top right hand corner of the Select Data Source dialog. (You may also find you need to install MS Query to get what you want). In both Word 2000 and 2002, you should only need one DSN to allow you to use all the tables and queries in your database that are accessible via ODBC. But you will need to go through the MS Query route to choose the table/query or define your own. An alternative to all this is to write, or macro record, a short macro containing an OpenDataSource command that uses an ODBC connection string in the Connection parameter. Then you have to work out the corrct SQL to get the data you want, and insert it in the SQLStatement parameter, then run the macro. You will find examples I've posted if you search this newsgroup, e.g. using Google. One of the problems here is that Word 2002 requires an additional parameter (Subtype) if you want to connect via ODBC. If you are /only/ concerned with Word 2002 and prefer to use OLEDB, there are a couple of ways you can proceed without creating an ODBC DSN. a. use a .udl file. If you create a Notepad (.txt) file and rename it to something.udl, then double-click on the file name, you should see the Datalink editor. This is like, if not identical to, the editor you used to select your DSN in your point 2. However, a. in the Provider tab, select the Jet provider rather than the ODBC provider b. in the Connection tab, you may need to check Blank password and Allow saving password c. in the All tab, you will need to edit the valueJet OLEDB:System databse to be the pathname of your workgroup file. d. When you click OK, you do not see the questions that come at the end of the dialog you used to set up your .odc If you select this file as your data source, you should be presented with a list of tables, so you only really need one .udl for all your documents. It may be possible to use it to connect to a specific table or query, but that's for you to discover if you want. Alternatively, you can set up a .odc using much the same process you used before in your point (2), but choose the Other/Advanced option instead of ODBC DSN. Then fill in the datalink dialog options in the same way as for the .udl file above. When you get to save the .odc, there is a checkbox that lets you specify whether your .odc should cause Word to display a list of tables/queries within your .mdb, or whether Word should connect to a prespecified table/query. Again, if you use the former, you should be able to use a single .odc for all your mailmerge documents. -- Peter Jamieson "Tony_VBACoder" wrote in message ... I am new at setting up a Word document as a Mail Merge to an Access Database. My situation involves a secured Access 2000/2002 Database, with a Word 2000/2002 Mail Merge Letter. My Access Databases are secured with a MDW Security File required to open my Database. I have various queries within my Access database are specific for each Word Mail Merge document. In my Access DB workgroup security file, I have created a user called "WordUser" whose sole purpose is to be able to run the Word Mail Merge Letters. This user has no password and only has "Read" permission on all the tables/queries required for each letter and nothing else. I have had no luck being able to get my Word 2002 letter to connect to my secured Access database with the Mail Merge Wizard within Word (I have posted a few questions in the newsgroups, but have not gotten any responses). So instead, I have tried to go the ODBC route, where I would create a new DSN entry that points to my Access database, in hopes that I could use this ODBC entry as my datasource for my Word Mail Merge document. However, this method has also raised some questions that I hope someone can answer for me or help in the right direction. 1) To create a new ODBC entry, I do the following: a) click on "System DSN" Tab in the ODBC Data Source Administrator b) click the "Add" button c) select "Microsoft Access Driver (*.mdb)" d) click the "Finish" button e) In the "ODBC Microsoft Access Setup" Form I: f) enter "MyDB" in the "Data Source Name:" text box g) click the "Select..." button in the "Database" Frame to located my Access Database h) click the "Advanced..." button i) enter my "WordUser" in the "Login name:" text box in the "Set Advanced Options" form and click the "OK" button. j) select the "Database:" radio button in the "System Database" frame k) click the "System Database..." button to select my MDW file for my secured database l) click the "OK" button on the ODBC Microsoft Access Setup form to complete setting up my new ODBC entry 2) Now, once my ODBC entry has been created, I go to my Word 2002/2000 letter and select ToolsLetters and MailingsMail Merge Wizard. At the Wizard's Step #3, I select "Browse" from the "Use an existing list" option. This brings up the "Select Data Source" dialog box. Here, the "My Data Sources" is selected in the "Look in:" combo box and all my ODBC entries are showing EXCEPT my newly created Access ODBC entry I created in my above steps (a- l). However, there are 2 entries that I am not familiar with: "+Connect to New Data Source.odc" and "+New SQL Server Connection.odc". I selected the "+Connect to New Data Source.odc" option and it took me into a new "Data Connection Wizard" where I did the following: a) Select "ODBC DSN" from the "What kind of data source do you want to connect to?" list and clicked the "Next" button b) Now my newly created ODBC entry shows up in the "ODBC data sources" list. I selected it and clicked the "Next" button c) At this point, all my tables and queries were listed. From the list, I selected my query for the letter I am creating and clicked the "Next" button. d) Now, the "Save Data Connection File and Finish" dialog box is shown, prompting me to save this as a connection file (*.odc). I entered a name and clicked the "Finish" button. Question: Is this the proper way of getting a Word Document to connect to a Secured Access Database, by having to create a separate Connection File for each Merge Letter I am wanting to create? . . |
#6
|
|||
|
|||
FWIW,
a. for parameter queries, you have to use DDE because it is Access that displays the pop-ups that solicit the parameter values from the user, and DDE is the only connection method that actually starts Access. b. although you can /probably/ specify the access mode Word will use when using a .udl, .odc or ODBC Connection parameter in VBA (there is a tab for this in the Datalink editor), - it obviously won't help if someone else is opening the database in an incompatible sharing mode (e.g. some sort of exclsive access mode) - I get the impression that by default, when Word connects to a .mdb using OLEDB, it uses a more exclusive mode than it really should. I'm not completely convinced that anything you specify in a .odc will change that. But I don't know the facts on that one. -- Peter Jamieson "Tony_VBACoder" wrote in message ... Peter, thanks for all your help. I was able to find the MS Query button and locate my correct Query, and everything is working great. There are still a couple of other issues that I am running into in terms of wanting to pull data from a Parameter Query in Access, and I am also running into the "The Database has been placed in a state by user 'bob' on machine 'mach123' that prevents it from being opened or locked" error. But I will be posting these as separate issues as soon as I do a little more testing so that I can properly post what is going on. Thanks again. -----Original Message----- Here, you first said that I should go the ODBC route if I want to get this to work in both Word 2000 and Word 2002, but then you go onto say that an "ODBC connection set up in Word 2000 are discarded when you open the .doc in Word 2002". I am a little confused here. Will this method (ODBC) work in both Word 2000 and Word 2002? WHat I mean is that the OLEDB connection is simply not available in Word 2000. So the only option (other than DDE) that will work in both is ODBC. However, that does not mean that Word 2002 will correctly recognise an ODBC data source which has been set up in Word 2000 and vice versa. So... I will be needing this work with both versions, where I will probably end of creating the final Word Mail Merge .doc using Word 2000 because about 90% of the users have Office 2000, whereas the rest of Office XP (Word 2002). ....you /may/ need to create Word 2000 and Word 2002 versions of each mail merge app. I can't remember off the top of my head so you will need to check. I would also take a bit of time to ensure that your users can do what they need - e.g. if they need to apply their own filters/sorting, you may need to confirm that this approach allows them to do so. In regards to using ODBC, you mentioned "Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB". If I wanted to use the ODBC method, what should I have done so that I can be certain that I am using the OLEDB method? Assuming you meant "certain that I am using the ODBC method", then you either need to go via MS Query, or go the VBA OpenDataSource route, or check Word Tools|Options|General|"Confirm conversions at open", go through the Select Data Source dialog, pick your .mdb, then select the User/System DSN from the list of possible connection options. If you don't see it there, you will need to use MS Query or VBA. -- Peter Jamieson "Tony_VBACoder" wrote in message ... Peter, thank you for all your insight...it will take me a little while to absorb it and test it out with both Word 2000 and 2002. I do have one question in regards to something you mentioned: First, if you want to connect the same way in Word 2000 and Word 2002, you have to use ODBC (or the old DDE method).....But in addition, you may find that ODBC connections set up in Word 2000 are discarded when you open the .doc in WOrd 2002 and/or vice versa. Here, you first said that I should go the ODBC route if I want to get this to work in both Word 2000 and Word 2002, but then you go onto say that an "ODBC connection set up in Word 2000 are discarded when you open the .doc in Word 2002". I am a little confused here. Will this method (ODBC) work in both Word 2000 and Word 2002? I will be needing this work with both versions, where I will probably end of creating the final Word Mail Merge .doc using Word 2000 because about 90% of the users have Office 2000, whereas the rest of Office XP (Word 2002). In regards to using ODBC, you mentioned "Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB". If I wanted to use the ODBC method, what should I have done so that I can be certain that I am using the OLEDB method? Thanks -----Original Message----- Question: Is this the proper way of getting a Word Document to connect to a Secured Access Database, by having to create a separate Connection File for each Merge Letter I am wanting to create? IMO there is no "proper" way. At best, there may be a way that works for your application and minimises problems if you need to distribute your solution to mutliple systems. There are a number of issues here and unfortunately I don't think I can give you reliable suggestions in all cases, but here goes. First, if you want to connect the same way in Word 2000 and Word 2002, you have to use ODBC (or the old DDE method). OLEDB is only supported in Word 2002 and later. Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB. Any connection that uses a .odc (or .udl, as we will see) is actually using OLEDB. So those connections won't work with Word 2000. But in addition, you may find that ODBC connections set up in Word 2000 are discarded when you open the .doc in WOrd 2002 and/or vice versa. This brings up the "Select Data Source" dialog box. Here, the "My Data Sources" is selected in the "Look in:" combo box and all my ODBC entries are showing EXCEPT my newly created Access ODBC entry I created in my above steps (a- The reason (probably) that some ODBC entries appear here and some do not is that there are three types of ODBC DSN, as follows: FIle DSNs: these are just text files in "INI" format that contain the connection information you provided in the ODBC administrator. Typically they have a .dsn extension. These are probably the ones you can see Machine DSNs: these DSNs are not stored in text files. They are stored in the Windows registry, so I do not think you will see them listed in "My Data Sources". There are two types of Machine DSN: a. User DSNs. These are intended to be visible to a single user on a machine which may have several different users. They are stored in the per-user part of the registry. b. System DSNs (the type you created). These are intended to be visible to all users on a particular machine. Neither Word 2000 nor Word 2002, as far as I know, lets you open a .dsn file directly (in the Open/Select Data Source dialog box). Word just tries to get the data directly from the .dsn file, not from the database it refers to. If you are trying to open a file data source such as a .mdb in Word 2000, you can select the .mdb in the Open Data Source dialog, check "Select method", and click Open. If the DSN is a Machine DSN you will probably see it in the list of connection methods. Otherwise, you have to use MS Query to select the DSN (of any type) and set up the connection. You can do the same in Word 2002, although MS Query is a little harder to find - it's on the Tools menu in the top right hand corner of the Select Data Source dialog. (You may also find you need to install MS Query to get what you want). In both Word 2000 and 2002, you should only need one DSN to allow you to use all the tables and queries in your database that are accessible via ODBC. But you will need to go through the MS Query route to choose the table/query or define your own. An alternative to all this is to write, or macro record, a short macro containing an OpenDataSource command that uses an ODBC connection string in the Connection parameter. Then you have to work out the corrct SQL to get the data you want, and insert it in the SQLStatement parameter, then run the macro. You will find examples I've posted if you search this newsgroup, e.g. using Google. One of the problems here is that Word 2002 requires an additional parameter (Subtype) if you want to connect via ODBC. If you are /only/ concerned with Word 2002 and prefer to use OLEDB, there are a couple of ways you can proceed without creating an ODBC DSN. a. use a .udl file. If you create a Notepad (.txt) file and rename it to something.udl, then double-click on the file name, you should see the Datalink editor. This is like, if not identical to, the editor you used to select your DSN in your point 2. However, a. in the Provider tab, select the Jet provider rather than the ODBC provider b. in the Connection tab, you may need to check Blank password and Allow saving password c. in the All tab, you will need to edit the valueJet OLEDB:System databse to be the pathname of your workgroup file. d. When you click OK, you do not see the questions that come at the end of the dialog you used to set up your .odc If you select this file as your data source, you should be presented with a list of tables, so you only really need one .udl for all your documents. It may be possible to use it to connect to a specific table or query, but that's for you to discover if you want. Alternatively, you can set up a .odc using much the same process you used before in your point (2), but choose the Other/Advanced option instead of ODBC DSN. Then fill in the datalink dialog options in the same way as for the .udl file above. When you get to save the .odc, there is a checkbox that lets you specify whether your .odc should cause Word to display a list of tables/queries within your .mdb, or whether Word should connect to a prespecified table/query. Again, if you use the former, you should be able to use a single .odc for all your mailmerge documents. -- Peter Jamieson "Tony_VBACoder" wrote in message ... I am new at setting up a Word document as a Mail Merge to an Access Database. My situation involves a secured Access 2000/2002 Database, with a Word 2000/2002 Mail Merge Letter. My Access Databases are secured with a MDW Security File required to open my Database. I have various queries within my Access database are specific for each Word Mail Merge document. In my Access DB workgroup security file, I have created a user called "WordUser" whose sole purpose is to be able to run the Word Mail Merge Letters. This user has no password and only has "Read" permission on all the tables/queries required for each letter and nothing else. I have had no luck being able to get my Word 2002 letter to connect to my secured Access database with the Mail Merge Wizard within Word (I have posted a few questions in the newsgroups, but have not gotten any responses). So instead, I have tried to go the ODBC route, where I would create a new DSN entry that points to my Access database, in hopes that I could use this ODBC entry as my datasource for my Word Mail Merge document. However, this method has also raised some questions that I hope someone can answer for me or help in the right direction. 1) To create a new ODBC entry, I do the following: a) click on "System DSN" Tab in the ODBC Data Source Administrator b) click the "Add" button c) select "Microsoft Access Driver (*.mdb)" d) click the "Finish" button e) In the "ODBC Microsoft Access Setup" Form I: f) enter "MyDB" in the "Data Source Name:" text box g) click the "Select..." button in the "Database" Frame to located my Access Database h) click the "Advanced..." button i) enter my "WordUser" in the "Login name:" text box in the "Set Advanced Options" form and click the "OK" button. j) select the "Database:" radio button in the "System Database" frame k) click the "System Database..." button to select my MDW file for my secured database l) click the "OK" button on the ODBC Microsoft Access Setup form to complete setting up my new ODBC entry 2) Now, once my ODBC entry has been created, I go to my Word 2002/2000 letter and select ToolsLetters and MailingsMail Merge Wizard. At the Wizard's Step #3, I select "Browse" from the "Use an existing list" option. This brings up the "Select Data Source" dialog box. Here, the "My Data Sources" is selected in the "Look in:" combo box and all my ODBC entries are showing EXCEPT my newly created Access ODBC entry I created in my above steps (a- l). However, there are 2 entries that I am not familiar with: "+Connect to New Data Source.odc" and "+New SQL Server Connection.odc". I selected the "+Connect to New Data Source.odc" option and it took me into a new "Data Connection Wizard" where I did the following: a) Select "ODBC DSN" from the "What kind of data source do you want to connect to?" list and clicked the "Next" button b) Now my newly created ODBC entry shows up in the "ODBC data sources" list. I selected it and clicked the "Next" button c) At this point, all my tables and queries were listed. From the list, I selected my query for the letter I am creating and clicked the "Next" button. d) Now, the "Save Data Connection File and Finish" dialog box is shown, prompting me to save this as a connection file (*.odc). I entered a name and clicked the "Finish" button. Question: Is this the proper way of getting a Word Document to connect to a Secured Access Database, by having to create a separate Connection File for each Merge Letter I am wanting to create? . . |
#7
|
|||
|
|||
After doing a little testing (though not enough to be
completely satisfied yet...), I did find that if I go into my ODBC entry and click the "Configure" button, and if I check the "Read Only" check box from the "Options" button, I don't get the "Database has been placed in a state by user 'bob' on machine 'mach123' that prevents it from being opened or locked" error. As for the Parameter Query method and using DDE, how do I go about setting this up? Thank you for all your insight. -----Original Message----- FWIW, a. for parameter queries, you have to use DDE because it is Access that displays the pop-ups that solicit the parameter values from the user, and DDE is the only connection method that actually starts Access. b. although you can /probably/ specify the access mode Word will use when using a .udl, .odc or ODBC Connection parameter in VBA (there is a tab for this in the Datalink editor), - it obviously won't help if someone else is opening the database in an incompatible sharing mode (e.g. some sort of exclsive access mode) - I get the impression that by default, when Word connects to a .mdb using OLEDB, it uses a more exclusive mode than it really should. I'm not completely convinced that anything you specify in a .odc will change that. But I don't know the facts on that one. -- Peter Jamieson "Tony_VBACoder" wrote in message ... Peter, thanks for all your help. I was able to find the MS Query button and locate my correct Query, and everything is working great. There are still a couple of other issues that I am running into in terms of wanting to pull data from a Parameter Query in Access, and I am also running into the "The Database has been placed in a state by user 'bob' on machine 'mach123' that prevents it from being opened or locked" error. But I will be posting these as separate issues as soon as I do a little more testing so that I can properly post what is going on. Thanks again. -----Original Message----- Here, you first said that I should go the ODBC route if I want to get this to work in both Word 2000 and Word 2002, but then you go onto say that an "ODBC connection set up in Word 2000 are discarded when you open the .doc in Word 2002". I am a little confused here. Will this method (ODBC) work in both Word 2000 and Word 2002? WHat I mean is that the OLEDB connection is simply not available in Word 2000. So the only option (other than DDE) that will work in both is ODBC. However, that does not mean that Word 2002 will correctly recognise an ODBC data source which has been set up in Word 2000 and vice versa. So... I will be needing this work with both versions, where I will probably end of creating the final Word Mail Merge .doc using Word 2000 because about 90% of the users have Office 2000, whereas the rest of Office XP (Word 2002). ....you /may/ need to create Word 2000 and Word 2002 versions of each mail merge app. I can't remember off the top of my head so you will need to check. I would also take a bit of time to ensure that your users can do what they need - e.g. if they need to apply their own filters/sorting, you may need to confirm that this approach allows them to do so. In regards to using ODBC, you mentioned "Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB". If I wanted to use the ODBC method, what should I have done so that I can be certain that I am using the OLEDB method? Assuming you meant "certain that I am using the ODBC method", then you either need to go via MS Query, or go the VBA OpenDataSource route, or check Word Tools|Options|General|"Confirm conversions at open", go through the Select Data Source dialog, pick your .mdb, then select the User/System DSN from the list of possible connection options. If you don't see it there, you will need to use MS Query or VBA. -- Peter Jamieson "Tony_VBACoder" wrote in message ... Peter, thank you for all your insight...it will take me a little while to absorb it and test it out with both Word 2000 and 2002. I do have one question in regards to something you mentioned: First, if you want to connect the same way in Word 2000 and Word 2002, you have to use ODBC (or the old DDE method).....But in addition, you may find that ODBC connections set up in Word 2000 are discarded when you open the .doc in WOrd 2002 and/or vice versa. Here, you first said that I should go the ODBC route if I want to get this to work in both Word 2000 and Word 2002, but then you go onto say that an "ODBC connection set up in Word 2000 are discarded when you open the .doc in Word 2002". I am a little confused here. Will this method (ODBC) work in both Word 2000 and Word 2002? I will be needing this work with both versions, where I will probably end of creating the final Word Mail Merge .doc using Word 2000 because about 90% of the users have Office 2000, whereas the rest of Office XP (Word 2002). In regards to using ODBC, you mentioned "Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB". If I wanted to use the ODBC method, what should I have done so that I can be certain that I am using the OLEDB method? Thanks -----Original Message----- Question: Is this the proper way of getting a Word Document to connect to a Secured Access Database, by having to create a separate Connection File for each Merge Letter I am wanting to create? IMO there is no "proper" way. At best, there may be a way that works for your application and minimises problems if you need to distribute your solution to mutliple systems. There are a number of issues here and unfortunately I don't think I can give you reliable suggestions in all cases, but here goes. First, if you want to connect the same way in Word 2000 and Word 2002, you have to use ODBC (or the old DDE method). OLEDB is only supported in Word 2002 and later. Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB. Any connection that uses a .odc (or .udl, as we will see) is actually using OLEDB. So those connections won't work with Word 2000. But in addition, you may find that ODBC connections set up in Word 2000 are discarded when you open the .doc in WOrd 2002 and/or vice versa. This brings up the "Select Data Source" dialog box. Here, the "My Data Sources" is selected in the "Look in:" combo box and all my ODBC entries are showing EXCEPT my newly created Access ODBC entry I created in my above steps (a- The reason (probably) that some ODBC entries appear here and some do not is that there are three types of ODBC DSN, as follows: FIle DSNs: these are just text files in "INI" format that contain the connection information you provided in the ODBC administrator. Typically they have a .dsn extension. These are probably the ones you can see Machine DSNs: these DSNs are not stored in text files. They are stored in the Windows registry, so I do not think you will see them listed in "My Data Sources". There are two types of Machine DSN: a. User DSNs. These are intended to be visible to a single user on a machine which may have several different users. They are stored in the per-user part of the registry. b. System DSNs (the type you created). These are intended to be visible to all users on a particular machine. Neither Word 2000 nor Word 2002, as far as I know, lets you open a .dsn file directly (in the Open/Select Data Source dialog box). Word just tries to get the data directly from the .dsn file, not from the database it refers to. If you are trying to open a file data source such as a .mdb in Word 2000, you can select the .mdb in the Open Data Source dialog, check "Select method", and click Open. If the DSN is a Machine DSN you will probably see it in the list of connection methods. Otherwise, you have to use MS Query to select the DSN (of any type) and set up the connection. You can do the same in Word 2002, although MS Query is a little harder to find - it's on the Tools menu in the top right hand corner of the Select Data Source dialog. (You may also find you need to install MS Query to get what you want). In both Word 2000 and 2002, you should only need one DSN to allow you to use all the tables and queries in your database that are accessible via ODBC. But you will need to go through the MS Query route to choose the table/query or define your own. An alternative to all this is to write, or macro record, a short macro containing an OpenDataSource command that uses an ODBC connection string in the Connection parameter. Then you have to work out the corrct SQL to get the data you want, and insert it in the SQLStatement parameter, then run the macro. You will find examples I've posted if you search this newsgroup, e.g. using Google. One of the problems here is that Word 2002 requires an additional parameter (Subtype) if you want to connect via ODBC. If you are /only/ concerned with Word 2002 and prefer to use OLEDB, there are a couple of ways you can proceed without creating an ODBC DSN. a. use a .udl file. If you create a Notepad (.txt) file and rename it to something.udl, then double-click on the file name, you should see the Datalink editor. This is like, if not identical to, the editor you used to select your DSN in your point 2. However, a. in the Provider tab, select the Jet provider rather than the ODBC provider b. in the Connection tab, you may need to check Blank password and Allow saving password c. in the All tab, you will need to edit the valueJet OLEDB:System databse to be the pathname of your workgroup file. d. When you click OK, you do not see the questions that come at the end of the dialog you used to set up your .odc If you select this file as your data source, you should be presented with a list of tables, so you only really need one .udl for all your documents. It may be possible to use it to connect to a specific table or query, but that's for you to discover if you want. Alternatively, you can set up a .odc using much the same process you used before in your point (2), but choose the Other/Advanced option instead of ODBC DSN. Then fill in the datalink dialog options in the same way as for the .udl file above. When you get to save the .odc, there is a checkbox that lets you specify whether your .odc should cause Word to display a list of tables/queries within your .mdb, or whether Word should connect to a prespecified table/query. Again, if you use the former, you should be able to use a single .odc for all your mailmerge documents. -- Peter Jamieson "Tony_VBACoder" wrote in message ... I am new at setting up a Word document as a Mail Merge to an Access Database. My situation involves a secured Access 2000/2002 Database, with a Word 2000/2002 Merge Letter. My Access Databases are secured with a MDW Security File required to open my Database. I have various queries within my Access database are specific for each Word Mail Merge document. In my Access DB workgroup security file, I have created a user called "WordUser" whose sole purpose is to be able to run the Word Merge Letters. This user has no password and only has "Read" permission on all the tables/queries required for each letter and nothing else. I have had no luck being able to get my Word 2002 letter to connect to my secured Access database with the Mail Merge Wizard within Word (I have posted a few questions in the newsgroups, but have not gotten any responses). So instead, I have tried to go the ODBC route, where I would create a new DSN entry that points to my Access database, in hopes that I could use this ODBC entry as my datasource for my Word Merge document. However, this method has also raised some questions that I hope someone can answer for me or help in the right direction. 1) To create a new ODBC entry, I do the following: a) click on "System DSN" Tab in the ODBC Data Source Administrator b) click the "Add" button c) select "Microsoft Access Driver (*.mdb)" d) click the "Finish" button e) In the "ODBC Microsoft Access Setup" Form I: f) enter "MyDB" in the "Data Source Name:" text box g) click the "Select..." button in the "Database" Frame to located my Access Database h) click the "Advanced..." button i) enter my "WordUser" in the "Login name:" text box in the "Set Advanced Options" form and click the "OK" button. j) select the "Database:" radio button in the "System Database" frame k) click the "System Database..." button to select my MDW file for my secured database l) click the "OK" button on the ODBC Microsoft Access Setup form to complete setting up my new ODBC entry 2) Now, once my ODBC entry has been created, I go to my Word 2002/2000 letter and select ToolsLetters and MailingsMail Merge Wizard. At the Wizard's Step #3, I select "Browse" from the "Use an existing list" option. This brings up the "Select Data Source" dialog box. Here, the "My Data Sources" is selected in the "Look in:" combo box and all my ODBC entries are showing EXCEPT my newly created Access ODBC entry I created in my above steps (a- l). However, there are 2 entries that I am not familiar with: "+Connect to New Data Source.odc" and "+New SQL Server Connection.odc". I selected the "+Connect to New Data Source.odc" option and it took me into a new "Data Connection Wizard" where I did the following: a) Select "ODBC DSN" from the "What kind of data source do you want to connect to?" list and clicked the "Next" button b) Now my newly created ODBC entry shows up in the "ODBC data sources" list. I selected it and clicked the "Next" button c) At this point, all my tables and queries were listed. From the list, I selected my query for the letter I am creating and clicked the "Next" button. d) Now, the "Save Data Connection File and Finish" dialog box is shown, prompting me to save this as a connection file (*.odc). I entered a name and clicked the "Finish" button. Question: Is this the proper way of getting a Word Document to connect to a Secured Access Database, by having to create a separate Connection File for each Merge Letter I am wanting to create? . . . |
#8
|
|||
|
|||
After doing a little testing (though not enough to be
completely satisfied yet...), I did find that if I go into my ODBC entry and click the "Configure" button, and if I check the "Read Only" check box from the "Options" button, I don't get the "Database has been placed in a state by user 'bob' on machine 'mach123' that prevents it from being opened or locked" error. Thanks, useful to know that. As for the Parameter Query method and using DDE, how do I go about setting this up? In Word 2000 and earlier, check the Select method option in Open Data Source and select the DDE method when offered. In Word 2002 and later, check Tools|Options|General|"Confirm conversions at open" (as before), select the ..mdb in Select Data Source and the DDE option when offered. When Word opens the document, it will start Access and request it to open the database. Access should prompt for login/password as usual, but notice that the Access dialog boxes may appear "behind" Word so you may need to Alt-tab to reach them). Access runs the query and will prompt for any parameters. The trouble with this is that you now have two applications open and communicating and that usually spells additional trouble if you are creating an application for others to use. That assumes you are using normal Access parameter queries. Although that's the only way you can use such queries in mailmerge using the built-in facilities, it isn't the only possible way you chould do things if you are using VBA. With VBA you could decide what information to get from the user, prompt using a Userform (say), construct your own query and issue it via OpenDataSource. Using ADO/ADOX or DAO, you could probably retrieve the text of an Access query (including the question marks used as placeholders for parameters) and use userforms or inputboxes to solicit the relevant values from the user, then issue that query using OpenDataSource. Never tried it myself and can see that in the general case a lot of work might be involved, but as long as the queries do not exceed Word's length limits (around 255/511 characters) I think the approach could be made to work if DDE won't do what you really need. -- Peter Jamieson "Tony_VBACoder" wrote in message ... After doing a little testing (though not enough to be completely satisfied yet...), I did find that if I go into my ODBC entry and click the "Configure" button, and if I check the "Read Only" check box from the "Options" button, I don't get the "Database has been placed in a state by user 'bob' on machine 'mach123' that prevents it from being opened or locked" error. As for the Parameter Query method and using DDE, how do I go about setting this up? Thank you for all your insight. -----Original Message----- FWIW, a. for parameter queries, you have to use DDE because it is Access that displays the pop-ups that solicit the parameter values from the user, and DDE is the only connection method that actually starts Access. b. although you can /probably/ specify the access mode Word will use when using a .udl, .odc or ODBC Connection parameter in VBA (there is a tab for this in the Datalink editor), - it obviously won't help if someone else is opening the database in an incompatible sharing mode (e.g. some sort of exclsive access mode) - I get the impression that by default, when Word connects to a .mdb using OLEDB, it uses a more exclusive mode than it really should. I'm not completely convinced that anything you specify in a .odc will change that. But I don't know the facts on that one. -- Peter Jamieson "Tony_VBACoder" wrote in message ... Peter, thanks for all your help. I was able to find the MS Query button and locate my correct Query, and everything is working great. There are still a couple of other issues that I am running into in terms of wanting to pull data from a Parameter Query in Access, and I am also running into the "The Database has been placed in a state by user 'bob' on machine 'mach123' that prevents it from being opened or locked" error. But I will be posting these as separate issues as soon as I do a little more testing so that I can properly post what is going on. Thanks again. -----Original Message----- Here, you first said that I should go the ODBC route if I want to get this to work in both Word 2000 and Word 2002, but then you go onto say that an "ODBC connection set up in Word 2000 are discarded when you open the .doc in Word 2002". I am a little confused here. Will this method (ODBC) work in both Word 2000 and Word 2002? WHat I mean is that the OLEDB connection is simply not available in Word 2000. So the only option (other than DDE) that will work in both is ODBC. However, that does not mean that Word 2002 will correctly recognise an ODBC data source which has been set up in Word 2000 and vice versa. So... I will be needing this work with both versions, where I will probably end of creating the final Word Mail Merge .doc using Word 2000 because about 90% of the users have Office 2000, whereas the rest of Office XP (Word 2002). ....you /may/ need to create Word 2000 and Word 2002 versions of each mail merge app. I can't remember off the top of my head so you will need to check. I would also take a bit of time to ensure that your users can do what they need - e.g. if they need to apply their own filters/sorting, you may need to confirm that this approach allows them to do so. In regards to using ODBC, you mentioned "Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB". If I wanted to use the ODBC method, what should I have done so that I can be certain that I am using the OLEDB method? Assuming you meant "certain that I am using the ODBC method", then you either need to go via MS Query, or go the VBA OpenDataSource route, or check Word Tools|Options|General|"Confirm conversions at open", go through the Select Data Source dialog, pick your .mdb, then select the User/System DSN from the list of possible connection options. If you don't see it there, you will need to use MS Query or VBA. -- Peter Jamieson "Tony_VBACoder" wrote in message ... Peter, thank you for all your insight...it will take me a little while to absorb it and test it out with both Word 2000 and 2002. I do have one question in regards to something you mentioned: First, if you want to connect the same way in Word 2000 and Word 2002, you have to use ODBC (or the old DDE method).....But in addition, you may find that ODBC connections set up in Word 2000 are discarded when you open the .doc in WOrd 2002 and/or vice versa. Here, you first said that I should go the ODBC route if I want to get this to work in both Word 2000 and Word 2002, but then you go onto say that an "ODBC connection set up in Word 2000 are discarded when you open the .doc in Word 2002". I am a little confused here. Will this method (ODBC) work in both Word 2000 and Word 2002? I will be needing this work with both versions, where I will probably end of creating the final Word Mail Merge .doc using Word 2000 because about 90% of the users have Office 2000, whereas the rest of Office XP (Word 2002). In regards to using ODBC, you mentioned "Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB". If I wanted to use the ODBC method, what should I have done so that I can be certain that I am using the OLEDB method? Thanks -----Original Message----- Question: Is this the proper way of getting a Word Document to connect to a Secured Access Database, by having to create a separate Connection File for each Merge Letter I am wanting to create? IMO there is no "proper" way. At best, there may be a way that works for your application and minimises problems if you need to distribute your solution to mutliple systems. There are a number of issues here and unfortunately I don't think I can give you reliable suggestions in all cases, but here goes. First, if you want to connect the same way in Word 2000 and Word 2002, you have to use ODBC (or the old DDE method). OLEDB is only supported in Word 2002 and later. Unfortunately, the route you have taken in your example actually uses OLEDB, using the ODBC provider for OLEDB. Any connection that uses a .odc (or .udl, as we will see) is actually using OLEDB. So those connections won't work with Word 2000. But in addition, you may find that ODBC connections set up in Word 2000 are discarded when you open the .doc in WOrd 2002 and/or vice versa. This brings up the "Select Data Source" dialog box. Here, the "My Data Sources" is selected in the "Look in:" combo box and all my ODBC entries are showing EXCEPT my newly created Access ODBC entry I created in my above steps (a- The reason (probably) that some ODBC entries appear here and some do not is that there are three types of ODBC DSN, as follows: FIle DSNs: these are just text files in "INI" format that contain the connection information you provided in the ODBC administrator. Typically they have a .dsn extension. These are probably the ones you can see Machine DSNs: these DSNs are not stored in text files. They are stored in the Windows registry, so I do not think you will see them listed in "My Data Sources". There are two types of Machine DSN: a. User DSNs. These are intended to be visible to a single user on a machine which may have several different users. They are stored in the per-user part of the registry. b. System DSNs (the type you created). These are intended to be visible to all users on a particular machine. Neither Word 2000 nor Word 2002, as far as I know, lets you open a .dsn file directly (in the Open/Select Data Source dialog box). Word just tries to get the data directly from the .dsn file, not from the database it refers to. If you are trying to open a file data source such as a .mdb in Word 2000, you can select the .mdb in the Open Data Source dialog, check "Select method", and click Open. If the DSN is a Machine DSN you will probably see it in the list of connection methods. Otherwise, you have to use MS Query to select the DSN (of any type) and set up the connection. You can do the same in Word 2002, although MS Query is a little harder to find - it's on the Tools menu in the top right hand corner of the Select Data Source dialog. (You may also find you need to install MS Query to get what you want). In both Word 2000 and 2002, you should only need one DSN to allow you to use all the tables and queries in your database that are accessible via ODBC. But you will need to go through the MS Query route to choose the table/query or define your own. An alternative to all this is to write, or macro record, a short macro containing an OpenDataSource command that uses an ODBC connection string in the Connection parameter. Then you have to work out the corrct SQL to get the data you want, and insert it in the SQLStatement parameter, then run the macro. You will find examples I've posted if you search this newsgroup, e.g. using Google. One of the problems here is that Word 2002 requires an additional parameter (Subtype) if you want to connect via ODBC. If you are /only/ concerned with Word 2002 and prefer to use OLEDB, there are a couple of ways you can proceed without creating an ODBC DSN. a. use a .udl file. If you create a Notepad (.txt) file and rename it to something.udl, then double-click on the file name, you should see the Datalink editor. This is like, if not identical to, the editor you used to select your DSN in your point 2. However, a. in the Provider tab, select the Jet provider rather than the ODBC provider b. in the Connection tab, you may need to check Blank password and Allow saving password c. in the All tab, you will need to edit the valueJet OLEDB:System databse to be the pathname of your workgroup file. d. When you click OK, you do not see the questions that come at the end of the dialog you used to set up your .odc If you select this file as your data source, you should be presented with a list of tables, so you only really need one .udl for all your documents. It may be possible to use it to connect to a specific table or query, but that's for you to discover if you want. Alternatively, you can set up a .odc using much the same process you used before in your point (2), but choose the Other/Advanced option instead of ODBC DSN. Then fill in the datalink dialog options in the same way as for the .udl file above. When you get to save the .odc, there is a checkbox that lets you specify whether your .odc should cause Word to display a list of tables/queries within your .mdb, or whether Word should connect to a prespecified table/query. Again, if you use the former, you should be able to use a single .odc for all your mailmerge documents. -- Peter Jamieson "Tony_VBACoder" wrote in message ... I am new at setting up a Word document as a Mail Merge to an Access Database. My situation involves a secured Access 2000/2002 Database, with a Word 2000/2002 Merge Letter. My Access Databases are secured with a MDW Security File required to open my Database. I have various queries within my Access database are specific for each Word Mail Merge document. In my Access DB workgroup security file, I have created a user called "WordUser" whose sole purpose is to be able to run the Word Merge Letters. This user has no password and only has "Read" permission on all the tables/queries required for each letter and nothing else. I have had no luck being able to get my Word 2002 letter to connect to my secured Access database with the Mail Merge Wizard within Word (I have posted a few questions in the newsgroups, but have not gotten any responses). So instead, I have tried to go the ODBC route, where I would create a new DSN entry that points to my Access database, in hopes that I could use this ODBC entry as my datasource for my Word Merge document. However, this method has also raised some questions that I hope someone can answer for me or help in the right direction. 1) To create a new ODBC entry, I do the following: a) click on "System DSN" Tab in the ODBC Data Source Administrator b) click the "Add" button c) select "Microsoft Access Driver (*.mdb)" d) click the "Finish" button e) In the "ODBC Microsoft Access Setup" Form I: f) enter "MyDB" in the "Data Source Name:" text box g) click the "Select..." button in the "Database" Frame to located my Access Database h) click the "Advanced..." button i) enter my "WordUser" in the "Login name:" text box in the "Set Advanced Options" form and click the "OK" button. j) select the "Database:" radio button in the "System Database" frame k) click the "System Database..." button to select my MDW file for my secured database l) click the "OK" button on the ODBC Microsoft Access Setup form to complete setting up my new ODBC entry 2) Now, once my ODBC entry has been created, I go to my Word 2002/2000 letter and select ToolsLetters and MailingsMail Merge Wizard. At the Wizard's Step #3, I select "Browse" from the "Use an existing list" option. This brings up the "Select Data Source" dialog box. Here, the "My Data Sources" is selected in the "Look in:" combo box and all my ODBC entries are showing EXCEPT my newly created Access ODBC entry I created in my above steps (a- l). However, there are 2 entries that I am not familiar with: "+Connect to New Data Source.odc" and "+New SQL Server Connection.odc". I selected the "+Connect to New Data Source.odc" option and it took me into a new "Data Connection Wizard" where I did the following: a) Select "ODBC DSN" from the "What kind of data source do you want to connect to?" list and clicked the "Next" button b) Now my newly created ODBC entry shows up in the "ODBC data sources" list. I selected it and clicked the "Next" button c) At this point, all my tables and queries were listed. From the list, I selected my query for the letter I am creating and clicked the "Next" button. d) Now, the "Save Data Connection File and Finish" dialog box is shown, prompting me to save this as a connection file (*.odc). I entered a name and clicked the "Finish" button. Question: Is this the proper way of getting a Word Document to connect to a Secured Access Database, by having to create a separate Connection File for each Merge Letter I am wanting to create? . . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using a WORD document as a subreport or report with mail merge | Bob Reynolds | General Discussion | 3 | August 15th, 2004 07:59 PM |
Access 2000 to 2003 mail merge to word | JJ | Running & Setting Up Queries | 1 | June 23rd, 2004 06:41 AM |
Mail Merge to Fax - Word 2003 | Paul | General Discussion | 1 | June 9th, 2004 02:19 PM |
mail merge - how to insert a word file in main document as a filed | Manikrishna | Mailmerge | 1 | May 7th, 2004 10:13 AM |
Word 2003 is adding duplicate records to my mail merge | Doug Robbins - Word MVP | Mailmerge | 0 | April 24th, 2004 03:58 AM |