General discussion

Locked

Archiving in Access

By jsotomayor ·
I have been asked to research the pros and con of archiving an Access application of about 1500 records. These records date back to 1992. The application is multiuser.

This conversation is currently closed to new comments.

11 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Archiving in Access

by Lo In reply to Archiving in Access

Hi - Are you archiving 1500 total, per day/week/month or are there 1500 records total in your database? If your total database is only 1500 it is probably less effort and cost to flag them as 'inactive', modify your software to not allow update (override for administrator or password) and just make good backup with on/off site.

Not sure about actual archiving. You can probably create a copy of your database just structure (copy the database, select all those you DON'T want, delete them, backup the database). Select from the active database those you want to archive, export. Import them into the archive database. Test/check that all is well in archive land. Reselect from active, delete. lo

Collapse -

Archiving in Access

by jsotomayor In reply to Archiving in Access

Poster rated this answer

Collapse -

Archiving in Access

by donq In reply to Archiving in Access

I keep many of my tables small by automating the archiving process using VBA.

Copy the structure to a Table Name you will recognize as archived - whatever.

In a module:
1 declare your database, tables, recordsets, etc. and open a "source" recordset based on the table with 1500 records (I base mine on a query so I can enter constraints to check archiving parameters.

2 open a "source" recordset (or table) and set Target.Field1 = Source.Field1 followed by Field 2, 3, 4, 5 ... whatever.
After each target field in a record is set save your work, delete the archived record, and iterate to the next record until you are done. Link the code to a button so anyone can archive whenever parameters match whatever is specified in your source recordset query.

Collapse -

Archiving in Access

by jsotomayor In reply to Archiving in Access

Poster rated this answer

Collapse -

Archiving in Access

by yatst In reply to Archiving in Access

PROs - I generally keep only two years of inactive records in a database with my active records. AFter two years i ship them off to archive. This makes searches faster and puts relevant data in my reports.

Cons - If a customer/production (?) comes back you either need to locate the information in the archived database and export it to the current or create a new record.

Collapse -

Archiving in Access

by jsotomayor In reply to Archiving in Access

Poster rated this answer

Collapse -

Archiving in Access

by jay_ricketts In reply to Archiving in Access

We've done this, for a relatively low volume application (about 2000 main records, 10000 related ones), a defect tracking system. We set up a form that queries the app and the archives, tells you what projects have been archived, and allows you to select additional projects for archiving.

The archive process copies records to the archive, verifies their presence, then deletes them from the app. It does this for each of the four tables. The archive tables are kept in the same database (sort of - I'll explain further if you need to, but it's not really on topic). This is accomplished mostly with append and delete queries, rather than with code.

We developed a user screen for viewing the archives. It's really just a glorified (and nested) datasheet view, with some built in querying functionality. We didn't just dup the user screens, because the user screens only show you "parts of the elephant", so to speak. Anyone needing to do research needs to see everything, hence the specialized screen.

The biggest downside for us has been the archive interface. Team leaders are okay with the archiving form, but users don't do the archive lookup often enough to remember how, so the dba (moi) gets stuck doing the research - justoften enough to be annoying. We HAVE to have it though - audit trail. Or else we'd have to keep the records in the app and do more filtering - maybe would have been easier, in hindsight. But now that it's in place, it's very low maintenance, runslike clockwork. Knock on wood...

Jay Ricketts

Collapse -

Archiving in Access

by jsotomayor In reply to Archiving in Access

Poster rated this answer

Collapse -

Archiving in Access

by cking In reply to Archiving in Access

If you entire application contains 1500 records, or if you want to archive only 1500 records, one option, is setting up archive table(s)within your existing database. Copy the structure of the original table(s)and query the records into the archive table(s). Then keep regular back ups of the entire database.

We generaly only archive when performance begins to degrade. This is different with each application based on the number of records, user hits and GUI your application maintains.

This improves the performance of the active table(s) and the archived records are still available for reports.

Collapse -

Archiving in Access

by jsotomayor In reply to Archiving in Access

Poster rated this answer

Back to Web Development Forum
11 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums