How do I create a Macro to Purge information in an Access 2000 Table 1

[oopa]

I am using Access 2000 as a backend database which stores customer information for online purchases.

I want to create a macro, which will autoexec, to purge all information in the Orders Table older than 72 hours, each day.

The Orders table contains 46 fields.

I've tried search engines for answers and getting nowhere.

Jim

 

[genomon]

Well as far as I know, the macro could only be executed from the backend mdb; check out the FAQs as I believe I recall one that addressed the issue of exe'ing a macro from a remote database....

Alcohol and calculus don't mix, so don't drink and derive.

 

[oopa]

Yes, the IP claims he can .exe the macro on the server side as long as I create the Macro.

Jim

 

[JerryKlmns]

DELETE O.*
FROM Orders
WHERE DateDiff("h", O.DateRequested, Now())<72;

This SQL statement deletes all the records exceeding 72h of request. Save it as a query and make a macro that runs this query

 

[oopa]

Our cart system is written is ASP, as a backend to Adobe GoLive, hosted on a Windows 2003 server. The age of the Order recorded is by mm/dd/yyyy.

So, can I use:

DELETE * FROM Orders WHERE [MyOrderDate]<Date()-3

 

[JerryKlmns]

I am using Access 2000 as a backend database

In MS-Access

DELETE Orders.* FROM Orders WHERE [MyOrderDate]<Date()-3

is valid. But 72h before now is not always equal to 3 days before now. If you check for hours, you'll have to run it hourly instead of daily

 

[oopa]

Thanks, I'll see if this will work.

I may just run the query once a week instead of daily.

Jim