• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1645
  • Last Modified:

Word 2010 can't open MS Access 2013 Parameter Query for merge

Hi folks,

I want to create a simple Word 2010 merge file for my wife.  The goal is to open the file and have it check an Access 2013 accdb file and run a query with a user-entered parameter.  Basic stuff, should be a piece of cake.

So I have the query with the parameter saved but when I go to open it as a data source from Word I do the following:

1) Use Existing List
2) Select accdb file
3) Get prompted to use OLE DB Database Files, click OK
4) Word binds to the table, no prompt to use the query

If I select Show All from Step 3 above, I get the following:

3) Get prompted to use OLE DB Database Files, check Show All
4) Get a list of all data formats, select MS Access Database via ODBC
5) Get a dialog "ODBC Microsoft Access Driver Login Failure" complaining that it can't find the .mdb file (WTF, Microsoft?)
6) Dismissing the dialog allows me to select the accdb file and now I can use the Options button to show queries.  
7) Selecting my parameter query results in Word saying "Word was unable to open the data source"

So what steps do I need to take to allow Word to see that query? I have tried the above and also attempted to invoke the Merge Wizard from Access with roughly the same result.

Please note that I can Google at least as well as you.  Link-only answers are not recommended.
0
Jason C. Levine
Asked:
Jason C. Levine
  • 10
  • 6
  • 6
  • +1
2 Solutions
 
GrahamSkanCommented:
Your question title defines the situation for everyone. Up to 2003, you could use DDE which opened the Access application and which then prompted for the parameters. OLE DB works directly on the database files, so the the database's user interface is not opened.

It should be possible to use VBA to modify the merge sql so that it can select from a stored but unparametrised query. However that might get a bit complicated, especially if you don't have VBA experience.
0
 
aikimarkCommented:
Please post the Word file.

My gut reaction is that this might be a connection string problem.
Secondary concern is a possible compatibility issue between Office 2010 and 2013 products.
0
 
Jeffrey CoachmanCommented:
I want to create a simple Word 2010 merge file for my wife.
Define "simple"?

In some cases a very "simple" mail merge can be simulated using MS Access only (in a report).

It only gets complicated when you need special formatting, or you have a lot of text.

Perhaps not really a "solution", ...Just putting this out there as an FYI...
Sample attached...
(The two available States for the parameter are NY and GA)

JeffCoachman
Database27.mdb
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Jason C. LevineNo oneAuthor Commented:
My gut reaction is that this might be a connection string problem.

Unlikely.  Word can connect to the accdb and see the table and get the data from it.  If I remove parameters from the query, Word can use it just fine.  This only fails when I add [Enter ZIP Code] to the Access query.
0
 
Jason C. LevineNo oneAuthor Commented:
Define "simple"?

Name, address, city, state, zip query from a single table with [Enter ZIP] as the parameter on the ZIP column.
0
 
aikimarkCommented:
@Jason

Do you see the prompt when you run the query from Word?
0
 
Jason C. LevineNo oneAuthor Commented:
No.  As stated in the original question:

7) Selecting my parameter query results in Word saying "Word was unable to open the data source"
0
 
aikimarkCommented:
I know you want this to work as configured, but I think any such dynamic filtering will have to be done by Word.  In the mail merge dialog, you should see the ability to filter the records.  This can also be automated with VBA code.
0
 
Jason C. LevineNo oneAuthor Commented:
Alright, so you and Graham both say that VBA is the answer.  What would that code look like?
0
 
aikimarkCommented:
Again, I'll ask...please post the Word file (or representative sample)
0
 
GrahamSkanCommented:
It would help to know what your level of expertise is.

However, to demostratre the principle,

in the immediate window, with your mailmerge main document selected, type this:
?activedocument.mailmerge.DataSource.QueryString {enter}
You should get something like:
SELECT * FROM `MyStoredQuery`
Now you will have to add a WHERE clause, so type something like this:
activedocument.mailmerge.DataSource.QueryString = "SELECT * FROM `MyStoredQuery` WHERE `ZIP` = '12345' {enter}

Then try the mail merge. If that works , we are on the way to building some code.
0
 
GrahamSkanCommented:
PS, note that database-specific names are protected with slanted apostrophes in this context.Text can make do with ordinary apostrophes (single quotes).
0
 
