Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MailMerge VBA DataSource Connection Issue

Posted on 2016-11-08
10
Medium Priority
?
271 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
[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
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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