Hi,

I want to run a query in the background. Not "hidden", but actual system-perspective background. I really want to do this, and am asking even though I know it's not something that Access is designed to do.

If two separate instances of Access runs on two different processes, I think I might have a shot. Here's my hairbrained idea:

1) from my main app, open a different instance of Access
2) set the process priority on this instance to low (?)
3) the low-pri instance then runs the query as an automation task with the main app being the target

I don't know if this is the right path to take or not... does anyone have insight? My main questions at the moment are whether I can separate Access into two process and manage the secondary one through the API. If this can't be done, is there a possibility of aquiring the thread handle that runs a specific task in the Access process for manipulation?

Dangerous, yes I know. I'm just looking for proof of concent at the moment. This need not be limited to VBA/Win32 API programming, though I would prefer to house it as so if possible.

Any thoughts highly appreciated.
Thanks,

I've been able to determine that these do in fact run on two separate processes. Now lots of API documentation and testing... still any thoughts moving forward are appreciated.

Can I ask what you hope to do with it? Maybe there's a better solution for this - would this be easier to achieve by using, for instance, SQL Server Express Edition and invoking a procedure asynchronously?

My app runs a messaging system that distributes messages targeted to certain users when various events take place. For instance, when a part qty on hand changes, there's a check to see if the adjusted quantity falls below a minimum number on that part, and sends a message out if so. Order entries or revisions send a message, job status changes, employee anniversaries, etc etc.

Most messages are event driven - I can capture current realtime data as the even is raised and handle the message accordingly, but other's don't have that luxury and need to be queried either "manually" or on some sort of schedule. Currently my schedule for these is on user login, but I would like to change this to run, say, every half our or so. This timed query I would prefer to run in the background.

It's not that big of a deal really, the login run isn't atrocious, and if it were I could certainly get away with windows sched task to run after hours - the non-automated data isn't so critical that not getting it within a half hour, or even two days, is any sort of problem.

Mostly it's just head scratching, wondering if I can.

Well, here's few possible solutions:

1) Instead of using specific time interval, base the query on a certain event. Let's select form's Open event as an example. Have that event call the procedure to check if there's any change and make the necessary updates. That isn't truly asynchronous but the implementation should be much simpler and should work well enough especially if you can write a query that immediately return a value indicating if there's updates pending before doing actual updates (which I assume is expensive)

2) I'd probably want to write a small EXE to do this. Furthermore, the EXE can create its own instance of Jet engine and thus incur less overhead than if you instantiated another Access application. It'd be used like DLL but being an EXE, it can be called out of process.

3) Why not VBScript? No custom EXE would be needed and it should be fairly rich to boot.

1) this is the way I do it now - it works fine, but it's not a true background task

2) An EXE is probably the ideal way to go, but if I get this working I'd like to post a VBA example to one of our code repositories. Occasionally the question pops up in the forums and so far the answer is "no, can't really do that with Access", so I'd like to change that if possible. Concerning Access/Jet in the exe, I'm actually hoping to be able to give the option to run Functions, SQL Strings or QueryDefs from the host app. This would demonstrate the possibilities, though certainly I agree that an exe interacting with jet alone to process some SQL would be far more lightweight. (can you run a function based on a pointer to it's location from a separate process without having to instantiate another instance of Access?)

3) This would certainly be easier, but some people have IT reqs that don't allow scripts to run, so as a general rule I try avoid them for any "public" solutions.

So far I've been able to programmatically determine the process handle of a given instance of Access, determine if I have the required rights to read and set the PriorityClass information, and have been able to determine the process's current Priority Class. Next will be to test the setting of the Priority and its resulting behavior.

You need to take in account that VBA is not really made to be multi-threaded or work inter-process communications. Thus, there's no built-in mechanism for handling thread safety and transversing processes. I'm sure there's APIs that help you do this but it remains you'd be interacting with a piece of code that isn't totally thread-aware, making it very fragile than if you used an EXE of your own. I'm not saying it can't be done, just that it takes much more caution. A while ago, I saw someone talking about enabling asynchronous calling by providing two different DLLs; one to submit requests, other to receive events. (Why two DLLs was needed, I can't remember). The idea would be that you'd call the request, and register an event handler to be notified. That was for VB6, though.



Yes, passing a function pointer is possible but only to API. I do this with EnumChildWindows API; here's a sample. (Sorry, this is for VBA7, but you can make it compatible with older Access by removing the "PtrSafe" and replacing all "LongPtr" with "Long"


