none
Write Script to Clear Access DB every week RRS feed

  • Question

  • In my testing facility, I would like to write a script/macro that will clear/erase automatically the test results weekly from the week's production. The reason is that every month or so,  the Access DB gets clogged up with 1000s of test results and shuts down the system. I then have to manually go to that computer and clear/erase.

    Please advise on the best way to do this, or provide script if someone has done this already.

    Thanks

    • Moved by Steve Fan Wednesday, April 12, 2017 1:50 AM
    Tuesday, April 11, 2017 10:35 AM

All replies

  • Hi,

    This forum is for general questions and feedback related to Microsoft Office. Since your question is more related to script/macro, I'll move it to a more appropriate forum:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Steve Fan


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, April 12, 2017 1:49 AM
  • You could write some Delete queries, then kick them off using a Macro. Then you could schedule a Windows Task to run the Access app with this macro, using the /X command line switch of msaccess.exe.

    But if your database bogs down with thousands of records, there is something SERIOUSLY wrong with your database design.


    -Tom. Microsoft Access MVP


    Wednesday, April 12, 2017 3:07 AM
  • Hi,

    Another option, if possible, is to store your test results in a Temporary Database.

    Hope it helps...

    Wednesday, April 12, 2017 3:21 AM
  • I'll second Tom's general sentiment that something seem very wrong with your database.  If you are willing to share more information with us perhaps we could help you find a way of fixing it so it doesn't bog down.

    Also, with regards to using Delete queries ..., don't forget to also perform a Compact on the database afterwards.

     

    Another idea, would be to make a master copy of the db with no data, and simply use a scheduled task to replace the production one with the master copy.  No Deletion or Compact required, just a simple copy/paste.


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

    Wednesday, April 12, 2017 1:07 PM
  • Ok, so I guess you have some kind of methodology that you apply for the actual cleansing part of this requirement, right.  All you need to do is schedule, right.  Use MS Task Scheduler:

    http://www.digitalcitizen.life/how-create-task-basic-task-wizard

    Also, to have your DB perform an action when it opens, try this concept.

     

    Create an AutoExec macro

    If you have already created a macro that contains the actions that you want to occur when the database starts, just rename the macro AutoExec, and it will run the next time that you open the database. Otherwise, follow these steps to create a macro:

    1. On the Create tab, in the Other group, click Macro. If this command is unavailable, click the arrow beneath either the Module or the Class Module button, and then click Macro.

    2. In the Macro Builder, in the first empty Action cell, select the action that you want to perform. If applicable, under Action Arguments, type the appropriate values in the argument boxes.

      If you cannot find the action you want, on the Design tab, in the Show/Hide group, make sure Show All Actions is selected. This expands the list of actions that you can use, but the list will include some actions that will only run if the database is granted trusted status. For more information, see the articles Decide whether to trust a database or How database objects behave when trusted and untrusted.

    3. Repeat step 2 for each additional action you want to occur.

    4. Click Save, and in the Save As dialog box, type AutoExec.

    5. Click OK and then close the Macro Builder. The new macro will run the next time that you open the database.


    MY BOOK

    Thursday, April 13, 2017 3:41 AM