Solved

Access Mail Merge Fails With Sharepoint Tables

Posted on 2016-08-27
27
64 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 19
ID: 41773696
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
ID: 41773705
The Word user is the same user who is running the Access program. Please explain what you mean by "sign into SharePoint".
0
 
LVL 19
ID: 41773706
open the website and sign in
0
 
LVL 1

Author Comment

by:ms3930
ID: 41773707
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 19
ID: 41773711
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
ID: 41773715
I logged into the S.P. site, but still get the errors.
0
 
LVL 19
ID: 41773720
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 19
ID: 41773723
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
ID: 41773725
Can't link to a linked table.
0
 
LVL 19
ID: 41773736
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
ID: 41773741
Added the trusted location and enabled all macros in Word. Versions are the same. Still get the error.
0
 
LVL 19
ID: 41773752
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
ID: 41773754
Using a copy purchased on a CD.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 19
ID: 41773755
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
ID: 41773774
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 19
ID: 41773792
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
ID: 41773811
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 19
ID: 41773817
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
ID: 41773866
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 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41773873
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
ID: 41773898
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 19
ID: 41773910
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
ID: 41773979
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
ID: 41774317
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 19
ID: 41774596
thanks, Mitch. I can't look till later -- perhaps someone else will jump in before that
0
 
LVL 1

Author Closing Comment

by:ms3930
ID: 41775514
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 19
ID: 41778584
you're welcome, Mitch ~ happy to help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

911 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

22 Experts available now in Live!

Get 1:1 Help Now