Solved

MailMerge VBA DataSource Connection Issue

Posted on 2016-11-08
10
48 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

895 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

17 Experts available now in Live!

Get 1:1 Help Now