none
Word merge from Access 2010 RRS feed

  • Question

  • Hi, I want to be able to set the mail merge data source and get 'Object variable or With block variable not set in procedure...' at this line

                With wordDocument
                    .MailMerge.OpenDataSource _
                                Name:=gDbapp.Name, _
                                ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
                                AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
                                WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
                                Format:=wdOpenFormatAuto, _
                                connection:=connectionString, _
                                SQLStatement:="SELECT * FROM 'qWordMergeDataSource' WHERE wmdsPrintId='" & sessionID & "'", _
                                SQLStatement1:="", SubType:=wdMergeSubTypeAccess

    I have recorded a macro in Word to do this and the code snippet above is taken from this.

    Do you have any suggestions or recommendation to resolve this issue?

    Many thanks,

    Jonathan


    Jonathan

    Friday, February 20, 2015 1:44 AM

Answers

  • Hi Jonathan

    You'll always get this message in the UI - it's a security thing. There is a Registry entry that can be changed to suppress this message: http://support.microsoft.com/kb/825765

    When using code to open a mail merge document the data source will ALWAYS be cut off (again, a security thing) and must be attached once more, which is what you're attempting to do.

    The reason you're getting the OLE DB prompt is because that code is incomplete: Word doesn't know what kind of connection technology to use. I'm surprised the macro recorder would do that... Which version of Word are you using?

    RE your earlier error: Access will deny connection if you've made changes in the VBA Editor - you've locked the database exclusively. Does it help if you close the database, open it non-exclusively and then run the code without doing any editing?

    Best for testing the connection would probably be to use a VBA macro in a Word document, with the database closed, until you get a good connection string, then carry that over to your Access VBA.


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by Kiwi Jonathan Wednesday, March 11, 2015 2:02 AM
    Friday, March 6, 2015 7:08 PM
    Moderator

