Hyperlink to macro-enabled Word is unpredictable

[sheila.moore]

Using Access 2010 and I've been researching hyperlink to macro-enabled Word for many days. Here is what it does. When I first add the link, it will start out going straight to the "Do you want to run this Macro Yes/No" screen, like it should. I click "Yes" and it works fine, but about the 4 or 7 time, the message, "Do you want to run this Macro Yes/No" suddenly displays behind the Access screen, I can't Enter, or see it. It locks up Access and I have to kill the session in order to get out of this. As soon as I kill the Access screen, there is my "Do you want to run this Macro Yes/No" screen left behind. So it's not that the link is failing, sort of, it's that the Word Macro option is moving from the front screen where I need it to be, to the screen behind Access, where I can't get to it.

Anyone ever had this happen before? I've tried separate command buttons, labels with hyperlinks, macros and VBA code to run this. It always starts out fine and eventually fails. I've also searched the internet without luck. Very frustrating. Thanks for any suggestions.

Sheila

[Micron]

Thanks for any suggestions.

No response yet, so I'll offer this. I would never use hyperlink function to open another application. That was designed to open web pages, for which the default handler is a web browser. Since then, security issues have made this a buggy option for accessing anything from disk (local or network), which is why I have little experience with it. My approach these days is to use Automation or the Shell function. Perhaps the security prompt sometimes appears in the background due to the speed at which Windows is processing tasks, but you could probably get to it via ALT>TAB keyboard combination without having to shut anything down. The fact that there is a prompt when using the hyperlink approach suggests to me that the Word file might not be in a trusted location, but I confess I am not a Windows security guru.

[ItsMe]

I do not have experience with that happening, but I believe I understand why and what is happening. Aside from using a more reliable approach, such as what Micron has described, you can add your Word Doc to your Access' Trusted Locations. You can go to Options within Access by Right Clicking the Ribbon or via the Backstage >2007 or Office Button in 2007. There, you will see a section for Trusted Locations.

I use VBA code and Registry edits to manage such issues. So I cannot offer much detail on how to add stuff to trusted locations via the Options.

[HiTechCoach]

Using Access 2010 and I've been researching hyperlink to macro-enabled Word for many days. Here is what it does. When I first add the link, it will start out going straight to the "Do you want to run this Macro Yes/No" screen, like it should. I click "Yes" and it works fine, but about the 4 or 7 time, the message, "Do you want to run this Macro Yes/No" suddenly displays behind the Access screen, I can't Enter, or see it. It locks up Access and I have to kill the session in order to get out of this. As soon as I kill the Access screen, there is my "Do you want to run this Macro Yes/No" screen left behind. So it's not that the link is failing, sort of, it's that the Word Macro option is moving from the front screen where I need it to be, to the screen behind Access, where I can't get to it.

Anyone ever had this happen before? I've tried separate command buttons, labels with hyperlinks, macros and VBA code to run this. It always starts out fine and eventually fails. I've also searched the internet without luck. Very frustrating. Thanks for any suggestions.

Sheila

Sheila, This is a common issue when do you a mail merge with an Access database. If the database is already opened when your start Word and open the merge document then you commonly have the issues yo are experiencing.

As mentioned, the best solution is to use an Access report. My client regally generate letters with an Access report.

If you must use Word to get some special formatting the Access reports can't do then thew trick is to NOT merge with an Access query. What you do is save the query to a CSV file. Use this text file as the merge source data.. This avoids all the issues with Word having to open an additional instance s of Access. My applications use this to generate sales and marketing letters.

[ItsMe]

...What you do is save the query to a CSV file. Use this text file as the merge source data...

This is interesting. Is it your experience that Word locks the Access file when it makes a connection?

[HiTechCoach]

This is interesting. Is it your experience that Word locks the Access file when it makes a connection?

It is worse than that. It also starts another instance of Access and ...

From another Fellow Microsoft Access MVP Albert Kallal:

Don't let word attached to you running mdb file.
Again, this is just common sense. A large portion of word merge examples actually let word OPEN your mdb file. This is a formula for disaster. Allowing word to open the mdb file means that you have to deal with some known bugs (like word launching a second copy of ms-access for example). Further, if you implement security in ms-access, now word has to deal with passwords and permissions (again, a huge can of worms). Worse is if you have the runtime of ms-access installed, or multiple versions of ms-access, then word can again cause all kinds of problems as it tries to launch ms-access, and it might even launch the wrong version of ms-access. I could again rant on for pages here, but I think any developer can clearly see that if we prevent word from trying to attach to the ms-access mdb file, then we avoid a TON OF POSSIBLE problems. Again, since we CAN control this, then lets do so. As a result, my sample merge code DOES NOT let word attached to the mdb file. As a result, it is rock solid. As a result, it just works!

From: http://www.kallal.ca/wordmerge/page2.html

****>>> This was writing before Access 2007 and the new ACE (.accdb) format, it still holds true with Office 2007 and later.

[ItsMe]

I would like to understand more about the symptoms others have experienced. I prefer using a Report over Word Merge. However, sometimes MergeFields can be convenient. Before, I would not recommend them. Now, I see more uses for Merge Fields. I can see what you are saying about using a query object. Perhaps creating a separate access file that has the query object is a solution. Dedicate an Access file for Word. I have workstations that simultaneously use multiple Access FE's. I have not noticed this to be a problem.

I looked at some of my code samples to see if there is a better way I can be doing things. In my code examples, I am not using query objects located in Access. Instead, I am connecting to the backend via ACE and using an SQL statement. There are parameters for the OpenDataSource method to open the source as ReadOnly and OpenExclusive as well. I feel OK about this approach. Here is some code from my samples.

