Solved

Set Action query warning's

Posted on 2012-03-12
9
388 Views
Last Modified: 2012-03-28
Hi,

I have several action queries in a database which, when run, perform the action without prompting me to confirm action. The absence of a warning also applies to modifications to select queries, ie if I close down a select query which I have slightly modified to test syntax, it automatically saves select query changes without prompting me.

I have checked in the Database option tab and the "confirm action queries" tick box is checked.  I have also run simple macro to Set systems Warnings to yes.

However  database is still performing action queries without prompt.

ANy suggestions appreciated
0
Comment
Question by:Grasshopper3
  • 7
  • 2
9 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 90 total points
ID: 37712695
Forget SetWarnings ... for exactly the reason (and others) you've discovered.

With CurrentDB
     .Execute "YourActionQueryName1", dbFailOnError
     .Execute "YourActionQueryName2", dbFailOnError
     .Execute "YourActionQueryName3", dbFailOnError
       ' more
End With
0
 
LVL 75
ID: 37712697
With this much safer approach, no prompt.  BUT, should an error occur - the kind you WANT to know about - it will be exposed because of the dbFailOnError ... which you can then process in your error handling routine,

mx
0
 

Author Comment

by:Grasshopper3
ID: 37712713
Will this set the "Prompts" back on for all changes modification, not just the queries named in code?  What i want to do is change database properties so that I am prompted to confirm all action query actions or simple changes to select queries on deleteing tables etc.

if I do the above do i not have to type in all action queries in database (I have several hundred in database) into the code? Will this cover simple changes to select queries which, when I close a query I don't want saved.

At the moment I/other users can do this without confirming deletions etc.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 75
ID: 37712721
This is unrelated to any prompts or SetWarnings ... that's the good news, and there is no bad news.

See your prompts in the Options to Yes ... then you will be prompted for saving changes, deleting, etc ... the things you want to be prompted for.

Trust me ...

mx
0
 
LVL 75
ID: 37712723
prompts
0
 
LVL 75
ID: 37712727
In other words, the Execute method is the BEST of all worlds ...

SetWarnings should be removed from Access as I see it.  But in Macros (forgot those also), it's the only choice you have to avoid the annoying prompts.

mx
0
 
LVL 75
ID: 37712729
BTW ... you can also do this:

Dim sSQL as String
sSQL = <SomeActionSqlString>   ' delete, update, insert or makeTable

CurrentDb.Execute sSQL, dbFailOnError

mx
0
 

Author Comment

by:Grasshopper3
ID: 37712778
From the attached you can see I have the options set to yes but databse is still deleting, saving without prompts.

Frustrating!
ActionQuery.bmp
0
 
LVL 75
ID: 37712916
Open the vba Immediate Window and enter this:

DoCmd.SetWarnings True

and hit Enter.

OR ... reboot your system.  Sounds like there was a SetWarnings False w/o a SetWarnings True

mx
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question