How to handle custom-messages in VBA from a custom C++ DLL
LhK-Soft
I'm active in VC++ for several years now, so I know some things (I think).
Using MS-Access I store a db with lots of entries and uses the VBA
techniques to export those data to e.g. HTML, XML and other kind of data.
I'm using MS-VC++6.0-Enterprise (with sp6) at a NT4-Workstation (sp6a) and
MS-Access97 (from MS-Office97). Reason that I won't upgrade to latest
Office/VisualStudio is that I must ensure that any application I develop
still works at Windows9x (and so on) - so for full compatibility with any
configuration.
Lately I ran into a problem, while I use an external function from a VC++
DLL (DLL build as a 'MFC Regular DLL' using dynamic linkage over MFC) to
export all my data in a fixed file (must use the DLL for performance, while
I
first create a directory with files in VBA then use the functions inside the
DLL to grab the contents of the exported directory and put it all into a
binary file - it's intended to be used by an installation procedure for
customers).
The DLL creates a worker-thread in which it creates a modal-dialog box using
'DialogBoxIndirectParam'. This Dialog stays on top of the calling HWND until
it receives a message from the calling thread that it may close (using
'EndDialog').
From start-up until end of the dialog it receives custom message
(combinations of WM_USER) to display a progress-bar of the running thread.
This works fine when calling from a MFC-module (.exe) but appearently it
doesn't when called from MS-Access: the dialog popups, but halfway it stops
receiving messages and in rare cases it does handle all send messages, but
then it won't disapear from the desktop when the dll has finished.
I gues the problem is MS-Access, maybe it's STA (single threaded) while my
DLL is multi-threaded, so I would try another solution : sending the
progress
messages directly to the calling Access module. But then I come into my
problem.
How can I let MS-Access react at my own 'custom' messages, while the VBA
module is still 'waiting' for my DLL to finish.
I already tried to develop a COM object, but while this asks a lot of
development it didn't resolve my problem!
If anyone would know a good suggestion/answer for me, I would appreciate it
very much. Currently I run the exported DLL-function in VBA without popping
up the progress dialog (Access is blocking anyway while my DLL is in
progress, and that can take up a very long time, while I've got a lot of
data
in the DB), so Access won't react strange anymore.
I also tried to 'talk' directly to MS-Access by linking to the Access-COM
module in VC++ so I could try to get an interface to the Access.Application
object and so modify the statusbar, but as soon I do MS-Access popups with a
Kernel failure !?
So I come with my question, would it be possible to run my DLL-procedure in
a seperate thread, so Access can go into idle-status, so it could react to
custom messages I send from the DLL, so Access can write to the statusbar
itself...?
But is it possible at all, to launch a seperate thread in MS-Access? Or
should I better call a seperate thread from inside the DLL and then return
immediately so I could have MS-Access to go into a timer-loop (event
'OnTimer') - but then I still don't know how to react at custom messages
from
my DLL.
Thank you very much if you can help me!
--
Laurens Koehoorn
\\\ | ///
( @.@ )
+-----------oOOo------(_)------oOOo---------------+
Author of VCDwizard, get your copy at
http://www.labdv.com/vcdwizard/
Author of lkVCDimager, get your copy at
http://lkvcdimager.sourceforge.net/
Bonj
> The DLL creates a worker-thread in which it creates a modal-dialog box
This is a bad idea.
For optimum stability, an application should do *all* user interface
interaction in a single, primary UI thread.
There are exceptions to this rule (see 'UI threads') , but AFAIK they are
rare!
using
> 'DialogBoxIndirectParam'. This Dialog stays on top of the calling HWND
> until
> it receives a message from the calling thread that it may close (using
> 'EndDialog').
> From start-up until end of the dialog it receives custom message
> (combinations of WM_USER) to display a progress-bar of the running thread.
> This works fine when calling from a MFC-module (.exe) but appearently it
> doesn't when called from MS-Access:
How does it call it?
> the dialog popups, but halfway it stops
> receiving messages and in rare cases it does handle all send messages, but
> then it won't disapear from the desktop when the dll has finished.
>
> I gues the problem is MS-Access, maybe it's STA (single threaded)
I would guess it uses worker threads internally, but to do just that - work,
not create a UI.
Access VBA adheres to the COM object model, which states that any COM object
must only have its members accessed from the thread that created it.
> while my
> DLL is multi-threaded, so I would try another solution : sending the
> progress
> messages directly to the calling Access module. But then I come into my
> problem.
> How can I let MS-Access react at my own 'custom' messages, while the VBA
> module is still 'waiting' for my DLL to finish.
If your VBA code can create a form that you know the hWnd of, just pick an
event handler that isn't being used for the form's operation, then just
figure out the corresponding message that triggers that event, and post it
to the form.
If that doesn't suit your needs, create a dialog box in the function of the
DLL that is exported to VBA (not a thread function) - then tell the worker
thread the hWnd of this window to post your messages to, which the DlgProc
can intercept for the progress bar.
> I already tried to develop a COM object, but while this asks a lot of
> development it didn't resolve my problem!
COM requires strict laws regarding threading like it does on many things, in
order to be language-neutral, notably the one I mentioned above. But it
doesn't sound like you need a COM object here... a regular DLL can export to
VBA, create a dialog box, and create a worker thread - it's the order you do
them in that's important... i.e. the just create the dialog from the thread
that the VBA module called, and then just tell the worker thread its hWnd.
>
> If anyone would know a good suggestion/answer for me, I would appreciate
> it
> very much. Currently I run the exported DLL-function in VBA without
> popping
> up the progress dialog (Access is blocking anyway while my DLL is in
> progress, and that can take up a very long time, while I've got a lot of
> data
Perhaps another solution you could consider is not to use VBA at all, but to
write a self-contained application that connects to the Access database via
ADO.
This could then be expanded both in terms of additional
data-churning/viewing functionality, aswell as easily ported to SQL server
should you ever need to do that.
> in the DB), so Access won't react strange anymore.
> I also tried to 'talk' directly to MS-Access by linking to the Access-COM
> module in VC++
Take a step back and try a different tack, perhaps... this sounds messy!
so I could try to get an interface to the Access.Application
> object and so modify the statusbar, but as soon I do MS-Access popups with
> a
> Kernel failure !?
>
> So I come with my question, would it be possible to run my DLL-procedure
> in
> a seperate thread, so Access can go into idle-status, so it could react to
> custom messages I send from the DLL, so Access can write to the statusbar
> itself...?
> But is it possible at all, to launch a seperate thread in MS-Access?
No! Now I know for a fact that that's *definitely* impossible...
LhK-Soft
I try the suggestion you mentioned :
"Bonj" <benjtaylor at hotpop d0t com> wrote in message
news:eT5oqKW...@TK2MSFTNGP12.phx.gbl...
<snip>
> > This works fine when calling from a MFC-module (.exe) but appearently it
> > doesn't when called from MS-Access:
>
> How does it call it?
Access calls a function from the DLL, the DLL creates a worker-thread. The
worker-thread creates a dialogbox and returns this HWND to the calling
dll-function.
Then that function creates a class in which the actual job is done and uses
the HWND of the dlg in the worker-thread to send its status-messages to.
So actually, access isn't sending any messages to my dll; the exported
function in the dll is doing it.
> If that doesn't suit your needs, create a dialog box in the function of
the
> DLL that is exported to VBA (not a thread function) - then tell the worker
> thread the hWnd of this window to post your messages to, which the DlgProc
> can intercept for the progress bar.
I think this would solve most of my problem.
By turning things around : create the modal dialog in the function which is
called by access, and do the job in a worker-thread.
That must work though while when you call the api-function
"DialogBoxIndirectParam", it only returns when the system couldn't create
the dialog (but I've tested it all, and it does) or when one triggerd the OK
or CANCEL buttons (which I do programmaticly by responding to a custom
message). While the api-function is running, the dialog-proc does its job
'correctly'.
So Access still will be "blocking" but hopefully I get my job done.
> Perhaps another solution you could consider is not to use VBA at all, but
to
> write a self-contained application that connects to the Access database
via
> ADO.
> This could then be expanded both in terms of additional
> data-churning/viewing functionality, aswell as easily ported to SQL server
> should you ever need to do that.
I was thinking about that, but the problem is that the only way of running
external modules in Access' VBA is via the function "Shell", which runs
asynchronous. So it launches any external application but then returns
immediately to Access.
The idea is that one should wait, before closing Access or doing other jobs
inside Access.
The purpose of mine DLL works together with a custom export I do in VBA (VBA
'prepares' the data in a custom file layout, then my DLL will 'pack' these
files to 1 single binary so it can be used by an installer I wrote for this
data). It's not the intension to start this exporter twice (accidently), I
don't know what would happen but it won't be great.
So I'll try the first suggestion you gave.
Thank you very much for your help!
LhK-Soft
Tried the suggestion (follows) :
> "Bonj" <benjtaylor at hotpop d0t com> wrote in message
> news:eT5oqKW...@TK2MSFTNGP12.phx.gbl...
> > If that doesn't suit your needs, create a dialog box in the function of
> the
> > DLL that is exported to VBA (not a thread function) - then tell the
worker
> > thread the hWnd of this window to post your messages to, which the
DlgProc
> > can intercept for the progress bar.
But access still wouldn't accept it.
Then I thought, what if I wouldn't use a thread at all?
While the actual job is done in a class and the progress-dialog is done in
another class (not MFC, so safe for VB(A)), what would happen if I link both
classes together into 1 class? Well, I finally like the results.
Now, no thread will be started in the DLL and access runs my task while
having the progress-status in a seperate modeless dialog (comming from the
dll).
The C++ source looks like following:
extern "C" DWORD WINAPI WritePacket(LPCTSTR lpszPacket, LPCTSTR lpszSrcPath,
HWND hWndAccessApp)
{
AFX_MANAGE_STATE(AfxGetStaticModuleState());
CPacketWriter* writer=new CPacketWriter(AfxGetInstanceHandle(),
hWndAccessApp);
// create Modeless Dialog
if (writer->DoModeless() == NULL)
{ // failed to create the dialog ??
delete writer;
return 0;
}
DWORD ret = writer->WritePacket( lpszPacket, lpszSrcPath );
writer->SendProgressMsg(prgEND, 0);
delete writer;
return ret;
Bonj
>
> But access still wouldn't accept it.
How are you defining "wouldn't accept it" ? What is the error message, if
any, and at what point does it occur?
I see your point about not wanting Access to "block". FWIW, I have seen a
spurious error message before in Excel that says "Microsoft Excel is waiting
for another application to complete an OLE action" when it is executing a
long COM call - the call is working fine, but it pops up a message box
saying that every 10 seconds until it completes. I don't know whether that's
what you're getting, but you could possibly address that by having a
separate process, and use ShellAndWait (see below)
> Then I thought, what if I wouldn't use a thread at all?
If you don't use a thread at all, the DlgProc won't be able to process the
progress bar update messages as it will be on the same thread that's doing
the calculations. You just need to make sure the CreateDialog (or
equivalent) occurs in the same thread that Access's VBA is in - after that,
it can create worker threads. The worker thread could send a custom
'finished' message to the dialog's hWnd, which it could intercept and call
EndDialog, thus causing the call to CreateDialog to return (and back into
the VBA).
> I was thinking about that, but the problem is that the only way of running
> external modules in Access' VBA is via the function "Shell", which runs
> asynchronous. So it launches any external application but then returns
> immediately to Access.
> The idea is that one should wait, before closing Access or doing other
> jobs
> inside Access.
Do a search for "ShellAndWait" on google - it is a widely documented method
of calling CreateProcess, and then doing a loop, calling WaitForSingleObject
on the process's handle with a timeout of, say, 50ms (which you pass to it),
this means it "spins its wheels" in a loop, and it's executing a method
every 50ms, and it only breaks out of the loop when the process terminates.
It could possibly solve the bug I remembered above, if that was the case
with yours.
LhK-Soft
"Bonj" <benjtaylor at hotpop d0t com> wrote in message
news:%23cACkoA...@TK2MSFTNGP11.phx.gbl...
>
> >
> > But access still wouldn't accept it.
>
> How are you defining "wouldn't accept it" ? What is the error message, if
> any, and at what point does it occur?
"wouldn't accept it" : The dialog popup's, but can't get removed from
access' desktop when it's finished. Properly Access97 doesn't allow me to
start other threads when called external functions from a DLL when using UI.
> I see your point about not wanting Access to "block". FWIW, I have seen a
> spurious error message before in Excel that says "Microsoft Excel is
waiting
> for another application to complete an OLE action" when it is executing a
> long COM call - the call is working fine, but it pops up a message box
> saying that every 10 seconds until it completes. I don't know whether
that's
> what you're getting, but you could possibly address that by having a
> separate process, and use ShellAndWait (see below)
Looks handy that function "ShellAndWait", after I've searched the Net I
would use it whenever I've got a more sophisticated task to do.. but for the
moment I'm happy with the result.
With "blocking" I mend that Access starts the call to the external function,
but then waits until the external function returns. While it waits, the
whole desktop of Access "freezes" (even the assistent-icon), so it doesn't
do any refreshing of its desktop/windows.
I gues this wouldn't be the case when I would call "DoEvents" at a regulare
base (when I used "ShellAndWait").
> If you don't use a thread at all, the DlgProc won't be able to process the
> progress bar update messages as it will be on the same thread that's doing
> the calculations. You just need to make sure the CreateDialog (or
> equivalent) occurs in the same thread that Access's VBA is in - after
that,
> it can create worker threads. The worker thread could send a custom
> 'finished' message to the dialog's hWnd, which it could intercept and call
> EndDialog, thus causing the call to CreateDialog to return (and back into
> the VBA).
While when running a dialog as Modal, the call to "DialogBox" would not
return until the DlgProc has called "EndDialog" (usually in respons of a
message, like OnOK and OnCancel), but when running a dialog as Modeless, the
call to "CreateDialog" will return immediately (if the creation of the
dialog succeeded and the WM_INITDIALOG call has been made, it returns the
dialog's HWND) allowing me to run other things in the same thread besides
the existance of the dialog (I gues, in case of a modeless dialog, the C/C++
API launches its own thread) - but I've figured out I shouldn't call a
SendMessage/PostMessage to the dialog itself too much (instead I must use a
regular function when I want have things to be changed in the dialog).
To have "full" access to all members of the dialog-class (not a child of
MFC's CDialog, while CDialog calls some invalid methods which doesn't allow
me to create/run a dialog from inside a regular DLL), I derived the
worker-class (which does the actual job) also from the dialog-class which
does all things to display the dialog and dlgproc :
[code/start]
class ClkBaseDlg
{ // base class for all my "custom" dialogs when called from a DLL
...
public:
virtual BOOL DoProcessing(...); // processes any message from the DlgProc
HWND DoModeless(void);
};
class CProgressDlg : public ClkBaseDlg
{ // "independant" progress-dialog
...
};
class CExporter : public CRuntimer, public CProgressDlg
{ // class encapsulating all required stuff, when called from VBA
...
};
// in file lkBaseDlg.cpp
static BOOL CALLBACK myDlgProc(HWND hWnd, ...)
{
...
ClkBaseDlg* theDlg = GetMyDlg(hWnd);
if (theDlg)
return theDlg->DoProcessing(...);
//else
return FALSE; // indicating message has not been processed
}
ClkBaseDlg::DoModeless()
{
...
HWND ret = CreateDialogIndirectParam(m_hOwnerModule,
(LPCDLGTEMPLATE)m_DlgTemplate,
m_hWndParent, (DLGPROC)myDlgProc, (LPARAM)this);
...
return ret;
}
[code/end]
In previous code-sample, CRuntimer is a base class with basic
functions/variables which are the same for exporting/importing
(packing/unpacking) data.
CExporter would be the class specific for exporting the prepared data (by
VBA) to a single binary file which then can be unpacked by the installer I
created in another application.
This seems to be working, when it's called from the DLL from a VBA-module in
Access. While Access still is "blocking" anything at its own desktop, the
dialog in the DLL gets updated (so it's "alive").
> Do a search for "ShellAndWait" on google - it is a widely documented
method
So I did.. tx.
cu.
--
Laurens Koehoorn
Bonj
> "wouldn't accept it" : The dialog popup's, but can't get removed from
> access' desktop when it's finished. Properly Access97 doesn't allow me to
> start other threads when called external functions from a DLL when using
> UI.
You're not supposed to create extra threads from Access97 - do this from,
say, one of the handlers for a button on the dialog (WM_COMMAND), or when
the dialog starts, WM_INITDIALOG - i.e. *on the C++ side* - in the DLL. The
DLL starts the modal dialog box, the DLL *also* starts a thread - that
doesn't mean the dialog is running in the worker thread - that will only be
the case if the worker thread's thread procedure calls CreateDialog. If the
exported function calls CreateDialog, and the dialog's DlgProc starts the
worker thread, then Access should block, but the dialog itself should be
responsive.
> Looks handy that function "ShellAndWait", after I've searched the Net I
> would use it whenever I've got a more sophisticated task to do.. but for
> the
> moment I'm happy with the result.
What's your final configuration, like I said or slightly different?
> With "blocking" I mend that Access starts the call to the external
> function,
> but then waits until the external function returns. While it waits, the
> whole desktop of Access "freezes" (even the assistent-icon), so it doesn't
> do any refreshing of its desktop/windows.
That shouldn't matter. It shouldn't mean the dialog box freezes aswell. If
it does, try passing NULL to the hWndParent parameter - instead of Access's
window.
If some component is doing an operation on the database, you shouldn't
*want* the users to have access to the database while it's running, as they
should know that the database is being updated. It won't look wrong. If
you've explicitly taken the decision that user-interaction with the Access
desktop while the function is running is something that is needed, then you
*really* need to write it in ADO.
> I gues this wouldn't be the case when I would call "DoEvents" at a
> regulare
> base (when I used "ShellAndWait").
Ermm.... I don't know..
Like I say, if you *insist* on user-interaction with the database from an
Access desktop process, you don't really want that process also running a
time-consuming calculation.
>
> While when running a dialog as Modal, the call to "DialogBox" would not
> return until the DlgProc has called "EndDialog" (usually in respons of a
> message, like OnOK and OnCancel),
> but when running a dialog as Modeless, the
Are you saying that you are having a *modeless* dialog box? If so, then how
does Access know when it's finished? What if the Access function that
started the dialog has completed and no more code is running in Access, and
the user shuts Access down before the code has finished?
What problems do you have with a modal dialog box that a modeless one
solves?
I'm just trying to get you to understand that having Access run a dialog
that's supposed to look like it's in a completely separate process to
Access, but isn't because you can't see how else to communicate the results
back, is a kludge. Either Access blocks, or use a separate process. Anything
else (I can't quite figure out what you've finally gone for, but..) I'd be
very interested to hear how you implement the calls and the return of
information, and why you reckon this particular way of doing it isn't a
hack - not least because I'd want to know how to do it myself, since I
obviously didn't know it to advise you earlier!
> call to "CreateDialog" will return immediately (if the creation of the
> dialog succeeded and the WM_INITDIALOG call has been made, it returns the
> dialog's HWND) allowing me to run other things in the same thread besides
> the existance of the dialog (I gues, in case of a modeless dialog, the
> C/C++
> API launches its own thread) -
> but I've figured out I shouldn't call a
> SendMessage/PostMessage to the dialog itself too much (instead I must use
> a
> regular function when I want have things to be changed in the dialog).
> To have "full" access to all members of the dialog-class (not a child of
I don't know what you mean by "full" access....
> MFC's CDialog, while CDialog calls some invalid methods which doesn't
> allow
Well, that's probably the root of all your problems. MFC is ghastly,
bloated, cumbersome, dependent, obscure and probably buggy. I personally
*hate* it.
If you want RAD, there's VB6 or C#, if you want COM, there's ATL (if you
must), if you want controllability, there's straight C/C++, so *why* there
is a need for the abomination that is MFC god only knows.