strConnection = "Provider=Microsoft.ACE.OLEDB.15.0;" & _
                "Data Source=C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE15\ACEOLEDB.DLL;"
 objMailMerge.MainDocumentType = 0
 objMailMerge.OpenDataSource "C:\Test\AccessFiles\PDFfromReport.accdb", , , True, True, False, , , , , , strConnection, "SELECT * Table2"

[HiTechCoach]

I would like to understand more about the symptoms others have experienced. I prefer using a Report over Word Merge. However, sometimes MergeFields can be convenient. Before, I would not recommend them. Now, I see more uses for Merge Fields. I can see what you are saying about using a query object. Perhaps creating a separate access file that has the query object is a solution. Dedicate an Access file for Word. I have workstations that simultaneously use multiple Access FE's. I have not noticed this to be a problem.

I looked at some of my code samples to see if there is a better way I can be doing things. In my code examples, I am not using query objects located in Access. Instead, I am connecting to the backend via ACE and using an SQL statement. There are parameters for the OpenDataSource method to open the source as ReadOnly and OpenExclusive as well. I feel OK about this approach. Here is some code from my samples.

strConnection = "Provider=Microsoft.ACE.OLEDB.15.0;" & _
                "Data Source=C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE15\ACEOLEDB.DLL;"
 objMailMerge.MainDocumentType = 0
 objMailMerge.OpenDataSource "C:\Test\AccessFiles\PDFfromReport.accdb", , , True, True, False, , , , , , strConnection, "SELECT * Table2"

You code is is using: objMailMerge.OpenDataSource set to an access database so same old issues. You may also have code to open a connection to the back end via ACE and using an SQL statement but as fas as I not hat is NOT used by the Word Merge feature. Unless you have writing your own Word Macro (VBA) code to open a recordset to the Access back end and do all the merging yourself you still have the same issues. If you want to see it your code is better then start the merge and in watch the task manager to see if Word still started a new hidden instance of Access. I am betting it does.

You might be able to get away with what you are doing if you have completer control over the Client setup (what software gets installed) and on-site to support when it has issues.

I have commercial solutions that I must be able to run on any client setup. I have no control over the versions of Access and Word that are installed. I have been using the exact same code to do Word mail merge for over 15 years and have never had to modify the code. It works with All version sof Access since 2000 and with any version of word. By creating a Word merge doc (text file) it just works. I never have to worry about it what version of Access or Word is installed..

I think a lot of people here avoid Word Merge and use an Access reports is because of this issue. Most people do not know that it works great is you NEVER merge directly with an Access database.

I would NEVER do what you are doing. Even if I did not have to support so many client configurations. There is just NO need to ever let Word open an instance of Access just to use a table or query object. Even if it is in a separate temp/scratch back end just for the merge. It is such a simple code change to insure it just works.

I am in total agreement with Albert Kallal, Microsoft Access MVP, on this.

Have you seen Albert's: Super Easy Word Merge (Click here and still down to find it).

[ItsMe]

First, I want to be clear that I am not in disagreement. I am only trying to understand the disadvantage to multiple instances. I hear your comment about different versions. I have posted many times how I do not recommend different versions of Access open the same FE file. People have even countered my argument. I am just not seeing the conflict and was asking for some insight.

[HiTechCoach]

With Windows 64-bit that has lots of RAM (a minimum of 8 gig. 12 or more is better) then having multiple instances of Access open at the same time if usually stable. In my experience a 32-bit OS becomes problematic with multiple instance of Access open at the same time.

Word has been notorious a keeping the hidden instance of Access open even after the merge has completed. If the merge is repeated without rebooting Windows first then things can go bad in a hurry.

I also find that merging is a lot slower with an Access database because it has the overhead of starting and closing an instance of Access with each merge operation.

Merging with a text file is just simple, clean and fast.

[ItsMe]

Those are some good points. Thanks for posting them.

[HiTechCoach]

Those are some good points. Thanks for posting them.

You're welcome.

[sheila.moore]

I just joined this Forum and I have to say how thrilled I am with the support, suggestions and concerns here. Thank you everyone! I plan to read and reread all the comments over the next few days.

I am using a trusted link as it is the same network drive shared at the business I work.

Word doesn't actually lock up - I just can't get to the screen to click "Yes" (run this macro) because it's behind the Access screen I'm working on, sometimes. Sometimes the Word screen appears to me, as it should, the instance I click the hyperlink and I can click "Yes" to run the macro. The times it fails, it is not visible to me, but I can hear it ping on the screen and as soon as I kill Access, the Word macro "Yes" is waiting for me!

I am new to Access and my position so I was taking the mail merge already created before me to make my life easier. Truthfully, the users complained about leaving Access to print in Word but now I see it's easier that way or I could write a new report, which seemed to work fine the other day.

I'm learning something new daily and even though I think there are holes in my brain, I'm trying to soak it all in!!

My kindest regards - Sheila

[ItsMe]

...
I am using a trusted link as it is the same network drive shared at the business I work...

You might want to consider going to Word and adding the Folder where the Word Doc resides to Word's Trusted Locations.

[HiTechCoach]

You might want to consider going to Word and adding the Folder where the Word Doc resides to Word's Trusted Locations.

It is not really a trusted document or trusted locations issue. Otherwise the merge would never even start.

Is is an issue created by Word when it launches the hidden instance of Access. The focus of the active Access Application gets whacked.

In Sheila's database it is using a hyperlink to open Word. Not Word Automation. This is more common when Word Automation is NOT used with an Access database as the merge source. This has been a "bug" with Word Mai Merge for a for a log time now.

The good news is this can easily be fixed by NOT using an Access database as the merge source.

Another workaround is to ALWAYS close all instance of Access before you open Word.