auto delete by date

[dlbdennis]

I'm trying to add a line of code to my header. So when a user logs in it will delete all records form table2 except todays. This is what I have but can't seem to get it to work.

mySQL="Delete FROM table2 Where fldDate >= (now(),-1)"
dbRs.open dbConn
Set delrecords=dbRS.execute(mySQL)
:think:

[amir]

now includes the time as well as the date, wich is why you can't simply take 1 off.

use date()

[dlbdennis]

Still no go. Doesn't work???

[amir]

yea the sql syntax needs # befor and after a date

dd = date()-1
sql = "select ......... and shifts.shift_date =#" & dd & "#"

[dlbdennis]

I'm sorry I guess I'm not understanding the whole string.

mySQL="Delete FROM tblLog Where fldDate >= and shifts.shift_date =#" & dd & "#"

[Terry]

http://programmersresource.com/forum/showthread.php?s=&threadid=178

[tev]

<= :)

[dlbdennis]

http://programmersresource.com/forum/showthread.php?s=&threadid=178

This is so slow it is not functional to me.
How could I speed this up?

[tev]

I'm sorry i dont think i understand , what is too slow?

[dlbdennis]

Here is the code and when I use a database with a password its too slow, but does delete the records. When I use a database without a password it gives this error.
Could not delete from specified tables.

Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open "Database"

dd = date()-1

mySQL="Delete FROM table2 Where fldDate <=#" & dd & "#"
dbconn.execute(mySQL)

dbConn.Close
Set dbConn = Nothing

[Pyros]

Here's how I solved it:

<%
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open "DATABASE"
mySQL="Delete FROM YOUR_TABLE Where YOUR_DATE < DateAdd('d',-7,date())"
dbconn.execute(mySQL)
dbConn.Close
Set dbConn = Nothing
%>

I know this is an old post but I know others will have the same issue as me and it took me ruddy ages to solve this. :wacko: