Archiving old records

[Guest]

Is it possible to archive old records in a database easily. I have read
reports of archiving by simply copying the tables and removing the unwanted
records, however there are way too many links within the database and queries
which save records to different tables in several different databases. I
simply need to archive on particular table with over 30,000 records in it.
The DB has become too large to retrieve any data from it. Thanks in advance
for your aid.

 

[Frank Stone]

hi,
yes it is possible. easily...no. i am involved with that
right now. some of our table have a million + records.
you did not mention data structure so i can only be
general.
you can use copy and delete but i could not and 30k
records is a lot to copy and delete. so what i am doing
involves a append query to append the data out of
the "old" table to the "new" tables based on criteria. I
am using "product family" as a criteria(stuff we don't
make anymore) but you could use something else like date
(?)(<=[somedate])or something else then a delete query to
delete the appended data from the "old table"(main
table). this will not affect any links, update or append
queries you currently have to your "old tables".(it is
not effecting our queries/reports)
you will have to write new queries, reports to access
the "old data" in the newly created table housing
the "old data". You may create the "new tables" to house
the "old data" or do it with make table queries.(i used
both)
i have about 150+ table to purge of "old" data involving
PO's ,SO's, WO's, Part numbers, ect.
so you have my sympathies.
good luck
Frank

 

[Joseph Meehan]

Is it possible to archive old records in a database easily. I have read
reports of archiving by simply copying the tables and removing the
unwanted
records, however there are way too many links within the database and
queries which save records to different tables in several different
databases. I simply need to archive on particular table with over 30,000
records in it. The DB has become too large to retrieve any data from it.
Thanks in advance for your aid.

Interesting. With only 30,000 records Access should not be giving you
any problems, or is it more a matter of trying to match records from that
table and you are getting too many close matches with outdated records.

If the later you may want to add a field for current - non-current
records and use a filter. If the former maybe a good look at your design
might help.

 

[Dirk Goldgar]

Is it possible to archive old records in a database easily. I have
read reports of archiving by simply copying the tables and removing
the unwanted records, however there are way too many links within the
database and queries which save records to different tables in
several different databases. I simply need to archive on particular
table with over 30,000 records in it. The DB has become too large to
retrieve any data from it. Thanks in advance for your aid.

30,000 records isn't a lot. Have you ever compacted this database?

Without actually deleting records from a table, you can add an "Active"
field (or an "InactiveDate" field) use it to flag old records as no
longer active, revising your queries to select only active records.

You can create an archive database and extract selected records into it,
deleting them from the current table after you have done so. What you
say about "way too many links within the database and queries which save
records to different tables in several different databases" implies that
the structure is too complex for me to give you any more specific
advice.

When I've implemented an archiving function for a database, I wrote
fairly elaborate code to identify which records could be archived, and
to archive from the active tables in the proper order so that
referential integrity was maintained.

 

[Arvin Meyer]

Is it possible to archive old records in a database easily. I have read
reports of archiving by simply copying the tables and removing the unwanted
records, however there are way too many links within the database and queries
which save records to different tables in several different databases. I
simply need to archive on particular table with over 30,000 records in it.
The DB has become too large to retrieve any data from it. Thanks in advance
for your aid.

30,000 records isn't very many for an Access database. I generally archive
once a year all records that are more than 3 years old. That typically
leaves about 150,000 records in the main tables and about 45 MB in size. I
generally just copy the entire file and save it with the date appended to
its name. Then I turn on Cascading deletes and delete the records from the
major tables that need to be reduced. After compacting several times, I turn
off the cascading deletes, then import the tables into a brand new empty
database. Then I'm good for another year.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[PC User]

I came across some code to archive a table; however, I haven't tested
it yet. If you're interested, see below. Also I've come across code
to archive the entire db. Let me know if that's what you want and
I'll post it too.

Code:
========================================
The following will create a NEW archive table each time it's run
(based on yearly freq)

To fully automate the procedure it's a good idea to have the names of
the tables prefixed or suffixed with the year of the records (if doing
annually) or change the format type to the frequency of your archive.

Basically you need it to automatically create a descriptive, yet
unique table name each time it's run. The date is the best thing to
use for this.

RUN this from the on click Event and add a msgbox so the user can
cancel

'THIS IS DAO....
'--------------------
'Code start....

Dim strArchiveTable As String
Dim strArchiveDBPath As String
Dim StrYear As String
Dim SQLstr As String
Dim db As dao.Database

Set db =CurrentDB

'Current Year
strYear=Format(Date,"yyyy")

'DESTINATION TABLE
strArchiveTable ="MyTableName" & StrYear

'DESTINATION DB PATH
strArchiveDBPath = "c:\My Folder\MyDatabse.mdb"

DoCmd.Hourglass True
DoCmd.SetWarnings False

SQLstr = "SELECT * FROM MyTableName INTO " _
& strArchiveTable & " IN " & " ' " & strArchiveDBPath & " ' " _
& " FROM MyTableName"

db.Execute SQLstr

DoCmd.Hourglass False
DoCmd.SetWarnings True

msgbox "Archive complete"

'Code End
'------------------

"MyTableName" is the name of the table you want to archive
The table 'MyTableName2003' should appear in your dest db

You will get an error if the table name already exists so you may want
to trap this, however you shouldn't need to run it more than once in a
year
================================

Good Luck,
PC