Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-26-2016, 11:19 AM   #1
jedder18
Just Livin the Dream!
 
Join Date: Mar 2012
Posts: 115
Thanks: 27
Thanked 9 Times in 9 Posts
jedder18 is on a distinguished road
Table too large

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

jedder18 is offline   Reply With Quote
Old 04-26-2016, 11:23 AM   #2
stopher
AWF VIP
 
stopher's Avatar
 
Join Date: Feb 2006
Location: Southampton, UK
Posts: 2,396
Thanks: 20
Thanked 311 Times in 292 Posts
stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough
Re: Table too large

25k records is quite a small database. So I would not bother archiving unless you have a specific problem.
stopher is offline   Reply With Quote
Old 04-26-2016, 12:03 PM   #3
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,070
Thanks: 10
Thanked 3,870 Times in 3,813 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Table too large

+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.

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 04-26-2016, 03:19 PM   #4
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,288
Thanks: 78
Thanked 1,409 Times in 1,329 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Table too large

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..
Galaxiom is offline   Reply With Quote
Old 04-27-2016, 03:50 AM   #5
jedder18
Just Livin the Dream!
 
Join Date: Mar 2012
Posts: 115
Thanks: 27
Thanked 9 Times in 9 Posts
jedder18 is on a distinguished road
Re: Table too large

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.
jedder18 is offline   Reply With Quote
Old 04-27-2016, 02:18 PM   #6
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,288
Thanks: 78
Thanked 1,409 Times in 1,329 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Table too large

Quote:
Originally Posted by jedder18 View Post
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.
Galaxiom is offline   Reply With Quote
Old 05-10-2016, 03:44 AM   #7
BeeJayEff
Newly Registered User
 
Join Date: Sep 2013
Location: Somerset, UK
Posts: 198
Thanks: 37
Thanked 10 Times in 10 Posts
BeeJayEff is on a distinguished road
Re: Table too large

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.


BeeJayEff is offline   Reply With Quote
Reply

Tags
archive , delete

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] One Large Table Problem Jaebyrd240 Tables 17 03-31-2015 06:05 AM
Bypassing 255 field limit in table and forms by splitting out large table into two? joe789 Tables 5 06-29-2012 05:07 AM
Large Table Birdman895 Tables 2 05-01-2012 11:21 AM
Large Recordset, Large Search Criteria StevenAFC Modules & VBA 7 12-10-2008 05:15 AM
One large table? Or two different ones? RexesOperator General 2 01-15-2007 08:31 AM




All times are GMT -8. The time now is 06:42 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World