Link to home
Start Free TrialLog in
Avatar of isnoend2001
isnoend2001Flag for United States of America

asked on

delete records in access database > 3 days from VB6

I have an online database that records visiters per hour and day as well as most popular pages. After collecting info for a couple months it's getting too huge.
How can i delete records more than 3 days old thru vb.
This is what i have tried with no success:
Option Explicit
Dim db As Connection
Dim WithEvents adoPrimaryRS As Recordset

cmdDelete_Click()
 Set db = New Connection  
  db.CursorLocation = adUseClient  
   db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Counters.mdb;"
 
'db.Execute " DELETE * FROM tblCounter WHERE Date < DATEADD(""d"", -3, NOW()) "
end sub
What am i doing wrong
ASKER CERTIFIED SOLUTION
Avatar of rheitzman
rheitzman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Note that 'Date' may be a reserved word so you may need [ ] :

" DELETE * FROM tblCounter WHERE [Date] < #" & DATEADD("d", -3, NOW()) & "#"
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of isnoend2001

ASKER

Thanks guys i tried all 3 and none deletes records.
Date is in this format 5/18/2008
Made sure db was closed and path was correct
Thanks guys I screwed up
I was trying to delete from counters and it should have been counter
with out a "s"
I'm putting me on time-out


try this one...remove the (*)

" DELETE  FROM tblCounter WHERE [Date] < '" & Format$(DATEADD("d", -3, NOW()), "m/dd/yyyy") & "'"
Doesn't work:
" DELETE  FROM tblCounter WHERE [Date] < '" & Format$(DATEADD("d", -3, NOW()), "m/dd/yyyy") & "'"
does work:
 " DELETE * FROM tblCounter WHERE [Date] < #" & DateAdd("d", -3, Now()) & "#"