Unless I am missing something, it seems you are copying and duplicating records, archiving one year to the next. I say it would be easier and more effective, in the long run, to normalize your data structure and not duplicate data.
There is some of that going on because certain queries were massively easier to write if I first extracted the records I needed into a holding table and then ran a bunch of queries against that extract. Then I empty that holding table. The problem I am having now is that I can no longer build that holding table.
I just created a 2nd database with just the monster table and nothing else. It takes up 1.384 Gig. It's a whopper and it will continue growing. So eventually it will get back up to 2 gig. If I do what you suggested and link that new database to the current one (after deleting that same table in the current database and not before doing some reading hahaha), that should buy me about another 6-12 months to come up with a more permanent solution.
I need to learn how to link databases so the queries in my current database can still use that table even though it will be located in another database.
Does it sound like I am on the right track?
Yeah, make a copy of your DB and delete all of the queries, forms, modules, etc.
Place that copy in a share folder where the clients will have full privileges, execute, read, etc. Do a compact and repair of that.
Make another copy of the original DB and place that on your local machine's C drive. Delete all of the Tables. Then, run the Wizard (Linked Table Manager) or maybe External Data for the first time. The idea is to link to an Access accdb. Then do a compact and repair.
That is basically it. Afterwards, consider bringing those tables that are holding temp data into the Front End (FE) file. These are commonly referred to as Temp Tables. You do not delete temp tables. The are simply a structure to import snapshots for your client to work on. That might be a start.
I do not know what your financial situation is. It might be worthwhile to dedicate a machine to Access and install 64 bit Access. The default install is 32 bit. 64 bit allows for larger files sizes.
Ultimately, if you DB is growing exponentially at the rate described, you need to normalize the data.
I split the database in 2, created the linkage, and everything is working fine so far. Thanks for the help.Yeah, make a copy of your DB and delete all of the queries, forms, modules, etc.
Place that copy in a share folder where the clients will have full privileges, execute, read, etc. Do a compact and repair of that.
Make another copy of the original DB and place that on your local machine's C drive. Delete all of the Tables. Then, run the Wizard (Linked Table Manager) or maybe External Data for the first time. The idea is to link to an Access accdb. Then do a compact and repair.
That is basically it. Afterwards, consider bringing those tables that are holding temp data into the Front End (FE) file. These are commonly referred to as Temp Tables. You do not delete temp tables. The are simply a structure to import snapshots for your client to work on. That might be a start.
I do not know what your financial situation is. It might be worthwhile to dedicate a machine to Access and install 64 bit Access. The default install is 32 bit. 64 bit allows for larger files sizes.
Ultimately, if you DB is growing exponentially at the rate described, you need to normalize the data.