Table too large

[jedder18]

Have a db with over 25k records....Yes, I know...
anywho...need to archive older records.
I did a make table query to keep it to 2 yr records.
Put the table back with less records keeping same name so I don't have to change all the forms, queries, etc.
Well,, I get to 1 of the forms which contains a subform which contains this table and I am not able to update the table.
After researching,,,I see that the make-table more than likely caused this.
My question...what is the best way to remove all these records without causing data integrity issues for its use elsewhere? It would be close to 19k records....not gonna happen manually

 

[stopher]

25k records is quite a small database. So I would not bother archiving unless you have a specific problem.

 

[pbaldy]

+1. I've got lookup tables with more than 25k records. I'll give you a for 25 million.

That said, if you can't update the form containing the table, something else is wrong. Is the form bound just to the table, or a query containing other tables? That could make it read-only. Is filtering the form so it doesn't contain so many records an option? Perhaps adding an "Active" field or whatever that would exclude the 19k records you'd delete.

 

[Galaxiom]

I see no reason for Make Table to prevent records being added.

Using Make Table is not a good solution anyway because it won't recreate the indexes that should have been on the original table.

I suspect that your real problem is that you don't have indexes on the original table. Without appropriate indexes the performance will be poor which is probably why you are thinking you need to remove records from such a small table..

 

[jedder18]

I got it figured out.
Did an advanced filter and deleted the rows not needed.
It is a large table when you factor in it's being queried on every record numerous times.
It's in process of being revamped.
Not very well designed or user friendly.
Thanks for all the input.

 

[Galaxiom]

It is a large table when you factor in it's being queried on every record numerous times.

No, that is a badly designed query.

 

[BeeJayEff]

I do this sort of thing manually for my UserLog - just copy the table to a new one, delete the most recent month's worth of records from the copy and the older ones from the current table. Then rename the copy as e.g. UserLog1604.