• Forgetting source on ‘non-simple’ SQL (2002 SP3)

    Home » Forums » AskWoody support » Productivity software by function » MS Word and word processing help » Forgetting source on ‘non-simple’ SQL (2002 SP3)

    • This topic has 9 replies, 2 voices, and was last updated 20 years ago.
    Author
    Topic
    #419049

    When I open a mail merge document, Word tells me something like “Opening this document will run the following SQL command: SELECT * FROM ‘Sheet3$’ …”. It doesn’t tell me the actual DataBase that it’ll be using but hey – can’t have everything!

    Anyway, in my merge I only want records that have something in the “Name” field. So I go to the “Mail Merge Recipients” dialog and using the Filter drop down arrow, select (Nonblanks). I can then perform the merge.

    BUT!!! If I save the main document with (Nonblanks) selected, when I subsequently open it, I see the new SQL statement as “SELECT * FROM ‘Sheet3$’ WHERE (‘Name’ IS NOT NULL And ‘Name’ ”)” as expected BUT!!! it then tells me that it cannot find the data source, and I have to go through the file dialogs to link it to the source once again, and in the process it looses the (Nonblanks) selection once again.

    If I save it with the “simple” “SELECT * FROM ‘Sheet3$'” (i.e. no filters applied to the Recipients) then it remembers the data source, if I have a filter applied then it remembers the filter but forgets the source.

    So, how can I get it to remember BOTH the filter AND the source?

    Any ideas?

    TIA
    Paul Lautman

    Viewing 0 reply threads
    Author
    Replies
    • #945248

      I haven’t been able to find anything on this, and I cannot reproduce the problem – if I save a merge document with a WHERE part in the SQL string, it works correctly when I reopen it. Could you attach a zip file with a small workbook and document to illustrate the problem?

      • #945266

        Hello Hans,
        thank you for looking at my problem.

        The attached zip file should be unpacked in the root directory of the C drive. It will then re-create the original directory structure which I created for this test and which is very simlar to the structure that I actually use.

        When unpacked you will find in the directory C:$user1HSCPool two Word files and one Excel file.

        Both the Word files were linked to the Excel file when they were saved.

        Pool Log Mail Merge Man2s.doc is a “simple” SQL query whilst Pool Log Mail Merge Man2w.doc contains the WHERE clause.

        When I open Pool Log Mail Merge Man2s.doc, it is ready to go. When I open Pool Log Mail Merge Man2w.doc I have to go and re-link it to the database (the Excel file).

        Thanks once again for taking a look at this.

        TIA

        • #945283

          I get the same error with your files. I haven’t been able to determine what the essential difference is between the correctly functioning example I created and your files, but I’ll keep trying.

          • #945293

            Thank you Hans, this is very much appreciated.

            Maybe you can send me your working examples and I’ll try working in the other direction?

            • #945309

              I had used an existing workbook with proprietary data. While trying to construct a new example, I ran into the problem again, but I have found a workaround.

              Select Tools | Options… (in Word), then activate the General tab. Tick the check box Confirm Conversion at Open, and click OK.
              When you open the merge document, and get the message that the data source cannot be found, click Find Data Source…
              After you have selected the workbook, you’ll be prompted for a method to use (see screenshot).
              Do NOT select the default method (OLE DB Database Files), but select MS Excel Worksheets via DDE or an ODBC data source instead.
              If you pick ODBC, you’ll have to click Options… in the next dialog and tick all check boxes.
              You can now specify the worksheet, then filter the data etc.
              If all goes well, you’ll be able to reopen the document with the filter intact.

            • #945313

              When I try DDE I get the error “Word could not parse your query options into a valid SQL string.” and it then simply ignores the filter and reads all the data.

              However the ODBC option seems to work.

              Obviously a bug in Word with OLE DB somewhere.

              Thanks for your assistance.

            • #945592

              I tried recording the setting of a Merge filter, so that I could geta clue as to the VBA code required to automate it. But the subroutine produced contained no code at all.

              Where do I find the classes/methods/properties associated with this and get a clue as to the correct construction?

              TIA
              Paul

            • #945603

              Look up OpenDataSource and QueryString in the Word VBA help and in the Object Browser (press F2 in the Visual Basic Editor)

            • #945643

              Thank you Hans, you’re a star music (of course if word recorded the steps properly then I wouldn’t have had to ask you!!!)

    Viewing 0 reply threads
    Reply To: Forgetting source on ‘non-simple’ SQL (2002 SP3)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: