Solved

MailMerge VBA DataSource Connection Issue

Posted on 2016-11-08
10
34 Views
Last Modified: 2016-11-10
Receiving Error 5922, "Word was unable to open the data source" when I run the following VBA code in MS Access:

     strSQL = "SELECT * FROM " & "'" & "tbl_Header" & "'"

      With wrdDoc.MailMerge
         .MainDocumentType = wdFormLetters
         .OpenDataSource _
            Name:="R:\EDI_Data.accdb", _
            Format:=wdOpenFormatAuto, _
            AddToRecentFiles:=False, _
            LinkToSource:=True, _
            Connection:="TABLE tbl_Header", _
            SQLStatement:=strSQL, _
            SubType:=wdMergeSubTypeAccess
         .Destination = wdSendToNewDocument
         .Execute Pause:=False
      End With

Open in new window

Basically, I am running MS Access 2016 and I want to automatically create a text file using MS Word MailMerge.

When I run the above code, I get a "Confirm Data Source" pop up box that has "OLE DB Database Files" as the default selection (or I can select MS Access Databases via ODBC).

I have tried numerous variations, but no matter what I try, it always opens the word doc, asks to confirm the Data Source, and gives me an error message that it could not open the Data Source.  Also, I am able to open the document manually with no problem; but I need this to work automatically using VBA in MS Access.
0
Comment
Question by:Access57
  • 6
  • 4
10 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 41879616
The Connection argument sould be a connection string, such as:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Persist Security Info=False;
0
 

Author Comment

by:Access57
ID: 41879746
GrahamSkan - thanks for the response, but that did not resolve the issue yet.

I have tried various alterations and discovered that it (sort of) works if I remove the Connection, SQL Statement, and SubType - it opens word with the correct data source file, but I get a pop-up box to select the table / query / range.  If I manually make the selection, it will proceed through the end of the code.

So it looks encouraging that my code is close; but I am still need to resolve the Connection, SQL Statement, and SubType.

(Does it matter what the template has? That is to say, the current mail merge template already has a data source, Access table, and filter saved.  I plan to use the same data source file and table, but the filter will vary.)

Thanks!
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 41880752
Another thing, and I should have spotted it earlier, the SQL value looks wrong. Assuming that tbl_Header is the table name, and not a variable whose name is such, you simply need
SQL = "SELECT * FROM`tbl_Header`"

Open in new window

The SQLStatement argument is a bit fussy about its internal string delimiters. It seems to wants the backward-sloping apostrophes as I have shown.
0
 

Author Comment

by:Access57
ID: 41880757
GrahamSkan - thank you!

I am busy at the moment, but will give this a try later today.  It seems like I am really close; hopefully, a tweak here or there and I can get it working.

Well let you know how it works.

I appreciate it.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 41880766
The OpenDataSource statement should override any previous OpenDataSource settings as well as filter settings.
You might like to try recording a macro as you set the datasource manually. You get a lot of unnecessary stuff (mostly default settings), but the recorded macro should work.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Access57
ID: 41880771
Duly noted and thanks for clarifying this.

Will use the record macro feature and see what happens - will let you know how it goes when I have some time to focus on this.

Thanks!
0
 

Author Comment

by:Access57
ID: 41881726
Yes, the backward-sloping apostrophes solved the problem; the code above is working!

I should have noticed them when I opened the mail merge doc and it confirms the data source, the pop-up box shows the SQL statement (but the backward-sloping apostrophes are very tiny and hard to see).

If I could trouble you for one more question?  I need to filter the data by adding the where clause and I cannot get this SQL statement to work, do you see anything wrong with this?  (except for the variable, it is identical to the pop out box I get)

      strSQL = "SELECT * FROM `tbl_EDI_856_Out_Header` WHERE `BillOfLadingID` = `"  & strBillOfLadingID & "`"

If not, I can create a temp table that is populated with only the one record and link to that (I tried it and it works, but lots of overhead).

Either way, I will close out the question as soon as I hear back from you.
And thanks, you have saved me hours of time and much frustration!
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 41881900
It is only necessary to protect the database component names with the backwards apostrophes.
strSQL = "SELECT * FROM `tbl_EDI_856_Out_Header` WHERE `BillOfLadingID` = " & strBillOfLadingID

Open in new window

Strings would  need the standard apostrophe
strSQL = "SELECT * FROM `tbl_EDI_856_Out_Header` WHERE `BillOfLadingID` = 'AA1'"

Open in new window

0
 

Author Comment

by:Access57
ID: 41882156
GrahamSkan -

Thanks, I appreciate it
0
 

Author Closing Comment

by:Access57
ID: 41882165
Thanks, I appreciate your guidance, persistence, and expert knowledge in resolving this
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

760 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

19 Experts available now in Live!

Get 1:1 Help Now