• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1056
  • Last Modified:

Query Update Runs Automatically

I'm having trouble with a function of the update queries in Access. Specifically, when running an update query, it updates the items from the query even if I select "NO - I do not want to proceed with the update" when the prompt comes up. Normally when you select NO at this point it does not update the items. But for some reason it now runs the updates regardless of whether you have OK'd it by selecting YES - run update or not. The option of not running an update (normal) is critical to the work we do. the databases where I'm having this problems are so far only on two databases that are both located here in the same folder.
0
rkroger
Asked:
rkroger
  • 3
  • 3
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Which version of Access?

How exactly are you running the queries?

mx
0
 
harfangCommented:
An update query runs within a transaction. When you select [No] the transaction is rolled back. I see only two ways to mess with the data in that case:

* Your update query calls VB to perform additional editing outside of the transaction (this is a very bad practice, but I've seen it before)

* You are running a series of queries from VB and canceling the first does not cancel subsequent queries (or previous queries have already run at that time).

But I can assure you that an action query run from the interface will allow you to roll back completely (or accept the partial update, without the records where an error was encountered).

(°v°)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"But I can assure you that an action query run from the interface will allow you to roll back completely (or accept the partial update, without the records where an error was encountered)."

Marcus ... did you know that in A97 or even A2000 ... there was a bug ... and even with that setting = Yes .. it *Still* performed the Action !  It finally got fixed.  That's why I asked which version ...

mx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
harfangCommented:
No, I didn't know that. I guess I was luck never to stumble upon that one! Was it a but in the Access interface only, or also of the entire transaction mechanism?
(°v°)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Just when you ran a query from the query grid ... and had that Property 'Use Transactions' ... set to Yes ... the Yes did not matter. As I recall, what actually happened was ... when the message popped up and asked 'Do you want to append 137 records' ... and you said No .. well ... the records didn't get appended ... BUT ... lets say the AutoCounter values were initially from 1 to 100.  Afterwards ... if you went to enter a new record ... the next AN number (in this example) would have been 238 ... if you see what I mean.  There were some other variations of this issue also, but I don't recall all the details.  

mx
0
 
harfangCommented:
Ah, yes, that makes sense. Imagine another user adding a record to the same table while the first user is pondering whether he/she want to commit the transaction. What auto-number should be used then? Couldn't very well be 101...
(°v°)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now