Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access Mail Merge Fails With Sharepoint Tables

Posted on 2016-08-27
27
Medium Priority
?
185 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 22
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 22
ID: 41773706
open the website and sign in
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 22
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 22
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 22
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 22
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 22
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
 
LVL 22
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 22
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 22
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 22

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 2000 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 22
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 22
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 22
ID: 41778584
you're welcome, Mitch ~ happy to help
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

927 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