History or archive mechanism in Access

[sunnypat]

I would like to design an archive system for my access database. I have a Customer table that is quite large and is becoming slow to search for a record. We use an Access form to search for records via a Customer ID. I can reduce the size of the table by archiving old records to another table called History. However, I still want access to the historical records from the Access form when the record cannot be found in the Customer table. We could have a history search button that can be clicked to look for the record in the History table and display that in the form. Is there a good way to do this?

[Mariostg]

There is definitely more than one way to do that. I am sure I have seen a thread on this forum.
But I am very curious to find out more as to what you mean by "quite large". Assuming you search on an indexed field, this is normally pretty fast. Maybe the problem lies somewhere else.

Anyway, assuming your customer table has some kind of date field, you could use this to move some of that data to another table or even another database file based on the age of the record.

[NeoPa]

As Mario says, this is almost certainly an indication of a poorly set up database design, rather than a limitation of an Access table. De-normalising the database to handle this would certainly not be recommended unless the circumstances are extreme (and that would require a situation that's outside of my Access experience - and I've been playing with it for a while).

[sunnypat]

I think you guys are right and I will try to compress the database. I still wanted to archive some records to a history table. Would it be possible to have an access form with 2 buttons - one to search Customer database and another to search history table? User would press button 1 first to search and if record is not found, they would click the history button.

[NeoPa]

It's certainly possible, but I'd never advise it myself. If that is proving a problem then you should be looking elsewhere for a solution.

There are a number of possible approaches so, if you're determined to take that approach after all the adverse warnings, you'll need to specify more specifically what you intend.