cbishopcgr
asked on
Access - Run 2 make table queries without warning messages
I have two Make table queries that I want to run from One button - I want to NOT have the warning messages about modivying data in the table, deleting the existing table and pasting a new row in the table. The tables just hold some data for another query and can be replaced each time the the make table queries are run...
VB code would be fine - or I can use a macro - I want to run it from a button on a form.
These are the two Make table queries that I want to run without the warning messages:
GL Daily Cash Date Jrnl Number Seq MTQ
CM Daly Cash Date TranType Number Seq MTQ
VB code would be fine - or I can use a macro - I want to run it from a button on a form.
These are the two Make table queries that I want to run without the warning messages:
GL Daily Cash Date Jrnl Number Seq MTQ
CM Daly Cash Date TranType Number Seq MTQ
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This is the only place in my applications where I use a macro. Create two macros. One to turn the warnings off and the second to turn them back on. In the macro, also turn the hourglass on when you turn warnings Off and off when you turn warnings back on. I do this so I always have a visual clue that warnings are off. Leaving warnings off when you are making design changes is extremely dangerous since if you simply close a modified object without saving it, Access will not prompt you to save (no warnings). It silently discards your changes. So the hourglass is a self-preservation tool. I know if the hourglass is on, that warnings are off. Then the code would be:
DoCmd.RunMacro "mWarningsOff"
DoCmd.OpenQuery "GL Daily Cash Date Jrnl Number Seq MTQ"
DoCmd.OpenQuery "CM Daly Cash Date TranType Number Seq MTQ"
DoCmd.RunMacro "mWarningsOn"ASKER
Appreciate the quick response - worked perfectly. Thank you.
Personally, I prefer the Execute method to OpenQuery, for the following reasons:
1. No need to SetWarnings False/True. When you use:
DoCmd.SetWarnings False
You must ensure that you place your
DoCmd.SetWarnings True
statement in a position which ensures that if any errors are encountered, the error handling will include that statement. Otherwise, you could get yourself in a bind where you think you should get a warning and don't.
2. By using the Execute method, with the dbFailOnError argument,
currentdb.Execute strSQL, dbFailOnError
or
currentdb.querydefs("qryYo
you can run the query and not worry about the warnings, unless an error is encountered. You can then use an error handler in your procedure to make any adjustments, rollback the queries, or present a specific error message to the user.
1. No need to SetWarnings False/True. When you use:
DoCmd.SetWarnings False
You must ensure that you place your
DoCmd.SetWarnings True
statement in a position which ensures that if any errors are encountered, the error handling will include that statement. Otherwise, you could get yourself in a bind where you think you should get a warning and don't.
2. By using the Execute method, with the dbFailOnError argument,
currentdb.Execute strSQL, dbFailOnError
or
currentdb.querydefs("qryYo
you can run the query and not worry about the warnings, unless an error is encountered. You can then use an error handler in your procedure to make any adjustments, rollback the queries, or present a specific error message to the user.
docmd.openquery "GL Daily Cash Date Jrnl Number Seq MTQ"
docmd.openquery "CM Daly Cash Date TranType Number Seq MTQ "
docmd.setwarnings true