How to force Acces into synchronous queries

[Eurisko]

When performing several SQL queries in succession (through VBA), the queries are pulling the wrong number of records. However, when exact same queries are run manually, they pull the correct number or records.

I have never encountered this before. Has anyone come across this, or know of a solution?

I've tried the following:

DoEvents
DoCmd.RunSQL "query1"
DoEvents
DoCmd.RunSQL "query2"
DoEvents
DoCmd.RunSQL "query3"

CurrentDB.Execute "query1"
CurrentDB.Execute "query2"
CurrentDB.Execute "query3"

DoCmd.RunSQL "query1"
DBEngine.Idle
DoCmd.RunSQL "query2"
DBEngine.Idle
DoCmd.RunSQL "query3"
DBEngine.Idle

All to same effect. Wrong output when running in automated script.

 

[MarkK]

What happens if you do . . .

CurrentDB.Execute "query1", dbFailOnError
CurrentDB.Execute "query2", dbFailOnError
CurrentDB.Execute "query3", dbFailOnError

 

[Eurisko]

Thanks Mark, but it made no difference. This is quite a conundrum.

 

[Minty]

Do the queries have any parameters, if so where are you getting them from?

 

[Eurisko]

Yes, the queries have parameters, and they're taken from a table created from a previous query in the script. Which I believe is the source of the problem, since Access is running the queries asynchronous, these subsequent queries are likely trying to pull from a table that hasn't been completed yet.

Which is why I'm ascertaining how to run these queries in order, and synchronously.

 

[The_Doc_Man]

We should not make assumptions here. Is this a case where you have a split FE/BE database and the BE is NOT in Access format? I.e. SQL Server or something like that?

If so, the standard method for ensuring independent atomicity is to use a BEGIN TRANSACTION followed by a COMMIT for each query. Since I don't use that very often (because I have only homogenous databases - all Access), I'll defer to the others here on exactly how you set that up for the SQL Server case.

 

[jdraw]

Eurisko, further to the DocMan's comment, could you tell us about your configuration
Front end, backend, any other software; parameters; even a brief description in plain English would be helpful.

 

[Eurisko]

No, this is not a split database. It's a standard Access database with the tables "in house".

In plain English, through VBA coding, I create a table (from an imported text file) where one of the fields contains a parameter (A = Active, S = Suspended, C = Cancelled, etc). I then create individual tables based on that parameter.

So I end up with an active table, suspended table, cancelled table, etc.

Now, when run these queries in VBA, the resulting tables always end up with less records than expected. However, if I manually run these queries, one at a time, the correct number of records are selected.

 

[jdraw]

?? If the data in the tables is dependent only on the A/S/C, why not one table with a Status field?? I'm sure there is a reason, but please clarify.

Can you post a dumbed down version of the database?
dumbed down===>just enough data to show the issue; use false names people/city etc

Post in Zip format.

 

[Eurisko]

Here is a dumbed down version of the code:

DoCmd.TransferText TransferType:=acImportDelim, SpecificationName:="TorS", TableName:="TorS", FileName:="F:\TorS.txt", HasFieldNames:=True
CurrentDb.Execute "SELECT NPA, CTN, STATUS, SWITCH INTO SW FROM TorS WHERE (((STATUS) Like '*POTS*'));"
CurrentDb.Execute "SELECT SW.NPA, SW.CTN, SW.STATUS, SW.SWITCH INTO SW_A FROM SW WHERE (((SW.SUB_STATUS)='A'));", dbFailOnError 
CurrentDb.Execute "SELECT SW.NPA, SW.CTN, SW.STATUS, SW.SWITCH INTO SW_S FROM SW WHERE (((SW.SUB_STATUS)='S'));", dbFailOnError 
[FONT=&quot]CurrentDb.Execute "SELECT SW.NPA, SW.CTN, SW.STATUS, SW.SWITCH INTO SW_C FROM SW WHERE (((SW.SUB_STATUS)='C'));", dbFailOnError [/FONT]

[FONT=&quot]

Yes, there is a reason for the separate tables as further manipulation needs to be do[FONT=&quot]n[FONT=&quot]e [/FONT][/FONT]to each and exported as separate entities.

[/FONT]

 

[The_Doc_Man]

No, this is not a split database. It's a standard Access database with the tables "in house".

If this is so, then ALL repeat ALL queries are synchronous. "Pure" Access doesn't have the ability to run multi-threaded code locally. It's code design was based on a uniprocessor environment where EVERYTHING is linear. That is why the DoEvents action is necessary. Otherwise, asynch execution would make DoEvents moot.

You have 5 operations in your example. The first one SHOULD be synchronous because Access is managing the implied file system operation. However, starting with the second operation, you are very close to guaranteed that the query starting with "SELECT NPA, CTN, etc." will finish before the next query starts (the SW_A one). Nothing else can happen to TorS due to query #2 in your list once query #3 starts.

I have not heard of a multi-threaded version of Access and you have stated that this case is NOT a remote back-end like SQL Server, which COULD go multi-threaded on you. In order to be able to diagnose this, we will need to know Access version, Windows version, and maybe something about the platform. Also need to know if someone has dinked around in the registry AND in the image header, since normally Access will run in an image for which multi-threaded behavior is not allowed. Stated another way, I have NEVER seen Access occupy more than one CPU at a time on any system because normally it is not given permission to do so. Nor was it written with that permission, though you COULD theoretically write VBA code to trigger multi-thread operation via Win32 calls or trigger a Shell operation.

