none
Why does CompactDatabase hang intermittently? RRS feed

  • Question

  • I am using CompactDatabase within an Access VBA application to compact a large data file (~800MB). The line in the code is:

    DBEngine.CompactDatabase FileName1, FileName2, dbLangGeneral & ";pwd=pwd1", dbVersion40 + dbDecrypt, ";pwd=pwd2"

    The compact works intermittently and sometimes Access just hangs. I am using Access 2016 MSO (16.0.6528.1017) 64-bit on a Windows 10 machine.

    When the same code is run on a Microsoft Vista machine with Access 2007 (12.0.6735.5000) SP3 MSO (12.0.6743.5000) 32-bit it executes without ever causing problems.

    Is there any way of preventing Access 2016 from hanging in the above scenario even if the solution results in Access returning from CompactDatabase with an error?

    Help with this problem would be greatly appreciated.

    Thanks

    Infrazee

    Wednesday, March 16, 2016 10:42 PM

All replies

  • Hi Infrazee,

    I made a test with DBEngine under Access 2016 16.0.4312.1000 64-bit, it worked correctly. To check whether this issue is related with Access 2016 or this large data file, I suggest you create a simple Access database and run this code again.

    In addition, if you open this large data file, and click Database tools->Tools->Compact and Repair Database, will Access application hand?

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, March 18, 2016 2:08 AM
  • Hi Edward,

    Thank you for your reply.

    As I indicated in my description the problem is intermittent. To demonstrate, create a form with a button and put the following code in the click event, setting FName1 (Large file about 950Mb), FName2 and pwd1 appropriately:

    On Error GoTo CompactButton_Click_Err

        DoCmd.Hourglass True
        DBEngine.CompactDatabase FName1, FName2, dbLangGeneral & ";pwd=pwd1", dbVersion40 + dbDecrypt, ";pwd=pwd1"

        Kill FName1
        Name FName2 As FName1
        MsgBox "Compact Done"

    CompactButton_Click_Exit:
        DoCmd.Hourglass False
        Exit Sub

    CompactButton_Click_Err:
        MsgBox Error$
        Resume CompactButton_Click_Exit

    After clicking the button executing the code a few times Access just hangs and has to be closed using the Windows Task Manager. Furthermore, if you look at the copy of FName2 created you will find that it has been corrupted.

    I look forward to your response.

    Friday, March 18, 2016 1:54 PM
  • By any chance are you compacting over a network? That's the only time I've seen this issue. If That's the case, copy the database to your local drive before compacting it.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, March 18, 2016 2:47 PM
  • Hi infrazee,

    Did you compact over a network as reply from Bill?

    For compacting a large file, it might cause much time and Access may hang, how long you wait before you close Access with task manager?

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Saturday, March 19, 2016 5:42 AM
  • Hi Bill,

    Both files are on a local machine so this is not the solution, thank you.

    Regards

    Sunday, March 20, 2016 10:58 AM
  • Hi Edward,

    Thank you for your response.

    In answer to your question, I have a fast machine with a lot of Ram so a compact normally takes a few seconds.

    When Access hands I can wait many minutes and Access does not return. Also, if I inspect the folder where the compacted file gets written, there is a "compacted" file with a size smaller than that expected.

    Regards,

    Sunday, March 20, 2016 11:05 AM
  • Hi Infrazee,

    Do you mean compacting takes a few minutes manually, and it hands when you use this code? I suggest you put a break point and debug this code to check whether there is any error. What is file type of your Access db? Could you share us file extension for FName1 and FName2?

    To check whether it is related with your environment, I suggest you create a clean VM with Access 2016, and test whether this issue still exist.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, March 21, 2016 3:02 AM
  • Hi Edward,

    I seem to have caused some confusion. when I said "Access hands", I actually meant to say that "Access hangs". In other words the application stops responding.

    The file extensions are .mdb (dbVersion40) for both FName1 and FName2.

    The example code I detailed in my response of 18 March is so simple that I am positive the problem occurs in the CompactDatabase command.

    With regard to creating another environment in which to execute the code I will do that and get back to you in a few days when I am able to do this.

    Thank you for your assistance.

    Regards,

    Infrazee

    Tuesday, March 22, 2016 11:40 AM
  • Hi Infrazee,

    >> I seem to have caused some confusion. when I said "Access hands", I actually meant to say that "Access hangs". In other words the application stops responding.
    What I mean is that do you mean compacting manually worked correctly, but it stops responding when you use this code?

    >> The file extensions are .mdb (dbVersion40) for both FName1 and FName2.
    I suggest you convert mdb file to accdb file, and then try this code again with extensions are .accdb.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, March 24, 2016 5:43 AM
  • Hi Edward,

    Thank you for your response.

    Unfortunately changing the .mdb file to a .accdb file is not acceptable for the application I am working on. Also, running the compact manually over and over again eventually causes Access to hang.

    I tried running a test program on nine different computers and of the nine computers, only two ran consistently without problems!

    The only thing common to all nine computers is that they all used Intel Processors. And, as far as I can tell the problem occurs on any machine that has a multi-core processor! Another thing I observed is that a machine that exhibited the problem with one version of Access would also exhibit the problem with another version of Access (Access 2010, Access 2013 and Access 2016).

    Another problem I observed is that every now and then an error would appear "The query cannot be completed. Either the size of the query results in larger than the maximum size of a database (2GB), or there is not enough temporary storage on the disk to store the query result."

    When this spurious error occurs the compact process terminates gracefully and Access does not just "hang". This error I know to be spurious because all the machines have more than enough RAM and Disk space.

    If the spurious error always appeared instead of Access just hanging, this would be preferable to what is happening at present.

    Regards,

    Infrazee

    Thursday, March 31, 2016 5:53 PM
  • Hi Infrazee,

    >> I tried running a test program on nine different computers and of the nine computers, only two ran consistently without problems!

    If you compact database in these two computers with your original mdb file, will Access hang?

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, April 1, 2016 1:23 AM
  • Hi Edward,

    In answer to your question, no on these two machines Access does not hang.

    Regards,

    Infrazee

    Friday, April 1, 2016 10:43 AM
  • Hi Infrazee,

    >>on these two machines Access does not hang

    Based on this, I assume it is related with computer environment. I suggest you uninstall and reinstall your Office for a try.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, April 5, 2016 1:20 AM
  • Hi Edward,

    In answer to your question, no on these two machines Access does not hang.

    Regards,

    Infrazee

    Have you checked the health of the problem computer?

    1. Is the hard drive badly fragmented or low on free space?
    2. Has it been running for more than a couple days without being rebooted?
    3. Does it have more than one virus scanner running on it?

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, April 5, 2016 2:54 PM
  • Hi Bill,

    Thank you for your response.

    After the responses I received from you and from Edward Z I did the following:

    1) Wiped a computer and installed Windows 10 and Office 2010. The only other thing I did was to install chrome which is my browser of choice. NO OTHER SOFTWARE HAS BEEN INSTALLED ON THE MACHINE.

    2) I then went and ran Windows Update to ensure I have the latest versions of the operating system and Office 2010 installed.

    3) I created a new accdb database containing a single form that has a button on it with the following event handler:

    Private Sub CompactButton_Click()
    On Error GoTo CompactButton_Click_Err

        If Dir("C:\Temp\TemporaryFile.accdb") = "TemporaryFile.accdb" Then Kill "C:\Temp\TemporaryFile.accdb"
        DoCmd.Hourglass True
        DBEngine.CompactDatabase "C:\Temp\ALargeFile.accdb", "C:\Temp\TemporaryFile.accdb"
        DoCmd.Hourglass False
        MsgBox "Compact Done"

    CompactButton_Click_Exit:
        Exit Sub

    CompactButton_Click_Err:
        MsgBox Error$
        Resume CompactButton_Click_Exit
        Resume Next

    End Sub

    4) I then run the program.

    What the event handler does is to compact a large file (1.2GB) into a temporary file. The original file is never changed and the temporary file is deleted prior to the next iteration. It therefore, should be able to press the button and run the event handler ad nausiam as Access is just doing the same thing over and over again.

    After a number of iterations Access stops responding and never completes the task! There is no fixed number of iterations before Access hangs, on the test machine it takes about four iterations but this is not consistent. The test machine has ample RAM (8GB) and available disk space (~400GB).

    From what I have described, you would assume that there must be something wrong with the test machine. However, the same behaviour I observer on 6 other machines! I believe that it is extremely unlikely that they are all faulty.

    Of 9 machines loaded with various operating systems (Windows Vista, 10, Server 2008, Server 2012) and various versions of Access (2010, 2013 and 2016), 7 display this behaviour and only 2 work consistently without the described problem!

    I therefore must conclude that there is a problem with the Access Compact!

    I cannot avoid using the compact because my databases frequently have data added and removed which causes the databases to grow continuously.

    I look forward to your response.

    Friday, April 8, 2016 8:17 AM
  • I suggest reading the following thread and perhaps consider revising your code.

    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, April 8, 2016 1:41 PM