Table too large (1 Viewer)

jedder18

Just Livin the Dream!
Local time
Today, 14:55
Joined
Mar 28, 2012
Messages
135
Have a db with over 25k records....Yes, I know...:eek:
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

AWF VIP
Local time
Today, 21:55
Joined
Feb 1, 2006
Messages
2,396
25k records is quite a small database. So I would not bother archiving unless you have a specific problem.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:55
Joined
Aug 30, 2003
Messages
36,118
+1. I've got lookup tables with more than 25k records. I'll give you a :eek: for 25 million. :p

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

Super Moderator
Staff member
Local time
Tomorrow, 08:55
Joined
Jan 20, 2009
Messages
12,849
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

Just Livin the Dream!
Local time
Today, 14:55
Joined
Mar 28, 2012
Messages
135
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.
 

BeeJayEff

Registered User.
Local time
Today, 14:55
Joined
Sep 10, 2013
Messages
198
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.
 

Users who are viewing this thread

Top Bottom