Solved Running Queries in VBA locks the record

[Lochwood]

I have an append Query and an Update Query which i run via a button on the form. If i run them manually both work fine but if i run them from the button, the append query works but the update query doesnt as though the append query is locking the record some how but as i said if i run them manually they work fine. any ideas?

VBA
DoCmd.OpenQuery "Append_Query"
DoCmd.OpenQuery "Update_Query"

 

[gemma-the-husky]

if you run these in sequence, the second query won't start until the first one completes. Action queries are non-interruptible atomic processes if you will.


There may also be issues with the first query potentially needing to be cancelled by the user.
[edit]
I mean an append query may lock the table in case it needs to undo by command of the user. If another user is making changes at the sametime it cause an issue.

 

[theDBguy]

Hi. Are both queries modifying the same table? If so, there may be a timing issue.

 

[bob fitz]

Could it be that you need a DoEvents between the two querries:
DoCmd.OpenQuery "Append_Query"
DoEvents
DoCmd.OpenQuery "Update_Query"

 

[Lochwood]

the append query appends the data to a history table before the update query runs so we have a copy of the data before it changes. there may be a timing issue but i dont think so. here is the error i get on the update query that runs after the append.

Tried the DoEvents but same issue

 

[cheekybuddha]

Use:

On Error GoTo Err_Here

  CurrentDb.Execute "Append_Query", dbFailOnError
  CurrentDb.Execute "Update_Query", dbFailOnError

Err_Here
' Code to bail gracefully
  MsgBox "An Error occured - bailing!"

Then you can handle errors within your code and you won't get Access' popups

 

[theDBguy]

there may be a timing issue but i dont think so.

You don't think so, or did you confirm it? For example, you said both queries work fine if you do it manually. How long does it take you to run both queries manually? How long does it take you to run the first query before you run the second query? Have you tried adding the same time delay in your code just to see if it still doesn't work? For example, if you say it takes you only one second to run the first query before you run the first query, then you could test your code like this:

1. Run first query
2. Wait one second
3. Run second query

Just a thought...

 

[The_Doc_Man]

I'm with Dave (Gemma) here. Access is single-threaded with respect to any user code. Further, you are using the DoCmd actions rather than db.Execute, so there has to be some synchronization between subsequent SQL commands. HOWEVER, it is possible that you have a slight overlap between Access (the user interface part) and ACE (the actual DB engine) since they are two separate processes.

The "DoEvents" method that was suggested is one way to do it. There is also inserting this next line between the two queries.

DbEngine.Idle dbRefreshCache

IF this works, you should have a smooth transition from one query to the next. If it doesn't work, then the problem is outside of that code because something else is locking the record/table in question.

DBEngine.Idle method (DAO)

Idle method

docs.microsoft.com

The first paragraph of the linked article explains what it does pretty clearly. Therefore, if it doesn't work to fix your problem, then your interference is elsewhere.

 

[gemma-the-husky]

the append query appends the data to a history table before the update query runs so we have a copy of the data before it changes. there may be a timing issue but i dont think so. here is the error i get on the update query that runs after the append.

Tried the DoEvents but same issue

View attachment 90382

So one record was locked. Is another user active? Do you have any active record locking, or is it all optimistic? Try it again with exclusive access, add a couple of seconds delay between commands. How many records are there in the table? Maybe there is a corrupt record. Try copying blocks of records until you find a culprit. You have something tangible to investigate now.

 

[Isaac]

@Lochwood what's the back end?

 

[Pat Hartman]

Action queries produce prompts to the user. Until the prompt for the append query is responded to, the code stops. Switching to the .Execute method avoids this issue since you won't get any warning messages.

If you want to stick with the Access method of .OpenQuery, then you have to turn warnings off. Since turning warnings off is so dangerous, I created two macros.
mWarningsOn - turns the hourglass off and warnings on
mWarningsOff - turns the hourglass on and warnings off.

That gives you a visual clue. If you find the hourglass on, then your macro to turn the warnings back on never ran. run it to clear the hourglass.

Run the off macro on the line above the Append queyr and run the on macro on the line after the update query.

 

[Lochwood]

Hey guys i got this fixed.
In my code i run 2 queries and before they run we have a msgbox prompt. if clicked yes it runs the queries but before it runs them, it populates 2 fields on the form with username and Now(), these fields are then used to populate fields on the first query and subsequently locking the record to allow the second query to run. I have since removed this step and just added them directly to the query and both worked fine. thanks for your help

 

[Isaac]

Glad you got it straightened out