Solved

Access Mail Merge Fails With Sharepoint Tables

Posted on 2016-08-27
27
155 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 13
27 Comments
 
LVL 21
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 21
ID: 41773706
open the website and sign in
0
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!

 
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 21
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 21
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 21
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 21
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 21
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 21
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 21
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 21
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 21

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

Featured Post

Technology Partners: 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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

623 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