Jason C. LevineNo oneAuthor Commented:
It would help to know what your level of expertise is.

With VBA?  Minimal.  With overall stuff? I'm better than most, not as good as some.

Again, I'll ask...please post the Word file (or representative sample)

Right now it's a blank Word file with the Avery 5160 label format applied.  What are you expecting to see?

Then try the mail merge. If that works , we are on the way to building some code.

Will try and report back.
0
 
aikimarkCommented:
@Jason

At this point, I was interested in seeing your connection to the database and the mail merge configuration for a successful execution (non-parameterized query).  That will help the experts replace Word's Mail Merge dialog with a VBA routine.

I would expect that the database does not change between executions, so prompting for that might not be required if we know the database name and location (absolute or relative to the Word document or current user directory)
0
 
Jason C. LevineNo oneAuthor Commented:
mail merge configuration for a successful execution (non-parameterized query)

Oh.  Why didn't you say so in the first place?

Alright, I'm at my office where I don't have Access 2013 but I do have Access 2007 and the problem is more or less identical in each version.  Give me a few minutes to whip up sample data to protect the innocent.
0
 
Jason C. LevineNo oneAuthor Commented:
0
 
GrahamSkanCommented:
Are any of us truly innocent?

As an aside, knowing the query string as in my suggestion, would be sufficient to allow us, well me, anyway, to proceed.
In fact, provided we can be sure that your original mail merge document does not get saved with the specialisations from the code, we can probably proceed without any further information, except that we will need to know the name of the zip code field.
0
 
Jason C. LevineNo oneAuthor Commented:
in the immediate window, with your mailmerge main document selected

Uh, translation for the above please.  I don't know how to invoke the "immediate window"
0
 
Jason C. LevineNo oneAuthor Commented:
Are any of us truly innocent?

This. Is. Not. P&R.

except that we will need to know the name of the zip code field.

See sample accdb file and template above.  I'm happy to attempt to add VBA to the Word doc, no idea how to do it.

My wife is >< close to getting a PHP site that does this for her :/
0
 
GrahamSkanCommented:
The VBA Immediate window is where VBA statements can be executed on a one-off basis. You need to be in the VBA IDE to see it (Alt + F11) from the document window and you might need to open it via the View menu item.

Here is some speculative code to run your merge. You need it to be in the ThisDocument module of the MailMerge document that uses your non-paramitrised query.

Private Sub Document_Open()
    Dim strZipCode As String
    Dim doc As Word.Document
    Set doc = ActiveDocument
   
    strZipCode = InputBox("Please give the zip code", "Zip Code")
    doc.MailMerge.DataSource.QueryString = ActiveDocument.MailMerge.DataSource.QueryString & " WHERE `ZIP Code` = '" & strZipCode & "'"
    doc.MailMerge.Execute
    doc.Close wdDoNotSaveChanges
End Sub
0
 
Jason C. LevineNo oneAuthor Commented:
Uh, okay.  I have the code in the Immediate window of my Label Template-aikimark.docx.  What is supposed to happen?
0
 
aikimarkCommented:
Alt+F11 to open the VB project window.  I usually right click somewhere in the tree for the document and insert a module.  Past the code into the module.

I just got back from a meetup and probably won't be able to play with this until tomorrow.  The GrahamSkan code looks like something I would have suggested, so you are probably close to a solution.

You may need to save the document as a .DOCM file in order for the macros to be preserved.  Once the code does what you need it to do, your wife can launch it from the Ribbon or menu or you might customize the document with a button.
0
 
GrahamSkanCommented:
The macro that I gave needs to be placed in the ThisDocument module of the template. The mention of the Immediate window was an attempt to find the existing sql and to modify it manually.
If the document is a template and you use it as such (File/New), then the name of the macro will have to be changed:
Private Sub Document_New()
    Dim strZipCode As String
    Dim doc As Word.Document
    Set doc = ActiveDocument
   
    strZipCode = InputBox("Please give the zip code", "Zip Code")
    doc.MailMerge.DataSource.QueryString = ActiveDocument.MailMerge.DataSource.QueryString & " WHERE `ZIP Code` = '" & strZipCode & "'"
    doc.MailMerge.Execute
    doc.Close wdDoNotSaveChanges
End Sub 

Open in new window

0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 10
  • 6
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now