Multiple Access sessions appearing during amil merge from SQL Server

  • Thread starter Thread starter mdupris
  • Start date Start date
M

mdupris

OK, I'm baffled. I have an Access applicaiton fronting SQL Server and
used by 15-20 folks at any one itme. One of the features is to fire off
Word sessions to open documents which are mail merges into the active
record. The key peices of VBA code looks like this:

sSQL = "SELECT * FROM wherever WHERE whatever " = " & what2
sDSNName = "what3.dsn"
Set gobjWordApp = CreateObject("Word.Application")

gobjWordApp.Documents.Open FileName:=gsDocFullPathName,
ConfirmConversions:=False, _
ReadOnly:=True, AddToRecentFiles:=False, PasswordDocument:="",
_
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", Format:=wdOpenFormatAuto

gobjWordApp.ActiveDocument.MailMerge.OpenDataSource _
Name:=sDSNName, _
Connection:="FILEDSN=" & sDSNName & ";", _
SQLStatement:=sSQL, _
SQLStatement1:="", _
SubType:=wdMergeSubTypeWord2000

And the mail merge itself happens just fine. The problem is that
occasionally (and there seems no consistency here) a second Access
session is opened coincident to the mail merge. It is invisible to the
user; it can be killed within Task Manager with no impact to either the
session with the ADP file or the Word session. The killer problem is
that this Access session sucks up virtually all available CPU time
bringing things to a crawl -- until the process is killed at the system
level.
Anyone have a clue about what's happening here? Even better. anyone
know how to prevent it -- or at least detect it programmatically?
Thanx!

= Marchand =

Anyone
 
You don't show what happens to your gobjwordapp after you
are finished with it.

I normally use GetObject rather than CreateObject, but
there are arguments either way.

I don't use background printing, because in the end, the
papers are not going to come out of the printer any faster,
so I am able to kill my word object when the printing is
finished, if I want to, (which I don't, because I use
the same word object for the next operation).

Word will open a new Access object if it can't find an
available Access object.

I am surprised that your code requires an Access object
at all: I would have expected that to use ODBC, not an
Access object, but you need to ask that question in a
Word group.

If your code is actually using DDE to communicate with
an Access object, it will be looking for a Window with
the application title "Microsoft Access".

Any database which resets the application title to anything
else will not be found by DDE.

Or when you say that there is an 'Access Session' are
you sure that it is 'Access' you see, not the Access
odbc driver?

If it really is 'Access', you should also consider the
possibility that it is some other code which is starting
or leaving the rogue Access session.

(david)
 
reply. Thanks for your reply. No, I haven't tried the code outside of
Access. I'm not quite sure what you're getting at, though. The code
itself works fine: The Wor d session is created, documents opened, etc.
It's the occasional creation of the secondary, CPU-consuming Access
session that's baffling. Could you explain a bit more where you were
headed here?

= Marchand =
 
David,

Good stuff to ponder here. I've also used 'CreateObject' because I
thought that "GetObject' would fail if, in this example, there wasn't a
pre-existing open Word session. Obviously there's things I have to
explore here.

I'm not using background printing per se. I'm simply starting the Word
session, opeing the document and leaving it open for the user to
edit/print/save whatever. (Typically there are some minor,
personalizing edits they need to do.) However, it's possible that the
user could end the Access session with the Word session still open.
Could you explain more about "Word will open a new Access object if it
can't find an available Access object"? In this case, the Word session
was created by Access itself so there is definitely one available to
Word initially. But do you mean the Word session may on its own create
an Access object if, the originating Access object is closed with the
Word session still open?

At the end of the macro above, I do a "Set gobjWordApp = Nothing" to
clear the Word object from Access memory with no apparent ill-effect on
the Word session. Is this necessary? Could it part of the problem?

I perhaps didn't make it clear in my original post that the VBA code
posted is within an Access ADP file. It's Access itself which is
starting the Word session. What I'm assuming/intending that what code
does is establish a direct link between Word and SQL Server. Once
established, I'm also assuming that the continued existence of the
Access session is irrelevant. It's done it's job, Word is now talking
with SQL Server and Access can disappear. Although in practice the user
typically has more stuff in other parts of the application so the
Access session stays open a while.

The Access ADP application window's title remains "Microsoft Access"
throughout the session. When you write "are you sure that it is
'Access' you see, not the Access
odbc driver? " how would I determine that? Session Manager simply
reports it as "Microsoft Access" with no other details. How should I
dig deeper?

Thanks for all your hints so far!

= Marchand =
 
Normally Word 2003 Mail Merge uses an OLEDB connection
to a database. MSaccess.exe is not involved at all.

Office 97 used ODBC Mail Merge. MSaccess was not involved
at all.

Word For Windows used DDE Mail Merge. It started a DDE
session with Access. If DDE finds an existing copy of Access
running, it uses that. It looks for a window with the
title "Access Application". If DDE does not find a window
like that, it starts a new MSaccess.exe session.

All three forms of mail merge are still supported by
Access. OLEDB only links to 'views', not 'procedures',
so not all select queries in the database are visible.
This is the most common reason for using Office 97 or
Word For Windows mail merge instead of OLEDB.

Applications which are upgraded from Office 97 often
continue to use ODBC. Applications where OLEDB doesn't
work are often shifted back to DDE mail merge. Applications
which use a different application title were often shifted
from DDE to ODBC.

Unless you are using DDE mail merge, there is nothing
in your code which starts a copy of MSAccess.exe

Access will fail to close correctly if there are OLE
references to the Access object.

Access will sometimes fail to close correctly if it
is waiting to close some other object.

In some circumstances, Access has failed to close correctly
while it waits for other instances to close. I do not know
if this is still a problem.

I don't think you have correctly identified yet where the
second instance of Access is coming from - this is the
area where you should focus your attention.

You can look in the list of loaded files to see if MSAccess.exe
is loaded.

(david)
 
David,
thanks for your detailed reply. I absolutely agree with your
statement "I don't think you have correctly identified yet where the
second instance of Access is coming from" That is indeed precisely my
problem It simply appears on the system, under the user's login name
but is a separate process from the Access session running the ADP file.
This second session can be killed manually without impact on either the
Word session(s) or Access. There is nothing in the VBA code (itself in
Acceess) which creates another Access session. The second Access
sessions somehow simply comes into existence and sucks up huge amounts
of CPU. It continues to baffle me. Your description of the various
flavors of Word interfaces was enlightening. Thanx!

= Marchand =
 
Oh, I was only thinking about the possibility that the second Access
application could have been created by something else than your piece of
code (either when running or not inside Access).

Executing it from somewhere else could have put some more light on this
problem.
 
Back
Top