By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,028 Members | 1,733 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,028 IT Pros & Developers. It's quick & easy.

Delete query

P: 29
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
Sep 19 '06 #1
Share this Question
Share on Google+
1 Reply

P: 29
how about this??

Expand|Select|Wrap|Line Numbers
  1. DELETE tblCoils.CoilID, tblCoils.Date, tblCoils.Customer, tblCoils.Alloy IN '\\DBserveer\\backup.mdb'
  2. FROM tblCoils
  3. WHERE (((tblCoils.Date)<(Date()-90)));
the table names and field name are the same.
Sep 19 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.