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

Archiving data

P: n/a
Could some one give me some pointers on basic archiving techniques? I have
developed several databases but never been faced with this issue. Here is
the basic scenario..

Suppose I have the following two tables tblCustomers and tblOrders. I want
to archive all orders over two years old.

Is this how it is done?:
Archiving:
----------
I run a query to move all matching rows from tblOrders in the main.mdb into
the tblOrders in the archive.mdb.
I put the current date in the ArchivePerformed field of tblArchiveDate to
date stamp the event.
Querying archived data:
------------------------
If the following query is run by a user:
SELECT tblOrders.*
FROM tblOrders
WHERE (((tblOrders.OrderDate) Between #1/1/2000# And #1/1/2006#));
Should I only show non-archived results and warn the user that archived data
exists that matches the query?
Should I get clever and pull all the data together into a temp table and do
stuff with it?

Am I way off will how to set up archiving and build a UI for it? How do you
implement and manage archiving?

Paul
Jul 14 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
You talk to the customer and see how they want it implementing.

The point of archiving is to reduce the number of records in the table and
so increase performance, so the likelihood of the customer wanting you to
automatically pull records from the archive is low as this will hit
performance, although they will probably want a method of reviewing or even
restoring archived records.

The usual things you want to do are
Transfer data from the live tales to the archive tables
Delete/Mark as archived the records in the live tables
Update your archived diary.

The best way is to use a transaction so that either it all works or none of
it works.

--

Terry Kreft
"Paul H" <no****@nospam.comwrote in message
news:Q6********************@eclipse.net.uk...
Could some one give me some pointers on basic archiving techniques? I have
developed several databases but never been faced with this issue. Here is
the basic scenario..

Suppose I have the following two tables tblCustomers and tblOrders. I want
to archive all orders over two years old.

Is this how it is done?:
Archiving:
----------
I run a query to move all matching rows from tblOrders in the main.mdb
into
the tblOrders in the archive.mdb.
I put the current date in the ArchivePerformed field of tblArchiveDate to
date stamp the event.
Querying archived data:
------------------------
If the following query is run by a user:
SELECT tblOrders.*
FROM tblOrders
WHERE (((tblOrders.OrderDate) Between #1/1/2000# And #1/1/2006#));
Should I only show non-archived results and warn the user that archived
data
exists that matches the query?
Should I get clever and pull all the data together into a temp table and
do
stuff with it?

Am I way off will how to set up archiving and build a UI for it? How do
you
implement and manage archiving?

Paul


Jul 14 '06 #2

P: n/a
"Paul H" <no****@nospam.comwrote in
news:Q6********************@eclipse.net.uk:
Could some one give me some pointers on basic archiving
techniques?
No, because I've never archived any data in any of my applications.

Why would anyone ever do that? If you have so much data that
Access/Jet chokes on it, then you should migrate to a different back
end.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 14 '06 #3

P: n/a
Have a read of Allen's site about the topic:
http://allenbrowne.com/ser-37.html

Mark

"Paul H" <no****@nospam.comwrote in message
news:Q6********************@eclipse.net.uk...
Could some one give me some pointers on basic archiving techniques? I have
developed several databases but never been faced with this issue. Here is
the basic scenario..

Suppose I have the following two tables tblCustomers and tblOrders. I want
to archive all orders over two years old.

Is this how it is done?:
Archiving:
----------
I run a query to move all matching rows from tblOrders in the main.mdb
into the tblOrders in the archive.mdb.
I put the current date in the ArchivePerformed field of tblArchiveDate to
date stamp the event.
Querying archived data:
------------------------
If the following query is run by a user:
SELECT tblOrders.*
FROM tblOrders
WHERE (((tblOrders.OrderDate) Between #1/1/2000# And #1/1/2006#));
Should I only show non-archived results and warn the user that archived
data exists that matches the query?
Should I get clever and pull all the data together into a temp table and
do stuff with it?

Am I way off will how to set up archiving and build a UI for it? How do
you implement and manage archiving?

Paul

Jul 14 '06 #4

P: n/a
Thanks folks, all very helpful answers...I need to talk to the client now.

Paul
Jul 14 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.