Here, I passed in the pointer to function EnumChildProc which is then called back by the API for each windows enumerated.

The documentation says you can't use AddressOf operator within VBA code and so far, I've never figured if there's a workaround to pass a function pointer within VBA.

Like this you mean?



and the caller, in this case from clsGetOpenFile:


apihfFalseHook is public function in a standard module, not related to the caller or GetAddrOf other than that it happens to be the function the return will reference.


Concerning everything else: yep, point taken. As mentioned in my original post: dangerous. Many thanks for the input.

GetAddrOf() can be used to retrieve the pointer address itself but what I was saying is that there's no way to invoke a function via a pointer within VBA. Maybe there's an API that calls an arbitrary function via a pointer, but I doubt it.

Also, I believe your example is redundant - you would have same result with:


I'd expect to use GetAddrOf to be able to hold the pointer address for future use without having to relooking it up.

BTW, if apihfFalseHook is a function that does nothing, then you can do better by:


API should understand that a null pointer basically means you don't want to hook into anything and won't try to call an function that does nothing. But if it does something like track API usages, then never mind.

Ah ok... not sure how to do that in any other language either, actually. Not that I should know.


I seem to recall getting some syntax error when I was initially setting that up, which led me to find that function. Maybe I was just having an off night...



When going through the MSDN documentation it said that under a certain scenario I had to use callback that returned False. I didn't try setting the hook itself, just let the callback do nothing and return 0. I'll give it a shot next time I'm playing around in there.

Hmm. Interesting. I don't think I've ever used the callback in the lpfnHook in past and always passed &H0. I'll have another look at the documentation... ah:

Reference

Thus, if you don't set the flags OFN_ENABLEHOOK, then you can safely pass in null pointer instead of a bogus function that returns zero.

That's what it was. I enabled the option to use the 3.1x style window (don't ask why, please) by leaving the OFN_EXPLORER flag out, and was of the impression that I needed to supply a pointer to the hook which would return False and thus send the message on the the dialog. I set the OFN_ENABLEHOOK flag only because in this situation where I needed the false return from the hook.

That was my reasoning anyway. Do you think I could have instead omitted OFN_EXPLORER, OFN_ENABLEHOOK and then set the hook pointer to &H0? I didn't try it (because I didn't know it may have been an option), but looking at it now: is this a shortcut of sorts? A way to trick the API, or a "less explicit" way to do it - letting the API itself determine what we wanted? Generally I try to avoid situations where the computer may try to determine what I was thinking (especially with Access) - is this such a case? Just curious.

Thanks for the input.

No, just don't mind me - I'm not supposed to backseat driving.

Since I've never tried to use a hook procedure (just didn't have the need in past) and since you did indeed enable hooking (I'm taking you at the word that EXPLORER flag requires ENABLEHOOK flag as well), this is actually apropos, so my comments wouldn't be relevant in your specific scenario.

As for passing in null pointer being a trick of sorts... no. I think it's more of a convention than anything. So, if I didn't set OFN_ENABLEHOOK flag, it wouldn't matter what value I put in the lpfnHook parameter because API will ignore it no matter what, but I set it to &H0 as a matter of documentation - when I come back to this code or read someone else's code, and see such thing, I interpret as "we don't use this parameter".

I was incorrect to say in this specific situation that the API would know what null pointer means - that is also a implementation-dependent thing. *Some* APIs understand null pointer as having special meaning, but this one was not one of such cases because this one API uses flags instead to determine how it should process the structure and will just ignore the inapplicable members. So, ultimately, the only correct usage is what you get by following the documentation to the letter for every individual API. There's no universal "this is how you do it" for every API, which is why we can see lot of variances; some API expect null-terminated strings, but other wants you to tell it what the size of the string you are giving it, while others uses flags, and some don't use flags but a lot of optional parameters. When in doubt, consult the documentation. I really wish I could say there's a one magic trick that fits all but that is not the case. Should be kind of expected since API is pretty low level, I guess.

WRT running the more intensive timer-driven queries, I figure I can move these to a separate db on the server. Open/close it during normal business hours and have it query the backend every half hour or so. No slowdown of the FE, no process manipulation, and concurrency will take about as much consideration as any other multi-user environment design. I don't know why I didn't think of this when I originally cobbed the message system together.

As far as process manipulation, I'm considering using it for something else - I have one app that runs once evrey few days and ties up resources for a little while. This isn't related to any other apps (a backup program for select server folders) - I can't think of any reason that setting a low process priority in such a case would bother anything?

Cheers,