All replies

  • Hi Jonathan

    Are you using the object wordDocument anywhere else in this code? My first impression is that the object is not yet assigned to a Word.Document object...


    Cindy Meister, VSTO/Word MVP, my blog

    Saturday, February 21, 2015 8:18 PM
    Moderator
  • Hi Cindy,

    the immediate lines before are

            Set wordDocument = wordApplication.Documents.Add(letterTemplate)
            If Nz(wordDocument.MailMerge.dataSource.Name, "") = "" Then
    

    In debug mode, these statements are executed successfully.

    Many thanks,

    Jonathan


    Jonathan

    Sunday, February 22, 2015 7:10 PM
  • Hi Kiwi,

    Based on your description, these statements are executed successfully in debug mode, but not worked in runtime, am I right? Could you share us whether you test them in the same pc? If not, I would recommend you test it in the same PC.

    I have tried to make a sample to reproduce your issue, but failed. Could you share us a simple demo to reproduce help us to reproduce issue?

    In addition, have you seen the dialog as following when you double click the document to open the Word, if Yes, which button did you click? If you clicked the "No" button, I would recommend you reopening the file and click the "Yes" to make a try.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Wednesday, February 25, 2015 9:25 AM
  • Hi Jonathan

    In that case, what I'd do is query some objects in the Immediate Window of the VBA editor (Ctrl+G) to narrow down where the problem is. For example:

    ?wordDocument.Name
    ?wordDocument.MailMerge.MainDocumentType
    ?gDbapp.Name

    Press enter after typing a line to execute the command. You'll either see something printed below the command or get an error.


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, March 2, 2015 11:18 AM
    Moderator
  • Hi, sorry about the delay in responding.

    I should mention that the template is typically only opened from Access.

    When I use the Immediate window to examine the parts of the connection string and document properties, valid values are printed.

    I'm wondering whether part of the issue is that the connection string is limited to 255 characters. I have been adjusting the connection string to test whether a different combination makes any difference. No joy.

        connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "User ID=Admin;" & _
                    "Data Source=" & gDbapp.Name & ";" & _
                    "Mode=Read;" & _
                    "Jet OLEDB:Engine Type=6;" & _
                    "Jet OLEDB:Database Locking Mode=1;" & _
                    "Jet OLEDB:Global Partial Bulk Ops=2;" & _
                    "Jet OLEDB:Global Bulk Transactions=1;" ' & _
                    "Jet OLEDB:Support Complex Data=False;" & _
    '                "Extended Properties=" & Chr(34) & Chr(34) & ";" & _
    '                "Jet OLEDB:Create System Database=False;"  & _
    '                "Jet OLEDB:SFP=False;" & _
    '                "Jet OLEDB:System database=" & Chr(34) & Chr(34) & ";" & _
    '                "Jet OLEDB:Registry Path=" & Chr(34) & Chr(34) & ";" & _
    '                "Jet OLEDB:New Database Password=" & Chr(34) & Chr(34) & ";" & _
    '                "Jet OLEDB:Encrypt Database=False;" & _
    '                "Jet OLEDB:Don't Copy Locale on Compact=False;" & _
    '                "Jet OLEDB:Compact Without Replica Repair=False;" & _
    '                "Jet OLEDB:Bypass UserInfo Validation=False"
    

    Actually when changing the combination of connection string arguments I get the following error

    I am wondering whether this is the inner error? That is, I am running the access application that this template is wanting to use as a data source and the database is running in exclusive mode. I have tried 0 and 1 for the setting of

    Jet OLEDB:Database Locking Mode=1;

    But changing the setting to 0 does not change the outcome. Could running is exclusive mode be the cause and is there a workaround?

    Many thanks,

    Jonathan


    Jonathan

    Monday, March 2, 2015 7:48 PM
  • Hi Kiwi,

    Your connectionString is much complex, I think you could use with a simple connectionString to check whether a different combination makes any difference. The link below shows some information about connectionString.

    #MailMerge.OpenDataSource Method (Word)
    https://msdn.microsoft.com/en-us/library/office/ff841005.aspx

    For the error message, I will recommend you turn to the link below:

    http://www.source-code.biz/snippets/vbasic/10.htm

    Best Regards,

    Leo

    Tuesday, March 3, 2015 12:07 PM
  • Hi Leo, I got the initial connection string by recording a macro in Word. I have used the example from the Word link.

                    wordDocument.MailMerge.OpenDataSource _
                                Name:=gDbapp.Name, _
                                LinkToSource:=False, _
                                AddToRecentFiles:=False, _
                                SQLStatement:="SELECT TOP (1) * FROM '" & SelectQuery & "'"

    This causes the following prompt.

    When I click OK this triggers the following error.

    Word was unable to open the data source

    In case there was something wrong with the query used as a record source, I tried changing the sqlstatment to a simple connection to a small table.

    I'm sure that the solution is really simple when you know it!

    Many thanks,

    Jonathan


    Jonathan

    Friday, March 6, 2015 1:01 AM
  • Forgot to add that when I changed to using the connection instead of sqlstatement I got the following error message.

    Many thanks,

    Jonathan


    Jonathan

    Friday, March 6, 2015 1:05 AM
  • Hi Kiwi,

    If you manually create the Word merge, could you open the data source and get the Word merge worked?

    Regards,

    Leo

    Friday, March 6, 2015 2:54 AM
  • Hi leo, I can manually create document whilst access application is loaded. However, whenever I open the document I get the following prompt.

    I really want to avoid this as users will not know what to do with it. I guess after some training they will know to select Yes.

    Many thanks,

    Jonathan


    Jonathan

    Friday, March 6, 2015 3:30 AM
  • Hi Kiwi,

    As you have tried manually, I think you could record a macro and find the difference between your code and the code which was generated by macro. I have test the code in the link above, but I could not reproduce your issue.

    Could you share us a simple demo to reproduce your issue?

    Regards,

    Leo

    Friday, March 6, 2015 3:40 AM
  • Hi Jonathan

    You'll always get this message in the UI - it's a security thing. There is a Registry entry that can be changed to suppress this message: http://support.microsoft.com/kb/825765

    When using code to open a mail merge document the data source will ALWAYS be cut off (again, a security thing) and must be attached once more, which is what you're attempting to do.

    The reason you're getting the OLE DB prompt is because that code is incomplete: Word doesn't know what kind of connection technology to use. I'm surprised the macro recorder would do that... Which version of Word are you using?

    RE your earlier error: Access will deny connection if you've made changes in the VBA Editor - you've locked the database exclusively. Does it help if you close the database, open it non-exclusively and then run the code without doing any editing?

    Best for testing the connection would probably be to use a VBA macro in a Word document, with the database closed, until you get a good connection string, then carry that over to your Access VBA.


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by Kiwi Jonathan Wednesday, March 11, 2015 2:02 AM
    Friday, March 6, 2015 7:08 PM
    Moderator
  • Hi Cindy, your suggestion about opening Access in sharing mode (that is, without holding SHIFT) was a good one. This eliminated a bunch of errors!

    I Tried the method to change the registry entry. However, I still get the prompt. But, between us we have spent a lot of time on this and for now it will be end user training to click ok to confirm data source.

    Really appreciate the time that you and leo have put into helping me to resolve this issue.

    Many thanks,

    Jonathan


    Jonathan

    Wednesday, March 11, 2015 2:07 AM