How to force Acces into synchronous queries

[The_Doc_Man]

Supplement: I have searched the web to verify that my shot from the hip was on target.

Looking for "Multi-threaded MS Access" I find the general consensus (from many forums, not just Access World Forums) that MS Access is single-threaded. I was checking to see if anything new had been added to Access behavior, but it has not.

The asynchronous behavior you describe simply CANNOT occur because the basic product can't be made to work that way. Each query will finish before the next one starts. The .TransferText method, which also runs under Access, will finish before the next operation starts. Control always transfers synchronously. That is why events cannot interrupt other events.

Therefore, if you have a problem with queries behaving as you suggest they did, then the problem must be in the queries, not in Access. I know you don't want to hear that but after a bit more research, I don't see any other way around it. Your queries must not be what you think they are.

 

[Galaxiom]

The asynchronous behavior you describe simply CANNOT occur because the basic product can't be made to work that way.

Access itself uses a single thread and clearly will not start another command until the previous one returns. However the work is actually being done by the ACE database engine so I would be inclined to look there to explain the anomalous behaviour.

ACE uses multithreading to speed up processing with reading, writing and cache maintenance on separate threads. I wonder if the write is being delayed long enough to prevent the write cache being cleared before the subsequent read on the next command.

One solution would be to insert a delay in processing in Access using something like the Sleep function to wait a second or so. This function is not native to Access but there is plenty of information on line about setting it up.

The other more interesting alternative would be to have the engine synchronise reads and writes by refreshing its cache.

Try inserting this line after the original table creation.

 DbEngine.Idle dbRefreshCache

 

[The_Doc_Man]

Oh, DAMN! ACE is multi-threaded?

None of the forums and articles I researched had mentioned anything about that. They all pretty much categorically denied it. I don't recall Eurisko identifying the Access version, but ACE certainly only applies to newer versions of Office.

Apologies ahead of time for blowing this out of proportion if it turns out to be an error on Eurisko's part, but he was pretty emphatic about what he claimed he was seeing. If he is right, this is the tip of a big iceberg.

Eurisko, while I'm still not convinced of what is actually going on, I owe you a big apology if you are running a version of Access that uses a multi-threaded ACE engine.

To explain my concern, I must point out that my "day job" involves a multi-threaded O/S (OpenVMS, you can find it on Wikipedia). My primary systems run in IA64 boxes with 8 cores, read-ahead and write-behind processing, and advanced memory sharing and caching. I have studied the internals of time-slicing, multi-threading environments for > 30 years in my professional career.

It is well-known that to avoid destructive interference, you must put a flag, semaphore, or other locking method on any sharable resource; further, the holding "actor" must not prematurely release a shared resource lock and a waiting "actor" must not prematurely act without first gaining the lock.

If Eurisko has in fact found a case where ACE allows two threads to work on the same resource without stalling the later-arriving thread (because the former thread isn't done) then this would be SUCH a major flaw in terms of operational synchronization that NO part of a "pure Access" database can be trusted. It would suggest a flawed internal operations scheduler or lock manager for the ACE engine.

Normally, when a resource is in transit, it must remain unattainable until the transit is complete, whether reading, writing, or remapping. The queries that Eurisko presented were clearly a form of MakeTable query, but sound operational theory requires that table must not be made "public" (i.e. usable by subsequent queries) until it is, in fact, COMPLETELY made including all forms of write-back.

Further, Eurisko was clear about his environment. We don't have the fallback of saying that he was waiting for a heterogeneous back-end SQL operation to clear. His situation is purely homogeneous, so his copy of Access (ACE or JET, whichever one it is) absolutely MUST be able to see all of the relevant locks, which are, in a homogeneous case, all local to his system.

At this point it is IMPERATIVE in the analysis of the problem to know what version of Access Eurisko is running. Also, an opinion: If this is a real observation and not a bad-query problem, then it is something that will almost certainly have to become an Office KB-level patch that we won't be able to fix from our end.

 

[Galaxiom]

Oh, DAMN! ACE is multi-threaded?

Well my source is not impeccable (a Wikipedia article that cites no references) and only refers to multithreading being introduced to Jet 3.0. I presumed that ACE would not revert this development since it clearly would help performance.

However it is hard to see that this problem would not have arisen previously if it were an intrinsic programming error since Jet 3.0 was released over twenty years ago.

None the less, Eurisko seems clear about the queries being the same and the problem being consistently repeatable. I would be looking to there being something wrong with the Access installation on the machine.

Next test would be to try running it on another machine. If that also exhibits the issue then we aught to be very concerned. If not then I would suggest a Repair Install on Access.

I hope we hear back from Eurisko soon.

 

[The_Doc_Man]

So do I. I hate to blow it out of proportion, and I agree that this should have reared a VERY ugly head a long time ago.

It comes down to this: Whom do we trust? Access developers to have gotten this right, or Eurisko, to have correctly observed a real situation.

If Eurisko is right then this is terrible - near catastrophic - for us all. If he is wrong, it is just terrible for him. Perhaps he can read this and realize why I reacted as I did. I wish him no ill, but I sincerely hope his observation is flawed.

As to the ACE multi-thread articles, I was able to adjust my search and found a couple of references besides that one you found in Wikipedia, but still not enough details to be able to tell what is really done via the extra threads. I was even more flabbergasted that VBA is ALSO supposed to be multi-threaded. VB? Yes. VBA? The implications are staggering.

 

[NauticalGent]

I remember reading this and thinking this was a situation that I would never have to worry about.

Never say never, it happened to me today. Frustrating...

 

[GohDiamond]

Doggone it and I had my rant ready to go then I read the rest of the story.
I'll post it anyways for your enjoyment. (Postea de superiore sermone conscriptae)

I'm not quite sure just who you think you are, but if that's the kind of supercilious attitude you give people, perhaps you should move on to other ventures...

Well, I'm not sure who this is but I see The Doc Man with 9k+ Posts here and Eurisko with 9, I see over 400 Thanks for the HELP indications and Eurisko with 0.

Kind Sir, and I use the term lightly, you have come to one of the most helpful places on earth where MS Access is concerned and you address this sage council as though you were "the King", belittling and berating before taking time to know what your position is among us.

If you can extract your cranium from your posterior orifice for a moment and realize you've come into a hallowed hall of knowledge as a beggar you will be helped. However, if you can't see any wisdom in the post then, the door is not locked, just don't let it hit you in the backside on the way out..


Goh

 

[jdraw]

I note that the OP has 9 posts all back in early Mar 2016. There seems to be no additional info from him, and his bedside manner when dealing with experienced volunteers could certainly have been more appropriate, but is there any reference to an authoritative answer to the "issue"?

 

[Galaxiom]

... is there any reference to an authoritative answer to the "issue"?

The subject has been picked up again here.