MailMerge VBA DataSource Connection Issue

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.
Access57Asked:
Who is Participating?
 
GrahamSkanRetiredCommented:
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
 
GrahamSkanRetiredCommented:
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
 
Access57Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
GrahamSkanRetiredCommented:
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
 
Access57Author Commented:
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
 
GrahamSkanRetiredCommented:
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
 
Access57Author Commented:
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
 
Access57Author Commented:
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
 
Access57Author Commented:
GrahamSkan -

Thanks, I appreciate it
0
 
Access57Author Commented:
Thanks, I appreciate your guidance, persistence, and expert knowledge in resolving this
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.