best way to archive old records?

[patrichek]

Hi all,
I'd like ideas on how to archive records older than 1 year but still make them accessible. We are having speed issues. I thought about making an empty copy of my database and starting from scratch but thought there must be a better way than having 2 databases (one archive and one new).

by the way, i have a FE/BE setup with up to 80 users accessing at once.

thanks!

 

[Ed2020]

It depends somewhat on the design of your database but I'd do something like the following:

1. Create copies of the tables you want to archive in an Archive.mdb and rename them to something meaningful. Link into frontend database.

2. Append data you want to archive into the copy tables and delete from live tables.

3. On all forms, reports, etc. add an option group that allows the user to chosse between live and archived records. This option group will run code to change the record source of the form/report.

Obviously this could well end up being a lot of work. I'd make sure I'd tried all the quicker options before going down this route...

Ed Metcalfe.

Please do not feed the trolls.....

 

[rac2]

Isnt 80 users is a substantial load on an Access database? Is Access the back-end database?

Table size does not seem like the most likely cause of performance problems. Have you looked at the indexes on frequently used search and retrieval criterion columns?

In any case archive and destruction of data are normal aspects of designing an application. We usually leave that to last, especially with the huge storage capacity of computers today. But the need will always be there.

What about defining history tables within the one database? This might be more convenient when you need to report on a timeperiod that spans the current and history dates. And the supporting tables of codes and price lookups, etc. will be available and you wont have to think about maintaining that kind of relatively stable data in two places. But linking databases might work just as well, I dont know.

You may need different schedules or timeframes for archiving different kinds of data, a customer table is more stable than a transaction table. For example, year old sales is pretty stale but a customer may return after three years. An archived customer might need to be moved back into the active table, whereas an old sale is of no use except in reporting.

In other words, the duplicate database would work, but might have a lot of unused overhead that would be avoided with archival tables in the one database. The management issues cannot be avoided in either approach.


I like the suggestions posted by Ed.

 

[Remou]

I wonder are you using ADO to pull single records or small groups for editing etc?

 

[patrichek]

Hi Guys,
thanks for the responses. This is not an ADO project, otherwise I think we'd be in better shape. At this point I'm not sure how to proceed, I'm considering putting this database on its own subnet and its own server. I'm thinking network traffic may be causing problems along with the size of the db.

 

[Remou]

It is not necessary to have an ADO project to use ADO to get records. It is possible to attach an ADO recordset to a form.

Another point - as far as I recall, long path names can cause slow-ups in the database.

 

[patrichek]

Remou,
how is what you speak of possible? Where can i find info on ADO recordsets attached to a form?

thanks!

 

[Remou]

One How To, and one possible problem:

How to bind Microsoft Access forms to ADO recordsets

http://support.microsoft.com/kb/281998/EN-US/

ACC2000: Forms Based on ADO Recordsets Are Read-Only

http://support.microsoft.com/kb/227053/EN-US/