locked
Ms Access Crashes when an update query is run RRS feed

  • Question

  • Hello All,

    First of all thanks a lot for taking time to read the question.

    I have developed an MsAccess database (has vba code too) which crashes exactly when a specific update query is run. The details are as follows:

    A table has two columns of which one stores IDs and the second one a column for count. The update query I developed takes the ID from the first column, searches for the number of occurrences of the ID in column of another table another table and update the result in the second column against the ID. That's it.

    When I run this update query (from query window/ or from vba using docm.openquery / or currentdb .querydefs ("qry name").execute /or Docmd.runsql) the progress bar in the status bar moves close to half and after a while  the database crashes and we get to see the  Ms Access has stopped working screen. Closing it closes MS Access.

    Main table with 2 columns has 12000 rows populated with IDs (Text Data) in the first column.

    The other table into which the DCount searches contains about 60000 records.

    Not sure what's wrong in here. I recreated the query, recreated the base table with another name and other stuff but the database crashes when we reach the update statement.

    I have also done things like, moving all the objects into a new database, turning off the AutoCorrect options (however the Perform Auto Correct option remains turned on after clicking on the Ok button), using the /decompile option with the MsAccess.exe but nothing seem to work.

    Is something wrong with my profile or on my computer. If so then why only on this particular update statement with the DCount. I don't really get a clue of what is wrong and any suggestions are greatly appreciated.

    Thanks,

    Varma

    Wednesday, August 9, 2017 12:41 PM

Answers

  • Hello Peter N Roth,

    The update statement still fails in the original database but the exercise suggested by you made me investigate the difference between the table on which the update statement fails versus the new tables where it goes through successfully. The only difference was the new tables were created by me manually by creating a new table in design mode where the table on which the statement was failing was created by a make table statement.

    Somehow the tables when created by the MakeTable are getting corrupt and leading to weird errors. I deleted the table created by the MakeTable scenario and manually created the whole table which contained 72 columns. After i did that the error message disappeared and things look very good. The table seems to be robust as well.

    To double check, I went back to the databases where users reported same error and created the tables manually. Subsequently the runs were smooth and access never crashed. So not sure why the tables are getting corrupt because of MakeTable but glad that I now have a proven workaround.

    Thanks a lot Peter N Roth!!!

    Thanks,

    Varma

    • Marked as answer by Pradeepv Thursday, August 10, 2017 4:31 PM
    Thursday, August 10, 2017 4:29 PM

