Solved

Access Mail Merge Fails With Sharepoint Tables

Posted on 2016-08-27
27
45 Views
Last Modified: 2016-08-31
I'm using automation in Access 2013 to create merged documents from Word templates. The VBA code works fine when the data is in Access tables, but throws errors when I run it with the data in Sharepoint lists. The data sources are non-parameter queries. Here is the code I am using:

    Dim objWord As New Word.Application
    Dim objDoc As Word.Document
   
   objWord.Application.Visible = True
   Set objDoc = objWord.Documents.Open(strTemplateDocName)
   objWord.Application.Visible = True
 
strSQL="SELECT * FROM qrySource WHERE CustomerID=1;"  [typical]

objDoc.MailMerge.OpenDataSource _
        Name:=sCurrentDBPath, _
        LinkToSource:=True, AddToRecentFiles:=False, _
        Connection:=sQueryName, _
        sqlstatement:=strSQL

When the last statement above is executed, I get 2 error messages:
1. "The database has been placed in a state by usr 'Admin' ....". It then prompts for the data source.
2. "Error 5922: Word unable to open data source.."

When I run the queries by themselves, they run w/o error.

When I test the connection to the data source, it says: "Error in initializing provider".

Is there something about linking to the S.P lists that causes the error?

Mitch
0
Comment
Question by:ms3930
  • 14
  • 13
27 Comments
 
LVL 18
Comment Utility
Hi Mitch,

the Word user needs to be approved for security. Whoever is using Word should sign in to SharePoint first
0
 
LVL 1

Author Comment

by:ms3930
Comment Utility
The Word user is the same user who is running the Access program. Please explain what you mean by "sign into SharePoint".
0
 
LVL 18
Comment Utility
open the website and sign in
0
 
LVL 1

Author Comment

by:ms3930
Comment Utility
Additional testing produced the following:

1. In an Access file with only local tables, the mail merge (thru Automation) works fine.
2. If I then add a link to a single Sharepoint list, the merge fails (even though the newly-linked table is not involved in the merge).
0
 
LVL 18
Comment Utility
interesting ... what if you put the linked SP tables in another BE (back-end) and link to that BE from Access?
0
 
LVL 1

Author Comment

by:ms3930
Comment Utility
I logged into the S.P. site, but still get the errors.
0
 
LVL 18
Comment Utility
perhaps the versions are different .. in the Immediate (Debug) window of each, type
?application.Version

Open in new window


1. press Alt-F11 to go to VBA
2. press Ctrl-G to go to the Debug window
0
 
LVL 18
Comment Utility
another thought is the Trust Center Settings for Word:

File > Options > Trust Center

click Trust Center Settings

from the left list, choose Trusted Locations

on the right, check the box on the bottom that says "Allow Trusted Locations on my network "

also, here is where you can add specific trusted locations
I like to choose roots (ie: c:\)
and check box that says "Subfolders on this location are also trusted"
0
 
LVL 1

Author Comment

by:ms3930
Comment Utility
Can't link to a linked table.
0
 
LVL 18
Comment Utility
I thought that might be the case but worth a try -- check the Trust Center Settings ... for Access and Word -- steps are the same. Also make sure the application versions are the same
0
 
LVL 1

Author Comment

by:ms3930
Comment Utility
Added the trusted location and enabled all macros in Word. Versions are the same. Still get the error.
0
 
LVL 18
Comment Utility
Are you using Office 365? or desktop versions linked to SP?

also allow trusted locations on network in Access
0
 
LVL 1

Author Comment

by:ms3930
Comment Utility
Using a copy purchased on a CD.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 18
Comment Utility
that is probably why Word has issues. What if you use Word Merge feature and choose your SP list? Can you do it?
0
 
LVL 1

Author Comment

by:ms3930
Comment Utility
The plot thickens! When I open a merged Word doc, I cannot complete the merge if the Access file that contains the data source is open. If the Access file is not open, I can complete the merge in Word.

One other thing: the errors occur even when I don't choose an SP list as the data source.
0
 
LVL 18
Comment Utility
I have discovered the multi-user issue connecting when using DAO ... but if you use ADO to connect then Access can be open
0
 
LVL 1

Author Comment

by:ms3930
Comment Utility
It's not a multi-user issue, since there is only one Access file open at the time of the merge. The open Access file is the one running the VBA code.

I'm going to try another approach in which a second Access file (w/o SP links) will serve as the data source for the merges. I'll try anything at this point.

Thanks for your help, Crystal.
0
 
LVL 18
Comment Utility
you're welcome, Mitch

> "I'm going to try another approach in which a second Access file (w/o SP links) will serve as the data source for the merges. "

That should work. And should be ok with links to Access tables too.

Does Word need to use SP lists? If so, try merging directly to SP with Word -- we can attempt to automate it better once that works ... or doesn't, we can look for more answers
0
 
LVL 1

Author Comment

by:ms3930
Comment Utility
You've been very generous with your time today, Crystal. I'll get back to this question after I've tried my alternate solution.
0
 
LVL 18

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
Comment Utility
you're welcome ... please let me know how it works out

> "It's not a multi-user issue, since there is only one Access file open at the time of the merge. The open Access file is the one running the VBA code."

yes it is ... substitute "process" for "user" ...

you can even have these issues by yourself ~ One process has the Access db open. Another is trying to use it.
0
 
LVL 1

Author Comment

by:ms3930
Comment Utility
OK. I'm listening. You've seen my code (repeated below). How would you revise it to use ADO? Keep in mind, there are no Recordset or db objects open when this code is running:

Dim objWord As New Word.Application
    Dim objDoc As Word.Document
   
   objWord.Application.Visible = True
   Set objDoc = objWord.Documents.Open(strTemplateDocName)
   objWord.Application.Visible = True
 
strSQL="SELECT * FROM qrySource WHERE CustomerID=1;"  [typical]

objDoc.MailMerge.OpenDataSource _
        Name:=sCurrentDBPath, _
        LinkToSource:=True, AddToRecentFiles:=False, _
        Connection:=sQueryName, _
        sqlstatement:=strSQL

Open in new window

0
 
LVL 18
Comment Utility
I will have to set up an experiment ... perhaps you can post sample documents so it will go faster for me? thanks
0
 
LVL 1

Author Comment

by:ms3930
Comment Utility
There is much code that is executed before the lines I posted. I'll need to pare it down to post the accdb file.
0
 
LVL 1

Author Comment

by:ms3930
Comment Utility
I've attached two files to assist in finding the problem:

1. MailMergeExample.accdb: This has a query, some tables, and a module containing 2 subs that create the merged document.
2. A Word file I use as a template for the merge
MailMergeSample.accdb
Worksheet_AdDesign_Template.docx
0
 
LVL 18
Comment Utility
thanks, Mitch. I can't look till later -- perhaps someone else will jump in before that
0
 
LVL 1

Author Closing Comment

by:ms3930
Comment Utility
I awarded the points to Crystal because she offered several possible solutions to my issues. Based on two of them, I was able to arrive at what I believe to be the optimum solution. FGor what it's worth, part of my problem was caused by an open recordset (which Crystal suggested). The remainder of the solution was for me to implement a workaround.

Thank you Crystal.
0
 
LVL 18
Comment Utility
you're welcome, Mitch ~ happy to help
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Summary In SharePoint 2010 it is easy to create custom color themes to jazz up a site. Theme colors can also be created in PowerPoint 2010 with a few clicks. But how do the chosen colors actually look in the SharePoint site? The attached PowerPoint…
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now