Well, I have some Access apps that do some really long running DLOOKUP's and Queries against native Access tables with millions of rows and not only do I see the "Not Responding" message, but it also throws the entire box into limbo where I can't even see the cursor echo outside of the sleeping window. Since we're not sure about what's really going on inside the black box, perhaps someone like
@Albert D. Kallal, or others, might be able to shed some light on this topic?
Well, first of all, if you do a lot of stuff, then the Access UI will lock up. There ARE some threads, and don't confuse that of "threading" models vs that of multiple processor tasks (often the term task and thread are mumbo jumbo up here).
>So then in Access, how do you fork/exec a new detached process for executing a long running query against native backend tables?
Well, first keep in mind that the JET now called ACE data engine runs in-process. That means it runs on/with the current process thread. You can think of calling ACE data engine code JUST LIKE calling a sub routine. You have to wait until that sub call returns back to you!
Remember, we often ran say windows XP with only ONE processor, and multiple programs seemed to be running, right?
And Access does actually have somewhat of a threaded model (not a multi processor model).
So, you can see this when a form loads, and you have say some controls that are the result of say dlookups(), or some VBA functions. The form loads, displays, and THEN the display starts updating the controls AFTER the data is displayed. This is the UI thread that access has.
These "threads" are in fact que's of events built up over time.
Hence:
me.calc - trigger the calculation thread - but not the UI (yet)
me.paint - trigger the UI thread to display current control states/status
DoEvents - trigger all of the above pending thread que's
And then we have ones for data, such as me.Refresh.
So, keep in mind, that EVEN if you ran access on a different OS - such as Linux and WINE? It's going to behave the same - it's not due to the OS, it due to that Access is not firing off, and triggering separate processes.
So, when you call a simple VBA sub? You have to wait for the sub to finish, and return, and your VBA can then continue.
Same goes for a update query - it's REALLY IS a sub call to some code (the JET/ACE) data engine, and once again, your code has to wait until that update query is done, and that "sub" returns back to you!
I mean, have you used a programming language and framework that supports threading?
So, could we introduce code that fires off another process (and thread)?
Sure, we can. The only problem is the archeological history of Access. VBA does not have async programming features built in - and it was written LONG BEFORE multi-core CPU's appeared.
So, can you say call a long running stored procedure on SQL server, and not have to wait in VBA?
Yes, you sure can! -- and of course that SQL server data engine is it's own whole server and CPU -- right?
However, can we do the same in Access?
Yes, once again, we could - but we would have to add some extra moving parts here.
Simple way:
Well, create a new front end, link the tables, and then use shell command from your current instance of Access. (shell() will start a whole new separate process). On the other hand, you could even just launch the extra front end, and run the query against linked tables - it would be a WHOLE new copy of Access, and thus you get a WHOLE NEW windows task.
Another way? Well, call some .net code from Access. That .net code can then start a new task/thread, run the query, but the sub call you make would return instantly. Of course then you not have any freeze up's, and you also not know when it is done, or if it failed (hey, that's just like Linux now - no nags, but no knowledge of what you just wanted to do finished, or froze up!! - As you can see, such a case now is less then ideal, right?
So, if you want that kind of cake (no freeze ups), then you ALSO be faced with having to test/check if the process in question finished, or it has hung up. But then again, those Linux folks know how to use the command line to test/check for those running tasks, and kill them, or check if they finished, right? And now YOU THE USER is faced with managing those stray tasks on your computer - but then again, that's what Linux folks use and like, right? (so, you use the jobs shell() in Linux to query and see what's going on).
Of course, you NEVER want to use dlookups() in a query - each row for each dlookup() will tirgger and require and create a WHOLE NEW query. And in 9 out of 10 times, you can replace the dlookup with a left join. You can also use a sub-query, but then we back to performance issues.
So, once again, this is not really a windows thing at all. It "mostly" comes down to that the data engine is NOT a seperate windows process - but then again, who always wants to setup a whole new processing system, say like SQL server to just edit some data in a table?
Now, in some cases, this concept of moving the data processing OUT of Access and pushing such processing such as SQL updates etc. to some other process? Sure, and that's called SQL server - and it's a option we have for Access.
Now, I could consider posting a small .net routine that you call from Access, and it would run such update queries in another process. However, keep in mind this would not help for returning data in most cases, but would I suppose be of use for update queries that take a long time.
But, then again, if you had/have say 5 update queries to run, each taking a long time? Well, if you adopt asynchronous programming, then does it matter if the queries you are running run out of order? (you see, all of sudden, introduction of asynchronous operations all of sudden becomes FAR MORE complex, since if you send 5 queries to that system, and don't want to wait? Well, now you can't necessary be sure which query you sent will even finish, or run first!!! And if you THEN decide that you need/want to wait for each query, then you kind of back to square one, and now are waiting for the update query to finish, right?
R
Albert
Did you format it as an NTFS, or ExFAT?