All replies

  • What is the language of your Office/Access installation?

    What version are you running? 

    Is it Office365/2016 by any chance?  If so, there is a bug with on of the recent updates that caused this problem with action queries, see: https://www.devhut.net/bugs/action-queries-do-not-work-and-crash-access-in-foreign-language-instl/ I believe it was recently fixed, so I'd try manually pushing an update (File -> Account -> Update) and see if it goes away.  That said, there are new issues that have surfaced with even that update, see: https://www.devhut.net/bugs/access-2016-form-scrolling-doesnt-work/

    Good luck!


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, August 9, 2017 1:44 PM
  • Hello Daniel,

    Apology for missing out on the version. It is Ms Access 2007.

    I was not able to find the File --> Account --> Update option. However I guess it does not apply as the version I am using is 2007.

    Thanks,

    Varma

    Wednesday, August 9, 2017 2:53 PM
  • FYI,

    It fails even when I do it in design mode.

    Just to narrow down the scope, I asked a fellow user to run it on his machine and the result is same. It comes up with the following message:

    Microsoft Access has stopped working

    Windows can try and recover your information and restart the program

    I tried building a select query and it works like a charm. It is the update query that is failing.

    Thanks,

    Varma

    Wednesday, August 9, 2017 3:04 PM
  • What's the SQL statement of your query?

    Have you tried a Compact & Repair?

    Have you tried decompiling?

    Is Access fully updated?

    Have you tried an Office repair?


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, August 9, 2017 3:16 PM
  • It is something like this:

    Table1 has two columns

    Idty -  Text

    NumCount - Number

    Update Table1 Set [NumCount] = DCount("*","Table2","IdtyToSearch='"& [Idty] & "'");

    Table2 is the one where it has to search for number of occurences and update the NumCount column of Table1.

    Compact Repair & Decompile - Yes

    Access fully Updated - It should, I work for a financial institution where the IT team handles all these things.

    Thanks,

    Varma


    • Edited by Pradeepv Wednesday, August 9, 2017 3:27 PM
    Wednesday, August 9, 2017 3:25 PM
  • "Access fully Updated - It should, I work for a financial institution where the IT team handles all these things."

    Don't be so sure of that.  My experience with my financial clients is their IT is horribly behind the times.  Please validate.  I don't have 2007 in front of me so I can't tell you where MS buried the information on that version (they keep moving it around).

    Are you sure there is a value for [Idty] for all the rows of data?


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, August 9, 2017 3:38 PM
  • I will check with IT on the update front.

    As for [Idty] it has multiple values in the equivalent column of the second table ([IdtyToSearch] of Table).

    First I tried using the Dcount on Table2 itself so for each row, it checks how many instances of that value is present in the current column of the current table. When I ran that Ms Access crashed.

    So I took a distinct of the values in column [TdtyToSearch] and inserted them into the Table1 column [Idty]. So no doubt about that, all rows of Table1 have values for column [Idty].

    Thanks,

    Varma

    Wednesday, August 9, 2017 3:54 PM
  • Set up a separate database with the same conditions, except Main table has 3 rows, table 2 has 10 rows. Run your query. What happens?


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, August 9, 2017 3:55 PM
  • In that case, I did not need a second table at all.

    Had three rows with values in IDToSearch column. Built the update Query to search in the same column and updated the next column (NumcCount) with the number of occurences. No problems at all. It runs fine.

    Just to check if more volume of records is the problem, I kept on adding the data 20K,30K,60K and 120K records, but works fine.

    I copied the 60K records from the table where it was failing and pasted it into the test database table. The update query works fine.

    Now I am really not sure of what the reason could be? :-)

    Thanks,

    Varma


    • Edited by Pradeepv Wednesday, August 9, 2017 4:33 PM
    Wednesday, August 9, 2017 4:13 PM
  • So it works now?

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, August 9, 2017 6:06 PM
  • Hi Pradeepv,

    it is possible that something get corrupted , which is not getting corrected by compact and repair.

    you can try to delete that query and make a new query.

    if possible for you then only copy the data to the new database and create a new query in new database.

    if possible for you then post your database here, we will try to run that query to reproduce the issue.

    let us know about the results.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 10, 2017 1:42 AM
  • Hello Peter N Roth,

    The update statement still fails in the original database but the exercise suggested by you made me investigate the difference between the table on which the update statement fails versus the new tables where it goes through successfully. The only difference was the new tables were created by me manually by creating a new table in design mode where the table on which the statement was failing was created by a make table statement.

    Somehow the tables when created by the MakeTable are getting corrupt and leading to weird errors. I deleted the table created by the MakeTable scenario and manually created the whole table which contained 72 columns. After i did that the error message disappeared and things look very good. The table seems to be robust as well.

    To double check, I went back to the databases where users reported same error and created the tables manually. Subsequently the runs were smooth and access never crashed. So not sure why the tables are getting corrupt because of MakeTable but glad that I now have a proven workaround.

    Thanks a lot Peter N Roth!!!

    Thanks,

    Varma

    • Marked as answer by Pradeepv Thursday, August 10, 2017 4:31 PM
    Thursday, August 10, 2017 4:29 PM
  • Deepak,

    Yes it was corrupt table that was causing the issue but not sure why the tables created via MAKETABLE command were getting corrupted.

    Thanks for your time!!!

    Thanks,

    Varma


    • Edited by Pradeepv Thursday, August 10, 2017 4:31 PM
    Thursday, August 10, 2017 4:30 PM