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
|
|||
|
|||
Populating mail merge doc with data from MSsql 2K table
(Win2Kpro,Office 2003, MSSql 2000)
How can I (step by step from within MSWord) use a query of data in a large SQL2000 db table to merge into an MSword doc? If it needs MS Query - where can I download it from? tia |
#2
|
|||
|
|||
Populating mail merge doc with data from MSsql 2K table
Hi Sydney,
(Win2Kpro,Office 2003, MSSql 2000) How can I (step by step from within MSWord) use a query of data in a large SQL2000 db table to merge into an MSword doc? If it needs MS Query - where can I download it from? You wouldn't need to d/l MS Query, it's part of the Office 2003 package. And if it's already installed, you'd find it in the list under the button on the far right in the Open Data Source dialog box. Office 2003 should be able to link directly to SQL Server, however. In the Open Data Source dialog box click the "New Source" button. - Select SQL Server from the list - Enter the Server name in the following dialog box, and choose the security/login type you want/need to use. NEXT - Select the database, then the table/stored proc - Give it a "friendly name" and description (because you'll be able to re-use it from "My data sources" without having to go through all these steps, again) Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) |
#3
|
|||
|
|||
Populating mail merge doc with data from MSsql 2K table
MS Query is part of Office so you should have it already. However, it may
not have been installed depending on what you chose during SETUP, so you may need to go to Control Panel to sort that out. It is also rather hard to find the MS Query option in Word - when you get to the Select Data Source dialog box, look for the Tools menu in the top right - MS Query should be at the bottom of that. However, you should only need MS Query if you need to create a query to filter and/or sort the data in some way that cannot be done in the Advanced filter/sort dialog in Word. If you already have a table or view in SQL Server that gives you exactly the data you need, you can connect to that without using MS Query. Assuming you do not need MS Query, you can make the connection as follows: a. Choose Word Tools|Letters and Mailings|Mail Merge... Word displays the Mail Merge Wizard, Step 1. b. Select the document type (say Letters) and click Next to get to Step 2 c. Leave "Use the current document" selected and click Next to get to Step 3 d. Leave "Use an existing list" selected and click Next. The Select Data Source Dialog box should be displayed. If it opens with the default folder showing (My Data Sources) you should see an item called "+New SQL Server Connection.odc". If not, navigate to My Data Sources. If you still don't see the item, jump to point (m) below. If you see it, select it and click the Open button. e. You may see a dialog box saying "Confirm Data Source", with an entry "OLE DB Database files" selected. If so, click OK. This may pop up again later - if so, clcik OK. f. You should now see a "Data Connection Wizard" prompting for the server name. Enter the name of the Windows computer that is running SQL Server (e.g. myserver). If you have a complex multi-instance SQL Server environment this may not be enough and I don't know what you put there, so let's hope you haven't... g. Either accept "Use Windows authentication" or click the other button and enter your SQL Server user name and password, depending on how authentication is managed by your SQL Server server. If you don't know, you will need to ask your SQL Server administrator, but in essence, with "Windows Authentication" your permissions in SQL Server are associated with your Windows networking logon, so you do not (usually) need separate logon credentials. Click Next h. You should now see a list of databases available on the server, and tables within the currently selected database. Select the database, if necessary check "Connect to a specific table", then select the table, and click Next. i. In the next dialog, you are asked for a file name for the connection info. you have just entered. The file created will be an "Office Database Connection" (.odc) file, which is an HTML file with some XML in it. if you selected Windows Authentication in step (g), the option "Save password in file" should be greyed out. If not, only check it if you do not mind having your username and password stored in the .odc file in plain view. Click Finish. j. Word should now create the .odc file and select it as the data source. If you selected Windows Authentication, or opted to store the password in the .odc file, you should see the "Mail Merge Recipients" dialog box. If so, you can now click OK and continue setting up your merge document. I would check fairly early on that when you close and re-open your document, Word correctly reconnects to the SQL Server table/view. Otherwise, if you did not sleect Windows Authentication and you did not opt to store the password, you will probably see a standard OLEDB "DataLink Properties dialog box. You will need to provide the password here, and you will also need to check the "allow saving password" box. Note that although this suggests that the password will now be stored in the Word document, if you save and close the document and re-open it, the DataLink Properties dialog box should be redisplayed and you will have to re-enter the password. If you now create a new mail merge main document and select the recently created .odc file as your data source, you should only see the Datalink Properties dialog box if you did not select Windows Authentication, you did not opt to store the password, or (maybe) if the login/password is no longer adequate. If you do not check "Connect to a specific table" in step (h), you will be asked for the table in step (j) just after Word has selected the .odc as the source. If you need to use MS Query, the process is rather different, but perhaps you can let us know if that is the case. -- Peter Jamieson "Sydney Lotterby" wrote in message ... (Win2Kpro,Office 2003, MSSql 2000) How can I (step by step from within MSWord) use a query of data in a large SQL2000 db table to merge into an MSword doc? If it needs MS Query - where can I download it from? tia |
#4
|
|||
|
|||
Populating mail merge doc with data from MSsql 2K table
Thanks Cindy.
"Cindy M -WordMVP-" wrote in message news:VA.000097db.00fb9a47@speedy... Hi Sydney, (Win2Kpro,Office 2003, MSSql 2000) How can I (step by step from within MSWord) use a query of data in a large SQL2000 db table to merge into an MSword doc? If it needs MS Query - where can I download it from? You wouldn't need to d/l MS Query, it's part of the Office 2003 package. And if it's already installed, you'd find it in the list under the button on the far right in the Open Data Source dialog box. Office 2003 should be able to link directly to SQL Server, however. In the Open Data Source dialog box click the "New Source" button. - Select SQL Server from the list - Enter the Server name in the following dialog box, and choose the security/login type you want/need to use. NEXT - Select the database, then the table/stored proc - Give it a "friendly name" and description (because you'll be able to re-use it from "My data sources" without having to go through all these steps, again) Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) |
#5
|
|||
|
|||
Populating mail merge doc with data from MSsql 2K table
Thank you Peter. You spent a lot of time spelling it out for me - I
appreciate that v. much. Since I have a View for the data I need, it was very easy. "Peter Jamieson" wrote in message ... MS Query is part of Office so you should have it already. However, it may not have been installed depending on what you chose during SETUP, so you may need to go to Control Panel to sort that out. It is also rather hard to find the MS Query option in Word - when you get to the Select Data Source dialog box, look for the Tools menu in the top right - MS Query should be at the bottom of that. However, you should only need MS Query if you need to create a query to filter and/or sort the data in some way that cannot be done in the Advanced filter/sort dialog in Word. If you already have a table or view in SQL Server that gives you exactly the data you need, you can connect to that without using MS Query. Assuming you do not need MS Query, you can make the connection as follows: a. Choose Word Tools|Letters and Mailings|Mail Merge... Word displays the Mail Merge Wizard, Step 1. b. Select the document type (say Letters) and click Next to get to Step 2 c. Leave "Use the current document" selected and click Next to get to Step 3 d. Leave "Use an existing list" selected and click Next. The Select Data Source Dialog box should be displayed. If it opens with the default folder showing (My Data Sources) you should see an item called "+New SQL Server Connection.odc". If not, navigate to My Data Sources. If you still don't see the item, jump to point (m) below. If you see it, select it and click the Open button. e. You may see a dialog box saying "Confirm Data Source", with an entry "OLE DB Database files" selected. If so, click OK. This may pop up again later - if so, clcik OK. f. You should now see a "Data Connection Wizard" prompting for the server name. Enter the name of the Windows computer that is running SQL Server (e.g. myserver). If you have a complex multi-instance SQL Server environment this may not be enough and I don't know what you put there, so let's hope you haven't... g. Either accept "Use Windows authentication" or click the other button and enter your SQL Server user name and password, depending on how authentication is managed by your SQL Server server. If you don't know, you will need to ask your SQL Server administrator, but in essence, with "Windows Authentication" your permissions in SQL Server are associated with your Windows networking logon, so you do not (usually) need separate logon credentials. Click Next h. You should now see a list of databases available on the server, and tables within the currently selected database. Select the database, if necessary check "Connect to a specific table", then select the table, and click Next. i. In the next dialog, you are asked for a file name for the connection info. you have just entered. The file created will be an "Office Database Connection" (.odc) file, which is an HTML file with some XML in it. if you selected Windows Authentication in step (g), the option "Save password in file" should be greyed out. If not, only check it if you do not mind having your username and password stored in the .odc file in plain view. Click Finish. j. Word should now create the .odc file and select it as the data source. If you selected Windows Authentication, or opted to store the password in the .odc file, you should see the "Mail Merge Recipients" dialog box. If so, you can now click OK and continue setting up your merge document. I would check fairly early on that when you close and re-open your document, Word correctly reconnects to the SQL Server table/view. Otherwise, if you did not sleect Windows Authentication and you did not opt to store the password, you will probably see a standard OLEDB "DataLink Properties dialog box. You will need to provide the password here, and you will also need to check the "allow saving password" box. Note that although this suggests that the password will now be stored in the Word document, if you save and close the document and re-open it, the DataLink Properties dialog box should be redisplayed and you will have to re-enter the password. If you now create a new mail merge main document and select the recently created .odc file as your data source, you should only see the Datalink Properties dialog box if you did not select Windows Authentication, you did not opt to store the password, or (maybe) if the login/password is no longer adequate. If you do not check "Connect to a specific table" in step (h), you will be asked for the table in step (j) just after Word has selected the .odc as the source. If you need to use MS Query, the process is rather different, but perhaps you can let us know if that is the case. -- Peter Jamieson "Sydney Lotterby" wrote in message ... (Win2Kpro,Office 2003, MSSql 2000) How can I (step by step from within MSWord) use a query of data in a large SQL2000 db table to merge into an MSword doc? If it needs MS Query - where can I download it from? tia |
Thread Tools | |
Display Modes | |
|
|