Access Mail Merge Fails With Sharepoint Tables

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
LVL 1
ms3930Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Mitch,

the Word user needs to be approved for security. Whoever is using Word should sign in to SharePoint first
0
 
ms3930Author Commented:
The Word user is the same user who is running the Access program. Please explain what you mean by "sign into SharePoint".
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
open the website and sign in
0
 
ms3930Author Commented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
interesting ... what if you put the linked SP tables in another BE (back-end) and link to that BE from Access?
0
 
ms3930Author Commented:
I logged into the S.P. site, but still get the errors.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
ms3930Author Commented:
Can't link to a linked table.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
ms3930Author Commented:
Added the trusted location and enabled all macros in Word. Versions are the same. Still get the error.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Are you using Office 365? or desktop versions linked to SP?

also allow trusted locations on network in Access
0
 
ms3930Author Commented:
Using a copy purchased on a CD.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
that is probably why Word has issues. What if you use Word Merge feature and choose your SP list? Can you do it?
0
 
ms3930Author Commented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
I have discovered the multi-user issue connecting when using DAO ... but if you use ADO to connect then Access can be open
0
 
ms3930Author Commented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
ms3930Author Commented:
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
 
ms3930Author Commented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
I will have to set up an experiment ... perhaps you can post sample documents so it will go faster for me? thanks
0
 
ms3930Author Commented:
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
 
ms3930Author Commented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thanks, Mitch. I can't look till later -- perhaps someone else will jump in before that
0
 
ms3930Author Commented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Mitch ~ happy to help
0
All Courses

From novice to tech pro — start learning today.