[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

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

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,

  • 3
  • 2
  • 2
  • +1
6 Solutions
Do you mean compacting and repairing?
turbot_yuAuthor Commented:
no, just clean all the data
you will need to be more specific - what potential problems will need fixing?
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Chuck WoodCommented:
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,

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
turbot_yuAuthor Commented:
The data volumn is too big, I just want to delete all the data every morning.
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


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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now