0

I have a system that receives input from the public each day. Each morning when it starts up I want to run a VB script that moves every input beyond the latest 500 entries into a backup table. This is kind of a complete archive of the systems activity.

I want to move (INSERT row from table 'active' into table 'archive' and then DELETE row from table 'active') every row beyond the initial 500 rows (sorted by column k) from one table to another.

I was hoping to be able to do this as a single SQL statement but haven't had much success. Is there a reasonable way to do this as a single (nested?) Jet SQL statement? Will have to write some intermediate VB Script to handle this action?

Thanks in advance,

Jotham
  • 1,122
  • 1
  • 10
  • 23

2 Answers2

0

Looks like I might have to do something like this.

INSERT INTO ChatArchive (MsgId, MsgText, Filtered, LastFetched) SELECT MsgID, MsgText, Filtered, LastFetched FROM ChatCurrent WHERE ID <= (SELECT MAX(ID) from ChatCurrent) - 500;
DELETE FROM ChatCurrent WHERE MsgId <= (SELECT MAX(MsgId) FROM ChatArchive);

500 here being the number of rows I want to remain in the system. The alternative is to store the list of MsgIds somewhere (in VB) and construct the second query from that.

Jotham
  • 1,122
  • 1
  • 10
  • 23
  • You can only run one statement at a time in MS Access – Fionnuala Feb 03 '10 at 12:40
  • Yeah, but this solution while two statements will be okay, since it's only taking the First items into the queue (queue defined by the sort order and contiguous incrementation of MsgId) rather than the last items. So there shouldn't be any problem with another transaction entering data into the system. – Jotham Feb 03 '10 at 21:17
  • What @Remou was saying is that you'd need to execute SQL statement in sequence, rather than sending both at once. – David-W-Fenton Feb 04 '10 at 01:32
0

Why not just flag the old records in some fashion so they aren't viewable by regular users any more? Using an archive table will be a pain down the road when you want to query data in both tables, etc, etc.

Tony Toews
  • 7,850
  • 1
  • 22
  • 27
  • Yeah it's a good idea, but I can't really alter the shape of the table (i.e. add a flag column) because that would involve changing the code of the software querying it (which would cause a new test cycle to occure...which is outside the business scope of what i'm allowed to do). – Jotham Feb 03 '10 at 11:34
  • You can do what you asked for within scope, but you can't actually fix the flaws you built into the underlying app? – David-W-Fenton Feb 04 '10 at 01:31
  • Right, it would involve another part of the project altering another part of the software. – Jotham Feb 04 '10 at 03:44
  • (What I'm making here is just a VB script to shunt the data into another table periodically) – Jotham Feb 04 '10 at 03:44
  • Also, there will never be querying of both data sets. This is kind of out of scope of the question that was asked (which is how to perform the "move" task). – Jotham Feb 04 '10 at 21:10
  • Jotham, yes, my reply is out of the scope of the original question. However I will always make such comments when I feel someone is asking the question for the wrong reasons. And when I feel they should be doing something different. – Tony Toews Feb 04 '10 at 23:18