473,734 Members | 2,567 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Archiving data

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 ArchivePerforme d 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.Or derDate) 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
4 2517
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******** ************@ec lipse.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 ArchivePerforme d 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.Or derDate) 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
"Paul H" <no****@nospam. comwrote in
news:Q6******** ************@ec lipse.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
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******** ************@ec lipse.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 ArchivePerforme d 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.Or derDate) 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
Thanks folks, all very helpful answers...I need to talk to the client now.

Paul
Jul 14 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
3411
by: SunSmile | last post by:
Hi, I am logging my exceptions to a word document(*.doc). After the size of word document is 5KB. I am archiving the word document to *.doc.1 Here when I am archiving the word document to *.doc.1, the new line characters("\r\n") are lost and the *.doc.1 document is instead displaying a null square space. I am using Log4Net to log the exceptions.
1
4836
by: Sally Ruggero | last post by:
I need advice on archiving data from our production database. Each night I would like to save and remove a day's data from two weeks ago, from all the tables. I know how to delete the data--though our schema does not specify cascaded deletes. However, I can't figure out how to save the desired data. I'd like to save it in SQL insert statement format, but I can't see how to get the data out. If I use psql to create a temp table with the old...
4
2420
by: Geoff | last post by:
I want to archive records from one recordset to another recordset. Not too sure of what to do in commented areas shown below. Dim Db As DAO.Database Dim Rec1 As DAO.Recordset 'Set as Global Variables Dim Rec2 As DAO.Recordset Public Sub OpenRecSet() Set Db = CurrentDb() Set Rec2 = Db.OpenRecordset("Tbl_Archive2004_5", dbOpenDynaset)
1
2186
by: sandip | last post by:
Hi All, Can someone please help me with good and easy-to-use data archiving tools for DB2 database? Does anyone have previous experience with IBM DB2 Data Archive Expert tool? Is this a widely used tool for archiving? Any info in this matter would be highly appreciated.
3
4886
bvdet
by: bvdet | last post by:
Following is an example that may provide a solution to you: """ Function makeArchive is a wrapper for the Python class zipfile.ZipFile 'fileList' is a list of file names - full path each name 'archive' is the file name for the archive with a full path """ import zipfile, os def makeArchive(fileList, archive):
4
1378
by: mforema | last post by:
Hi Everyone, I have a database that currently has two types of tables: one type has all of the current data, and the other type has all of the previous, outdated data. I already have command buttons on my form that opens a previous data query, so that the user can view the outdated data if he/she needs to. However, the database may need to grow to allow a way for users to view various versions of the data. People at my work agree that...
4
7860
by: eliane | last post by:
For some reason, DB2 is taking a long time to start archiving the logs. Pls, do you know what could be causing it? I see the following msgs on db2diag.log 2008-09-04-00.36.24.414103+000 I415281A322 LEVEL: Warning PID : 1585198 TID : 1 PROC : db2loggr (SGPPBWG2) 0 INSTANCE: ucsdb2 NODE : 000 DB : SGPPBWG2 FUNCTION: DB2 UDB, data protection, sqlpghck, probe:1780
1
1818
by: cleary1981 | last post by:
Hi, I have created a database that logs realtime data and stores 1440 records per day in my database. This system must log data 24hrs a day and always running. My fear now is that my db will eventually get to large. What are my options as regards archiving my data? Not sure if it relevant but I need to keep data for up to two years.
3
1653
nurikoAnna
by: nurikoAnna | last post by:
how to data archiving? I need you help I have no idea how to start coding data archiving . Please help
0
8946
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8776
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9310
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9182
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8186
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6031
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4550
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3261
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2724
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.