Solved

MailMerge VBA DataSource Connection Issue

Posted on 2016-11-08
10
64 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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
 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Make a Cell act like a Date 7 38
Excel VBA - macro in one workbook with another open 4 19
Calculation in Access 5 22
Dirty form - conditional formatting 5 7
I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

815 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

8 Experts available now in Live!

Get 1:1 Help Now