I have a table in my DB that has grown soo large I would like to split it into two tables. The current table has nearly 24000 records. While it still works; it is definatly slowing down (expecially with a2007!).

The problem is that when I tried to copy the table I got an error "Operation is not supported for this type of object".

I tried to copy/paste another one of the tables in the DB and that works fine; but it only had 100 or so records. Would the number of records cause me to get that error? I can't see any property settings on the table that would prevent me from copy/pasting it...

Thanks

Try doing an append to a new table.

Or actually a make table query from the old table. That will make the structure and put whatever records you have selected in the query into them. Then, use a delete query with the same criteria to delete from the other table.

Although, you really shouldn't need to do this with so few records. This would mean that some other design flaw exists as 24,000 records is childs play compared to the millions of records other users have in their tables. There are probably things that are causing you bottlenecks that have little to do with the table (unless the indexes are not set well).

Ken;
My thoughts were to copy the table; rename it History, then in the original table delete all records older than 4 yrs (it's currently got data back at least 8yrs). I guess I could do that with a append query... Hmmmmmmmmm Only problem is I've always run into errors or things just won't work right when I try to do an append/update/etc query. The only one that will consistantly work for me is a delete query :P Not one I really wanna mess with! LoL

Bob;
I agree with you. I would bet money there's a bottleneck of some sorts here. Like I said; it works, it's just slow. EVERYTHING got slow when Office2007 was installed :( But this table in particular was causing problems.
I do know what it needs... Normalization.. but that would cause such a rewrite of everything I wouldn't know where to begin! :eek:

The easiest thing to do is copy the entire .mdb that has the tables as a back up and in the original delete the old records...

Ken;
Can't really do that. Need to keep the records accessable. If they're in a backup mdb file they're not AS accessable (was about to say aren't accessable, but I'm learning and know a little better now! :) )
I think I need to dig further. I'm in agreement w/Bob on this one. 24000 records is a lot; but it shouldn't be a problem... Will have to work on this some over the weekend and away from work where I can concentrate and actually DIG into the DB a little ;)

I would say the best solution begs the question 'how complex' is your db. For example you could simply add a field that marks the record closed or inactive. Then base all of you forms, etc on the open records. Then to see the closed records you simply flip the criteria. But doing this is a bear if you have to change a lot of stuff.

If you're bogging down at 24k I'm guessing you're trying to manage too many relationships or have a form that's trying to do too much, like too many subforms, combo box lookups, etc....

Just my 2 cents - :)

24000 records really isnt many

depends how big they are, and what you are actually doing with them, though etc - but cant you slice them with a date range or similar to reduce the number.

Slice them with a date range.....
If I understand right, that's what I sort of wanted to do. Put everything older than 3yrs into a history table.
They're not very complex at all. It's an order log that lists all purchase request #'s, part information, and date/time/individual ordering/receiving. That's about it...

A lot of times I have to manually go into this table to search for a specific purchase request. I don't have a form/setup to do that so I just open the table, highlight the request column hit CTRL-F and do a search for it. When I was running Access2003 this would be nearly instantanious. Access2007 seems to sit forever before it finds the correct record (Okay, maybe 5-15sec).

Have you installed the Office 2007 SP1?

Bob;
sorry i've not gotten back here in a while. Had to take some emergency time off to go back home to be with my daughter. She had to have her galbladder removed :( All went well and i'm back at the grind...

To answer your question; I am not sure if SP1 has been installed. I don't have any control over that what so ever. My network people have STRICT STRICT control over what I can/cannot do. They manage all the upgrades/installs for our systems :( I will check with them though. Do you think a SP would fix this problem?

Access 2007 SP1 fixes several performance issues.