none
word 2013 using information from a sql servewr 2012 database RRS feed

  • Question

  • I have a few questions to about using the mail merge feature in word 2013 since this is the first time I have worked with this feature. I am asking this question since I need to modify an existing an existing word 2013 document that uses the mail merge feature:
    1. In the document, a pop up window shows basically that displays select * from view_bne. I then need to hit an ok button for the data to be displayed on each page for each letter. Can you tell me how to change what is displayed  in  the select * from view_bne statement? Is there a way for this select statement not to display every time the word 2013 document is opened and if so how would I change this?
    2. There is a window that shows up if I want to change the data source? Thus can you tell me how to change the data source for the word document? Is there a way that can be setup so the popup window does not show up all the time? Basically is there a way for the word document to 'remember' what the connection is?
    3. When running the mail merge is there a way that all word document letters can be saved electronically? Basically I may not want to print all the letters right away. I will want to print the letters at a later time?
    4. When printing a regular word document you can print specific pages. Basically you can print page 2of 5. Can you tell me how to do that for a file that will have 20,000 letters to print at once?
    5. If the mail merge of a word document is executed on one computer and the 20,000 letters are to print on another printer on that is setup on my company's network, how would I set that up?
    Saturday, March 4, 2017 3:15 AM

Answers

  • The other general approach that might be feasible in your organisation is if it has a database reporting product such as SQL Reporting Services. In that case, it may be quite easy, particularly for someone with good experience of the software, to set up the "report" that you need. It's about 10 years since I had anything to do with it so I really do not know what is on offer these days.

    Peter Jamieson


    Tuesday, March 7, 2017 8:34 AM

