Results 1 to 10 of 10
  1. #1
    YoYo is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    4

    Error with DoCmd.SetWarnings False/True and Append or Create table

    Hi,

    I have some code that creates some temporary tables so they can be used in recordsets. It all works fine, but, when I apply SetWarnings as false to suppress the messages (deleting recors, make table? etc), on the first time of running it I get:

    Runtime error 3000
    Reserved error (-3034); there is no message for this error

    But then after the frst time it makes the error, it seems to work ok for future attemps after I come out of the debugger.

    My first process was to make tables each time, I then changed to deleting just the contents of the tables with a query then appending the data but again the same message. At tleats the deleting of the contents seems to be ok.

    I tried CurrentDb.Execute the queries instead of DoCmd.OpenQuery but had the same message. I then tried executing a sql string instead of the query names but the same issue.

    The database is split and I get the error each time I link to either the DEV or Live back end. Tables are not created/amended etc. on the back end but on the front end.



    I've searched and searched and I see online is that SetWarnings False should just prevent the messages.

    Any ideas on how I can either append onto my tables or delete the tables and create new ones each time without having this error, or the user have to click the various message requests to get through?

    Thanks.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,756
    You're saying if you don't use SetWarnings then there is no issue? If so, it must be a problem with your code so perhaps you should post it. Please use code tags (# on post toolbar) to avoid issues.
    EDIT - if you convert your string(s) to usable query sql does it run without error?
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,834
    setwarnings off will suppress access action errors like queries, etc.

    system errors of backend db errors in like sql server are not suppressed. RESERVED ERR XXX
    something severely went wrong on the BE, or an Access internal error. (not vb code ,nor query actions)

  4. #4
    YoYo is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    4
    My code and the entire process works fine until I put the setwarnings false/true lines in to hide and then reactivate the: "You are about to run an apend query" messages from the user.

    It's not a SQL server back end just linked to access back end tables. Here is just a part of the code, it's really just running one qurey that deletes all the contents of the table, then the next query appends data into the table by selecting it from another table using one parameter.

    I've tried using the sql rather than built queres and agian it is fine until I put the Warnings bt in to try and supress the messages.

    I suspect something must be corrupted somewhere then.

    Code:
    'delete data and then apopend new data to the holding table of data to query as a recordset
    
    DoCmd.SetWarnings False
    
    
    DoCmd.OpenQuery "qryDelHoldingContent"
    
    
    DoCmd.OpenQuery "qryPOHoldingAppend"
    
    
    DoCmd.SetWarnings True

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,756
    If you remove SetWarnings and when you click OK/Yes on the prompt the query runs fine then I'd have to agree with you. Sounds like corruption. In that case, you could try a decompile to see if the code project is the culprit. If it doesn't fix the issue, then try importing everything into a new db. FWIW, I would not use SetWarnings without an error handler that made sure they were turned back on. Can't tell if you're doing that but I suspect not.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  6. #6
    YoYo is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    4
    Quote Originally Posted by Micron View Post
    If you remove SetWarnings and when you click OK/Yes on the prompt the query runs fine then I'd have to agree with you. Sounds like corruption. In that case, you could try a decompile to see if the code project is the culprit. If it doesn't fix the issue, then try importing everything into a new db. FWIW, I would not use SetWarnings without an error handler that made sure they were turned back on. Can't tell if you're doing that but I suspect not.
    I'll try importing the data into a new db, and no I hadn't got the warnings in error handling but am aware I need to, so will get that in place.

  7. #7
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,312
    Try using qdf.Execute dbfailonError (where qdf is a querydef based on your queries) instead of Docmd.OpenQuery.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,958
    Other possibility is your code is trying to append before the delete query has finished

    Try puttiing

    Doevents

    After the delete query and before the append query

  9. #9
    YoYo is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    4
    Hi, well I've got it working, thanks for the replies.

    I'm not entirely happy as I feel I'm forcing it. I added in the DoEvents and made sure I was again Executing rather than using DoCmd, but was still getting the problem.

    I think I have finally got around it by:

    1) Putting an If in error handling that if the error number is 3000 then resume.
    2) Leaving off the 'dbfailonError' on the executes

    The resume is there to mimick the fact that if I just 'play' through the debugger (all 3/4 appends that I do), the whole process worked fine.
    And my understanding is that the dbfailonError stops my table deletion/creation and append queries from having the data changed when there is an error message, I don't actually want this, I want the data changes kept and then the resume to force the continuation through all of them.

    Luckily this whole process is just to generate a report to view some figures at a glance, (some of which need transposing from rows to columns to match the others hence all the hassle) and so all the data changes are just into temporary tables the only purpose being to create the report from them, so they don't affect the underlying core data at all.

    Fingers crossed, its all sorted now.

    Thanks.

  10. #10
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,312
    Glad to hear you got it working, good luck with your project!
    Stay safe!
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. True/false looping only showing the false.
    By Ultimateneo in forum Queries
    Replies: 3
    Last Post: 02-09-2021, 11:41 AM
  2. Table True False Checkbox
    By Tuckejam in forum Access
    Replies: 2
    Last Post: 04-30-2020, 04:47 PM
  3. Error During Loop For Updating True/False
    By Levonas in forum Programming
    Replies: 10
    Last Post: 03-06-2015, 12:21 PM
  4. Replies: 8
    Last Post: 10-06-2014, 03:02 PM
  5. Create report that is True/false dependent
    By nomadinlondon in forum Reports
    Replies: 1
    Last Post: 03-19-2013, 11:24 AM

Posting Permissions

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