Results 1 to 12 of 12

Set warnings false - is this WIndows session specific, or Access session specific?

  1. #1
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    50

    Set warnings false - is this WIndows session specific, or Access session specific?


    What I mean is, when you have "Set warnings false" and then later at the end of a Module, turning it back to True, what happens if it is set to false and never set back to true? Is this going to say OFF/False for the rest of the day until you log out of windows, or should it revert to True when you close and then reopen the database?

    Thanks,

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,356
    Why don't you test it and find out? Let us know.

    I've never tested this myself. I avoid code that requires this but if I do use it I set back to True when the procedure completes and design code to set back to True in case of runtime error.

    I suspect it is a session setting.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,485
    At the very least, I believe it will be reset when you close/reopen Access.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  4. #4
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    50
    Quote Originally Posted by June7 View Post
    Why don't you test it and find out? Let us know.

    I've never tested this myself. I avoid code that requires this but if I do use it I set back to True when the procedure completes and design code to set back to True in case of runtime error.

    I suspect it is a session setting.
    That's just it - I'm having a hard time determining what is wrong in my set up. I have the lines in my module that turn off/on warnings "commented" out with a ', but the action queries inside my module, which is just a list of queries to run, are not giving warnings. I wanted to see all the warnings to make sure all queries were okay before disabling warnings again.

    Restarting Access does not revert it back. Well, to clarify, manually clicking on an action query gives a warning, but not when run with "execute" command inside the module. I just want to make sure things are working...

    Is the setting perhaps inside the VBA window and not the Access db?

    THanks,

    EDIT: I should add that this is a split database, which I have just implemented within the past hour or so. The table tehse action queries are hitting is in the back-end. So, would it not fire-off warnings for that reason? I don't seem to remember Access doing that...

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,485
    That's just it - I'm having a hard time determining what is wrong in my set up. I have the lines in my module that turn off/on warnings "commented" out with a ', but the action queries inside my module, which is just a list of queries to run, are not giving warnings. I wanted to see all the warnings to make sure all queries were okay before disabling warnings again.
    Have the been turned off already (by some other section of code or something)?
    Why not explicitly turn them on in your code before calling your action queries (there is no danger in setting it to True if it is already True)?

    If you still have issues, please post your VBA code so we can see how it is written.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,113
    There is a clue (perhaps) where it says 'I'm using Execute '. If this is Database.Execute, this does not provide warnings, which is the whole point of it. You actually have to tell Access to provide a warning if the query fails (dbFailOnError).

    If that's not applicable, much more of the code is needed to attempt to decipher what's going on and what some of the statements really mean.
    - "doesn't work" is no help. What's happening? Error messages? Where??
    - Use code tags for code/sql. Implement changes in copies of your database.
    Irregardless, ain't no such word as "reoccur".

  7. #7
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    50
    Thank you both! I think the biggest culprit is that I recently changed from using docmd.openquery to database.execute, which as you stated, does not provide warnings. That is something I will probably temporarily change in case one of the queries is doing something unexpected, and then revert back if desired.

    Marking as Solved...

  8. #8
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,006
    One other thing.
    Look in Database Options...Client Settings at the confirm settings for action queries.
    Is the checkbox ticked? If not, you will never see warnings when you run action queries.
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment
    nil illegitimi carborundum est

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,113
    Is the checkbox ticked? If not, you will never see warnings when you run action queries.
    You know me - black is black and white is white and there's nothing in between.
    So what did you really mean, as I'm sure you know that statement isn't really true (as written). Maybe the key word is "warnings", but are you applying that to when the affected record count is zero?

    EDIT: I might have to retract that statement. According to my understanding, dbfailonerror is supposed to generate a run time error if the record to be affected cannot be updated/deleted.
    Even with the option selected (to be warned) and using CurrentDb.Execute with dbfailonerror, I'm not getting warned even with the target field in the target table being locked because it's being edited. Yet M$ says
    In a Microsoft Access workspace, if you provide a syntactically correct SQL statement and have the appropriate permissions, the Execute method won't fail — even if not a single row can be modified or deleted. Therefore, always use the dbFailOnError option when using the Execute method to run an update or delete query. This option generates a run-time error and rolls back all successful changes if any of the records affected are locked and can't be updated or deleted.
    The record locking option is set to "edited record", yet no warning. So none of my testing makes any sense.
    I figured for sure, regardless of the setting you mentioned, warnings would be given if Execute failed with dbfailonerror and an attempt was made to alter a locked record, yet I can't seem to raise an error in any case. I do get the warning about someone else editing the record, but that's not what I was expecting.
    - "doesn't work" is no help. What's happening? Error messages? Where??
    - Use code tags for code/sql. Implement changes in copies of your database.
    Irregardless, ain't no such word as "reoccur".

  10. #10
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,006
    Hi Micron

    Quote Originally Posted by Micron View Post
    You know me - black is black and white is white and there's nothing in between.
    So what did you really mean, as I'm sure you know that statement isn't really true (as written). Maybe the key word is "warnings", but are you applying that to when the affected record count is zero?

    EDIT: I might have to retract that statement. According to my understanding, dbfailonerror is supposed to generate a run time error if the record to be affected cannot be updated/deleted.
    Even with the option selected (to be warned) and using CurrentDb.Execute with dbfailonerror, I'm not getting warned even with the target field in the target table being locked because it's being edited. Yet M$ says
    The record locking option is set to "edited record", yet no warning. So none of my testing makes any sense.
    I figured for sure, regardless of the setting you mentioned, warnings would be given if Execute failed with dbfailonerror and an attempt was made to alter a locked record, yet I can't seem to raise an error in any case. I do get the warning about someone else editing the record, but that's not what I was expecting.
    I meant EXACTLY what I said
    If you untick the 'Confirm Action Queries' option you will NEVER get a warning if you do any of these
    a) run an action query from the nav pane
    b) run an action query using DoCmd.OpenQuery
    c) run query SQL using DoCmd.RunSQL

    b) & c) are still true EVEN if you add DoCmd.SetWarnings True IMMEDIATELY before running the action query or SQL (which of course you wouldn't normally do)

    Ditto if there are no records that will be updated/appended
    The only thing I can think of to trigger a message is if the source or destination table don't exist when of course you get an error message

    In the attached example, I have unticked the Confirm Action Queries option
    The example includes my current favourite - the combined update and append query or UPEND query (AKA AppUp & Upsert)
    I've added code to run the 'UPEND' & update/append to tblOld from tblNew using each method

    P.S I ALWAYS set the record locks option to no locks but for this example also tested using locks for EDITED RECORD and ALL RECORDS - same outcome
    It would appear to indicate that the confirm checkbox takes priority over everything else

    So the MORAL of today's sermon is : ALWAYS tick the Confirm checkboxes
    I always tick all three
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment
    nil illegitimi carborundum est

  11. #11
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    50
    thanks for the additional info! That will be helpful in making sure everything is set up optimally.

  12. #12
    ridders52's Avatar
    ridders52 is offline Voodoo Is Practised
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    2,006
    You're welcome

    Just realised I forgot to attach the example db that I mentioned in my last post.
    Its attached now!
    Attached Files Attached Files
    Colin (Mendip Data Systems), Website, email
    If this has helped, please click the star button and leave a comment
    nil illegitimi carborundum est

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-23-2017, 03:19 PM
  2. mapi.session (emailing via CDO)
    By GraeagleBill in forum Programming
    Replies: 17
    Last Post: 10-05-2015, 04:01 PM
  3. Session variables
    By accessnewb in forum Programming
    Replies: 5
    Last Post: 08-04-2011, 10:45 PM
  4. Replies: 6
    Last Post: 11-18-2010, 03:00 AM
  5. Replies: 0
    Last Post: 11-28-2005, 12:04 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums