Delete table values automatically

  • Thread starter Thread starter Lp12
  • Start date Start date
L

Lp12

Hi All,
I want to create a routine that will clean all records from a specific table
automatically once a week (if possible) or each time that the MDB opens up.
Any thoughts?
Thanks a lot in advance
 
Hi,
the code to empty a table is simple

Public Sub EmptyTable()
Dim strSQL as String
Dim db as DAO.Database

Set db = dbengine(0)(0)

strSQL = "DELETE FROM TableName"
db.Execute strSQL, dbFailOnError

Set db = Nothing
Exit Sub

the above code goes in a standard module
when your database opens, you call the routine EmptyTable

Jeanette Cunningham
 
Hi,
I assume this is a single user database.
Does this database have a startup form or a main menu form?

On the menu/toolbar of the main access window go
Tools >Startup > find the combo for Display Form/Page and choose your
startup form or your main menu from the combo

in the Load event of the form put
Call EmptyTable

this will make the code find the routine called EmptyTable in the standard
module and it will delete any data from that table

The routine could be called from an AutoExec macro instead of a startup form
or main menu form

Jeanette Cunningham
 
Thanks Jeannete.
Do I need to activate any reference lib? when I run the emptytable() I get a
compile error for the dao object.
I've tried to search the VB editor Tools>Reference but couldn't find any
reference for DAO lib.
Any thoughts?
 
Hi,
I have to go now, if no one else explains what to do next, I will post back
maybe tomorrow or the next day.

Jeanette Cunningham
 
You need to add the reference library. It will be titled something like

Microsoft DAO 3.6 Object Library
(The 3.6 is the Access 2000 version)

Open a VBA module
--Select Tools: References from the menu
--Scroll down the list until you find the library
--Select it by checking it
--Click the OK button

That should do it.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Back
Top