Delete records after 1 year automatically

[Dawg62]

I'm trying to delete records that are older than one year through a stored procedure but haven't been able to do yet. Anyone have a sample script that will do that? I have tried a few that work on tables that are not connected to anything and they work but not on connected tables that are updated with info regularly. Remeber this is a .adp access project with SQL as the back end. TIA

 

[genomon]

If your table has a datetime field (record create date) you can use the Datediff function:
DELETE FROM tablename WHERE DATEDIFF(month, createdate, GETDATE()) > 12.

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[Dawg62]

I tried the script but it didn't delete anything. At the createdate I replaced it with the colun name SDT. It uses a smalldate time field.

 

[cmmrfrds]

There is not a lot of info. to go on. It sounds like the records are not deleted because of constraints.

In the table with the record you are trying to delete, show which columns have constraints. List the field names and the relationship (direction) of the constraints to other tables.

 

[Dawg62]

Well I got it to work on test tables with no problems. The problem I am having now is that it won't delete the rscords in the table I want. When I try to delte the rows manually it gives me this error: Cannot remove last status entry. Status is the name of the table. TIA