I was wondering if you can run a delete query in one database, and have the action take place in another database.
Namely, do something like
Delete (fields) IN (Database location) FROM (specify Table) WHERE (criteria is met)
Now, the reason i ask is a problem was set to me, and i really didn't have much say in the design aspect. but here goes
i have a main database where records and tables need to be deleted (archived) every 30 days. this is to keep the size small while keeping the most recent and neccessary data.
when the data is archived, it is sent to another database, archive. now, this data must be kept for 90 days, and by then, it is no longer of use, and should be deleted. i know this sounds stupid by moving and deleting all the time, but it's what is asked. if it can't be done, say so. please refrain from suggesting alternatives, just say it can't be done.
Here's the deal with the main db. when the program starts, a form loads that oes through the database, and deterines what tables are old enough to be deleted. By use of a make query and the IN clause, the table is then made and populated in the archive db. the program then goes and determines what records in the "main table" are to be deleted, then amends the "main table" in the archive db with the old records. after that is complete, all old tables are deleted from the main db and then all old records in the main table are deleted.
the database program continues and all is good.
so i ask you, is it possible to do the DELETE IN FROM WHERE method? or what?
thanks everyone