as further manipulation needs to be done to each and exported as separate entities.

I will respectfully agree that it is your database for which you must of course make the final decision on structure; but I will respectfully disagree that you have named a valid reason for having three split tables when everything else you needed to do could be done with an appropriate WHERE clause to isolate actions for the three cases.

 

[Eurisko]

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.

If the queries in the script are running sequentially as you CLAIM, I repeat CLAIM, then it should be a simple matter for you to explain why line 3 in the code returns 580 records in the script, while returning 8530 records (the correct amount) when run manually. Or why line 4 returns no records, and 5 records (the correct amount) respectively. Please regale us with your brilliance.

Secondly, I am under no obligation to justify my reasons to you regarding my methods, which have nothing to do with the question ma hand.

Now sir, if you cannot provide a courteous response with a valid solution to offer. Do not write me again.

 

[MarkK]

supercilious attitude

Supercilious? Serioulsy? Esoteric AT WORST! I think you've badly mis-read the post. And he gives you the solution!!!

Sheesh.

 

[Eurisko]

And he gives you the solution!!!

Yes, seriously. And no solution was given.

 

[Galaxiom]

However, when exact same queries are run manually, they pull the correct number or records.
DoCmd.RunSQL "query1"
etc

You begin by telling us that you run the "exact same queries" manually and via code and get different results.

It is clear from the code that you are executing saved queries eg "query1".

Here is a dumbed down version of the code:

CurrentDb.Execute "SELECT NPA, CTN, STATUS, SWITCH INTO SW FROM TorS WHERE (((STATUS) Like '*POTS*'));"
etc

Now you are showing that you are not executing those saved queries at all but SQL commands.

What results do you get if you run "the exact same queries" as you claimed in your question? I expect your manually run queries and the SQL commands are not identical.

Your "dumbed down version" probably omits the crux of the problem.

 

[Eurisko]

I wrote "query1, query2..." in the first comment as example of the sequence of queries I was running. They are not saved queries. As I wrote in my initial comment, I attempted both DoCmd.RunSQL and CurrentDB.Execute, with the same results.

I run the VBA script and end up with one set of results. Once that is finished, I will go into the VBA Editor; literally copy and paste lines from that script and run them one by one in the immediate window again, which produces different (and correct) results. The query in the script and the query I run manually are identical.

 

[Galaxiom]

You seem to be thinking that the first insert is not finished when the others are run. Best confirm that this is the exact and sole location of the problem. What happens if you run the first one manually then automate the others?

 

[Eurisko]

Yes. If the initial table is pre-existing. The resultant automated queries work correctly.

 

[NauticalGent]

Eurisko,

Please excuse my weighing in here. Being fairly new to this and other forums, the "old guard" CAN come off as condescending at times. I am been put on the defensive a couple of times myself but I realize that there is an expectation of skill sets and accepted practice or habits on their part.

Good, sound table structure and efficient query design is high on that list - especially when you ask questions regarding SQL and VBA - the Access Commandos who engage you naturally assume you have mastered the basics and when it APPEARS that something is contrary to that, they are compelled to ask, if anything to eliminate that possibility.

I agree that some of the "bed-side manner" is lacking, but I didn't come here to be coddled - I can here for help - and have received it in abundance.

I would never suggest you let someone insult you, but I do not see any mal-intent on anyone's part.

BTW, I hope this issue does get resolved as this seems like an issue that I may come across..

 

[The_Doc_Man]

Eurisko

It was NEVER my intent to be insulting, but I also can't take time to write flowery prose (unless you somehow specify that as a requirement for your answer.) Therefore, if I came across badly, I apologize for being a bit abrasive or abrupt. Sometimes I give the fastest answer I can with the intent of touching the important points and moving on. Remember, you are getting free advice here. Don't blame me for trying to minimize my exposure to each problem by giving very short answers. (Blame me if I give WRONG answers, and I deserve any blame I get in that case.)

My job description is neither diplomat nor lawyer nor liar. I tell it like I see it, and I saw a description of events that cannot possibly have happened exactly as you originally described it. This made me think that something is missing here. My follow-up questions and comments were partly due to my teaching background. As has been pointed out, I can get a bit esoteric sometimes, and thank you for trying to draw me back down out of the clouds.

When you come to this forum asking for help, we offer it. But if you don't tell us the significant details required to understand your scenario, we are going to waste time solving something that only vaguely resembles your problem. Why would we want to take our time solving the wrong problem? As other have pointed out after my earlier response, your description was inconsistent with the actual problem. It was probably that difference that triggered my "something ain't right" flag.

Having said that, I stand by my statement that Access is single-threaded and it takes non-trivial work to make it do something in a multi-threaded way. Whether those are stored queries or direct .Execute SQL cases does not matter. The answer is still the same. Particularly since Access is doing a delimited text import (and thus Access is managing that table as opposed to an external application such as Excel), that sequence of five queries that you showed us will be treated linearly with no simultaneity of execution. Each one will finish completely before the next one starts.

Now, here's where I'm going to take a leap of logic. Is there any chance that the "real" queries are machine-generated into a string that you build on-the-fly? And if so, is there a way for you to print out the queries before/after you execute them, so that you can see exactly what was executed (as opposed to what you wanted to execute)? THAT is where you need to focus your attention, because if you merely took the SQL you were using and did a series of Cut/Paste operations into a query design in SQL View, they should behave consistently for each execution. If those text strings are NOT machine generated but are constants, why don't you store them as named action queries so that you can run them by name and assure their consistency that way?