delete data when date expires

dannyphw · October 23rd, 2004, 08:30 AM

hi..

im doing a event calendar now. and much of the information have to be entered and stored into the database. however, the data and information will be redundant when the event past or expires. is there any code whereby i can set and automatically delete the data from the database once the event has past?

rajanikrishna · October 24th, 2004, 05:59 AM

Hi,

Write an VBS file to delete records from the database. set the scheduled tasks to run this file.

-------------
Rajani

happygv · October 24th, 2004, 08:59 AM

What is the database that is involved in this? Is that SQL server or MS Access? You haven't mentioned about that. If SQL server, you can schedule an job on sql server(under Enterprise Manager >> ServerName >> Management >> SQL server Agent >> Jobs) to run once everyday that checks for past events and deletes. For this you need to have SQL server agent running.

You can use something like this in the job and schedule it to run at a time when there is less load/operation on the server(sometime during midnight or at 12:00 am everyday).

Code:

Delete from TABLE_NAME where DATECOLUMN < getdate()-7

This deletes the rows all that are older than 7 days from now, thus keeps a week's old records always. Reduce/Increase 7 to a lower/higher value that suits your requirement.

If that is MS access, that is best done as RajaniKrishna suggested. Create a vbscript file that does the same and schedule a task on the OS at similar time. Running that daily or weekly would be left to your preference.

Cheers!

_________________________
- Vijay G
Strive for Perfection

dannyphw · October 24th, 2004, 08:04 PM

hi and thanks for ya reply

im using MS Access for now.

can you explain about running the VS script? im kinda new, so i don't really understand ya explanation. thanks

rajanikrishna · October 24th, 2004, 08:17 PM

Hi this is a sample VBS file to delete data from the database.

on error resume next
dim conn,rs,str1
set conn=createobject("adodb.connection")
conn.provider="Microsoft.Jet.Oledb.4.0"
conn.open "\\rajani\donfranco\production\attendance\emp. mdb"
conn.beginTrans
str1="delete from attendance"
conn.execute str1
str1="insert into attendance(eventdate,eventtime,cardnumber) select eventdate,eventtime,cardno from EventListTable in '\\rajani\database\CipherAccess.mdb' where len(cardno)>0"
conn.execute str1

if Err.number <> 0 then
conn.rollbackTrans
else
conn.commitTrans
end if
conn.close
set conn=nothing

Go to windows scheduler and run this file as per u r requirements(daily/weekly).

--------------
Rajani

happygv · October 25th, 2004, 04:57 AM

Hi,

Code:

strSql = "select count(*) as cnt from Table1 where col2 < date()-7"
'strSql = "delete from Table1 where col2 < date()-7"
set con = createobject("adodb.connection")
constr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db1.mdb;User Id=admin;Password=;"
con.open constr

'con.Execute(strSQL)

set rs = con.Execute(strSQL)
wscript.echo rs("cnt")
rs.close
set rs = nothing
con.close
set con = nothing

Create a vbscript file with extension .vbs and add the code given above here.

Then create a batch file and add the following code in it.

Code:

cscript yourvbscript_filename.vbs

open command prompt and execute the batch file in there. See if that gives the count of rows that are older than 7 days. If that seems to be correct, then UNCOMMENT the code that is in GREEN in the VBS file code shown above. And COMMENT all the lines that are marked in BLUE.

NOTE : All that marked in RED in the above code are subject to change based on your setup there.

Then open up control panel and under schedule tasks, add a new task that runs this batch file once every day or every week. That should be what you are looking for.

Hope that explains.
Cheers!

_________________________
- Vijay G
Strive for Perfection

dannyphw · October 27th, 2004, 12:34 AM

thanks for ya help! Appreciate all!