Q: I'm working on a complicated project in Access 2002 (XP). Although I believe this project is better suited for VB.NET, the powers that be insist I work in Access. The application is designed to export multiple Word objects into a single Word document. The problem is, when my users run the application, it sometimes crashes. Then the user has to close Word (using File > Close). However, an instance of Word is still open. My users can't export any more Word objects until they open the Task Manager and close that instance of Word.
I read in the October 2004 issue about shutting down Excel from VB6 and VB.NET. I need to do something similar, but with VBA in Access 2002 (XP). How ca n I accomplish that?
-- Kyle Mankes, Raleigh, North Carolina
A: The original VB6 code from the October 2004 issue works for Word as well as it works for Excel. It looks like this:
oWord.Application.Quit()
Set oWord = Nothing
The key here is the Quit method, which tells Word to do its proper cleanup and shutdown before you dereference the instance of Word by setting the object variable to Nothing. Just setting the object variable to Nothing doesn't necessarily shut down Word, because Word has its own handles open to other system resources it won't release until it goes through the shutdown procedures in the Quit method.
The tricky part about all this is when Access crashes. Because Access has the references to Word, Access has to run the code to shut down Word. You could put the cleanup code in some kind of error handler so Access shuts down Word properly on an error, but you're probably not going to be able to do any error handling in a crash. After the Access instance dies, the instance of Word is an orphan. And, as you've discovered, the only way to get rid of it is to directly kill it with Task Manager.
You can add the ability to kill orphaned Word instances in your Access application, which requires writing code to use the Win32 API directly. The knowledge base article at http://support.microsoft.com/kb/176391/EN-US focuses on finding an instance of an application by name and then politely asking it to shut down by using the PostMessage API call to send a WM_CLOSE message to Word. If that doesn't work (you can set a timer for how long to wait), you can rudely shut it down with the TerminateProcess API call.
Either way, you can automate the cleanup process for the orphaned Word instance. You can also set it up in a loop so that it keeps looking for and killing instances of Word until they're all removed from memory. Just be aware there's no way to tell one instance of Word from another, so you might be getting a little rough on your user's machine. Creating a "kill all Word instances" procedure that runs whenever your application starts doesn't seem appropriate; it might make more sense to add a cleanup button on the application somewhere.
Using TerminateProcess can also leave other orphaned processes behind. Another name for an orphaned process is a memory leak. To clean things up properly, you'll have to reboot a system whose applications are crashing and leaking regularly, and even then, damage can occur, such as registry corruption and damaged files.
Another approach to this problem is to create an out-of-process executable as an intermediary application that sits between Access and Word. Because the application is out-of-process, when Access crashes, it continues to function. Given that you can detect in the application that Access has died, you'd be able to command Word to shut down properly, and then the application would shut itself down as well. This is a complex approach to the problem because you'd be building an ActiveX .EXE (effectively like Word itself), but the difference is you're prepared with automated shutdown procedures if things go badly.
The other consequence of the intermediary approach is it's slower and consumes more memory. Because the intermediary holds the references to Word, all calls to Word have to go through it, and that extra layer of calling, especially out-of-process, takes time. How much time it takes depends on the processing speed and amount of memory available in the user's computers.
I'm not wild about either of these approaches, because ultimately they're just covering up the fact that Access is crashing periodically. Getting rid of the crashes is the best solution, but it may not be possible. Moving to Visual Basic 6 or to .NET certainly would address the Access crash issue by eliminating Access, but that's a lot of code to rewrite. You'll have to weigh carefully the costs of crashing against the costs of rewriting.
-- Richard Campbell, Access Advisor technical editor