All replies

  • 1. The pop-up question and how to suppress it: https://support.microsoft.com/en-gb/help/825765 . Since the registry fix described in there reduces security, there is a case for setting up a single machine to do merges, rather than fixing multiple PCs. There is another security issue, which is that you either have to set up your data source in such a way that Word prompts for the SQL Server login details every time it connects tot he data source, or the login details are saved with Word. 

    Another way to deal with the pop-up is to start with a document that is not connected to a data source. But that conflicts, in effect with your second question.

    2. Word does remember what the data source is - that's how it is able to present the query text in the popup.

    If you want to change the data source you can do so manually using the Mailings tab, Start Mail Merge group, Select Recipients dropdown. However, in the case of a SQL Server data source, what you actually have to select is a thing called a .odc file, which would typically be located on the machine doing the mailmerge (but which you *might* be able to locate at a network address) and provides all the information necessary for Word to connect. If you want to connect to a different source, you have to set up a new .odc. Word tries to use an OLE DB connection. (If you have to use an ODBC connection, yet another approach may be needed, using a .dsn file or a machine DSN).

    You can also make a connection programmatically. The simplest way is to use VBA and the Document.Mailmerge.OpenDataSource method. Again, you have to provide a .odc file, even though you are actually connecting to a database server, not a file.

    You used to be able to avoid having multiple .odc files by creating a completely empty .odc file (let's say it is called empty.odc), e.g. using Notepad, providing that .odc to the OpenDataSource method, then providing the relevant SQL query and connection string in the relevant OpenDataSource parameters. I have not tested this recently.

    3. You can set up the merge so that the output is to a new Word document rather than the printer. *All* the letters will be in that document. It's a regular Word document - you can open it and reprint some or all of it later.

    4. When you merge to a new document, each letter is output in a new Word section. You can specify a range of pages using, e.g. p1s10-p3s10 to print pages 1-3 of section 10 (which might in fact be letter 10). See, e.g. https://support.microsoft.com/en-gb/help/826218

    If you look around you will also find code that can split your document into separate files, either during the merge or after it. 

    5. If you need to execute on one computer and print from another, the simplest approach is probably to output to a netwrk location available to both computers/logins. However, that assumes that you do the merge step, then move to the other computer and do the print step.

    You should test that 50000 letters is not going to cause you performance or manageability problems. If the merge to a new document takes a long time, it may be advisable to split up the merge into several separate merges. If Word creates a large output file and it takes a long time to open, printing individual sections may be difficult. Having to restart a 50000-letter printout because of a problem isn't exactly my idea of fun either!
     

    Peter Jamieson


    • Edited by Peter Jamieson Saturday, March 4, 2017 1:25 PM insert links properly
    Saturday, March 4, 2017 1:23 PM
  • How would you split up the number of letters that need to be generated? Split up the volume in the sql?
    Sunday, March 5, 2017 12:09 AM
  • If I had to do it, I would probably write VBA and either

     a. dynamically construct a sequence of SQL queries (possibly using TOP and BOTTOM to get the rows I needed, but there may be a better way), and merge each batch or

     b. retrieve all the data and use the Document.MailMerge.DataSource.FirstRecord and .LastRecord properties to set the start record number and end record number for each merge.

    If the record count in the relevant database table can vary while you are merging, (a) could miss records or process records twice. You might need to ensure that all the SQL SELECTs were issued in a single SQL transaction, or if the data volumes are not large, make a temporary copy of the data and query that.

    With (b) you may find with a SQL Server data source that Word does not populate the RecordCount property of the DataSource object. If so, you can consider using ADO to query the record count directly.

    (Another completely different approach if the relevant skills are available would be to write .NET code to query the database, format and print the output directly.)


    Peter Jamieson

    Sunday, March 5, 2017 9:57 AM
  • I am told not use to vba or .net programming. Thus can you tell me how to accomplish my goal without using any special programming?
    Monday, March 6, 2017 3:58 PM
  • Assuming "any special programming" rules out things such as VBScript and Windows Powershell, there is no easy way to split up a batch, unless you can split it using some existing data item, e.g. process all the names beginning with "A", then all the names beginning with B", etc. etc. Even then you would either have to change the query *manually* to process each batch, or you would have to set up 26 copies of the mail merge main document, create the appropriate query for each one, then open those 26 documents, initiate the merge, and deal with the output. (And I'm simplifying that because there could be more than 26.)

    If someone is allowed to create SQL Server Views on your behalf, they might be able to create SQL views that would split up the batch in a useful way. But you would still need to do something along the lines I have described above.

    If you *are* allowed to use something like VB Script or Powershell, then it should be possible to do the same kind of thing that I have suggested can be done in VBA.


    Peter Jamieson

    Monday, March 6, 2017 4:43 PM
  • The other general approach that might be feasible in your organisation is if it has a database reporting product such as SQL Reporting Services. In that case, it may be quite easy, particularly for someone with good experience of the software, to set up the "report" that you need. It's about 10 years since I had anything to do with it so I really do not know what is on offer these days.

    Peter Jamieson


    Tuesday, March 7, 2017 8:34 AM
  • I thank you for your contribution. 

    VBA Mail Merge from a Stored Procedure as Datasource

    I'm trying to work around without luck.

    strSQL = "{ call spRelatorio2Word('TESTE') }"

    strConnection = "Provider=SQLOLEDB.1;User ID=**;Password=**;Data Source=localhost\SQL2014;Initial Catalog=DB"

    oDoc.MailMerge.OpenDataSource Name:="D:\X\EMPTY.odc", Connection:=strConnection, SQLStatement:=strSQL

    Run-time error '5548'

    Word can't open EMPTY.odc because has no data.

    ... also I was unable to succeed with a real working .odc

    Can you please help me if you read me?

    I want to use the mailmerge placeholders. I think is not easy to replace the palceholders for VBA code alternative....

    Saturday, August 11, 2018 5:44 PM
  • I can't verify this right now, and it will be several days before I can look at it again,  but there has long been a problem where Word cannot retrieve the results of a multi-step SQL Server Procedure. (I believe it is because SQL actually returns multiple result sets and Word does not process them properly.

    Is there a table or view that you have the necessary permissions to connect to, and can you try to set up a connection to that? If that succeeds, then I think the problem is really with the procedure call. In the past, the only way I have been able to work around that is to turn the procedure into a Transact*SQL table-valued Function. Let's say it's called myFunction, and takes a single string parameter. Then in your Word OpenDataSource call you would use something more like

    SELECT * FROM myFunction('TESTE')

    In some situations in Word you can get a fresh set of data merely by changing the value of .QueryString as your code is doing, but in other cases, Word insists that you close the data source and re-open it using OpenDataSource and the SQL you really want.

    There are plenty of things that can cause a SQL Server data retrieval to go wrong (e.g. there used to be a registry setting that affected the way that Word treated dotted SQL Server object names such as "dbo.someObject").

    Something else that used to be worth trying was using Excel to verify that a basic connection string + query to a table or view worked. Then you could typically use the same connection info. in Word. But

     a. that seems to have become somewhat harder in Office 2013 or 2016 as things have changed in this area.

     b. AFAIK, Excel deals better with these multi-statement Stored Procedures than Word, i.e. getting those to work in Excel will not necessarily help you to get them to work in Word.

    Which version(s) of Word do you need to use?


    Peter Jamieson

    Monday, August 13, 2018 1:02 AM