The idea presented by theDBguy is commonplace, particularly when you are working with very large databases. If you have a big amount of data to manage but only temporarily, you can create another DB. Any one of several possible methods apply.
I'll explain the other part of your question regarding one of our favorite (?) topics, BLOAT. The issue that you have discovered for yourself is that items don't get deleted immediately when you delete them. SO if you delete a big table, what ACTUALLY happens is that the table gets marked for deletion but (on the odd chance that someone was sharing that table at the moment) it is not actually deleted. This deferral means that you have to run garbage collection.
To do that, you need to run the Compact & Repair option on your database. However, if you are actually IN that database at the time, if you are not the ONLY user thereof, you cannot do that because the file is locked by others and you can't yank it out from under them while they are still working.
This garbage collection has been the bane of computer systems since I have been in the computer business - and my career has spanned over 40 years of actual computer work at all levels, plus a college career that, including grad school, spanned another 8 years. I figure that in the last 50 years, if someone hasn't solved the garbage collection problem, it might not be easily solvable. There ARE some tricks to play but they don't always work. Have you ever wondered why you need to reboot a Windows system after it has been running for several days? Garbage collection! A reboot is like a street sweeper, it gets rid of everything lying around inside.
Here's the problem. It is hard to find this level of info, but if you look hard enough, you can find it. Access files are internally built on lists of things. Every collection you have within Access is based off a list of pointers to objects in that collection. The database as a whole has collections of tables, query definitions, forms, reports, macros, and modules. Each table has a collection of record definitions, index definitions, indexes, and member records. Each form or report has a collection of controls, and so on.
When you delete a collection member (and trust me, you are ALWAYS deleting a collection member when you delete something), the other items in the list don't move, so what you have now is the space left behind when you deleted the items. You mark the objects you deleted and you mark the pointer to it. But because tables stay open until the last person closes it, deleting a table merely means "marked for delete" and any active users can still see things. (E.g. have you ever opened a datasheet, then deleted the underlying data separately? Did you see "#Deleted" pop up in the cells of the sheet?)
The reason you can't move things around is because your other users might have other objects open and THOSE objects can't be moved either. So what really happens is that your database's memory space becomes a Swiss cheese with holes in it.
The C&R operation can't actually move things around either, but it CAN make a new empty copy of the database file and then one by one, copy "live" elements from the old file into the new one. This resets the pointers when it moves the data. And the C&R does NOT copy deleted items. So that operation collects the garbage until the next time you need to do the C&R.
For small amounts of deletion and a few other operations, this is not a big deal. But where you ran into the brick wall was that deleting a huge table can leave a huge gap in your address space and leave it occupied by deleted data.
The beauty of a temporary DB as suggested by theDBguy is that you can just delete and rebuild the temporary DB on the fly. Several methods have been described in this forum.
