how to auto clear up the database with a fixed time interval

Posted on 2005-04-19
Last Modified: 2012-06-27
Hi Experts,

May I know how to auto clear up the database with a fixed time interval.
I am using access and some data is coming in by ODBC continuesly.

Thanks and Regards,

Question by:turbot_yu
    LVL 18

    Assisted Solution

    Do you mean compacting and repairing?

    Author Comment

    no, just clean all the data
    LVL 18

    Assisted Solution

    you will need to be more specific - what potential problems will need fixing?
    LVL 16

    Accepted Solution

    If you want to clean up (clear) a table, you can use this sub:

    Private Sub ClearTable(Table As String)
        Dim cnn As ADODB.Connection
        Set cnn = CurrentProject.Connection
        ' clear the table
        Dim strSQL As String
        strSQL = "DELETE * FROM " & Trim(Table)
        cnn.Execute strSQL
    End Sub

    Here is how you implement it in VBA:

    ClearTable "tblMyTable"

    I hope that helps,

    LVL 58

    Assisted Solution

    There are two questions here:

    1) "how to [do something automatically] with a fixed time interval"

    There are two ways to do this: a form's timer function and the windows task scheduler.

    a) Form Timer

    If you have a form that stays open all the time, you can set the form's timer interval to the desired time interval (in miliseconds). The event "Form Timer" will then be executed regularily.

    b) Windows Task scheduler

    If you create a shortcut in the scheduler using the /x command-line switch, the specified macro will be executed. This works even if you are working with the database, as you can open the same database twice on the same computer. Of course, the macro should also close the database once done.

    2) "how to clear up the database"

    This is not clear. I suspect that a series of queries is the general answer (delete queries?). Create all the "clean-up" queries you need and execute them through a VB function or a macro.

    Hope this helps

    Author Comment

    The data volumn is too big, I just want to delete all the data every morning.
    LVL 18

    Assisted Solution

    In that case - windows task scheduler is a good idea (as harfang suggested).
    With harfang's first option, on the form to stay open -
    on timer event:
    if time>#16:00:00# and time<#16:01:00# then
    strSQL="delete * from table1"
    currentdb.execute strSQL
    end if

    and set the timer interval to 60000


    LVL 58

    Assisted Solution

    What about deleting the entire database?

    You can have a "master" database (where you will add new queries, etc). Each morning, you run a small vb script that overwrites the "daily" database prior to the next data import...

    Else, you will have to add steps to also compact the database. If you do not like the idea of overwriting the daily working database, you will probably need two entries in the task scheduler, one for "clearing", one for compacting.

    1) run a macro to clear tables:

    create a shorcut in the task scheduler like this:

    "C:\(path to access)\MSACCESS.EXE" "(full path to your database).mdb" /x macCleanAll

    The macro macCleanAll will contain one line for each needed delete query and one line to quit access. Try to run that macro with a stopwatch, so you have an idea on the time it needs to run (probably only a few seconds).

    2) compact the database

    create a shortcut like this:

    "C:\(path to access)\MSACCESS.EXE" /compact "(full path to your database).mdb"

    And set the trigger option so that it runs after the first, e.g. 10 minutes later.

    Hope this helps

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now