OK, here is the sad truth. Splitting something up into multiple tables through Access does not help you. Making the tables smaller works until you need to rejoin the multiple tables to do a global search. If you were going to have, let's say five tables, each holding about 20% of the records, then you have extra overhead when you need to search all of the tables for something and that something is not the table's primary key.
No matter how you split this, Access is essentially single-threaded. Even if you had multiple front-ends on different machines with a shared Access back-end to try to speed up your processing, the file locks might make this not work any faster. In fact, that might make things SLOWER.
The TYPICAL way to speed things up is to assure that you have indexes on your tables. If you have indexes on the fields you are most likely to search for selective values, you will find a tremendous improvement in responsiveness. If your update also depends on one or more of the indexed fields, that query ALSO benefits from the presence of an index.
Splitting and then relinking the tables multiplies your overhead by a serious amount and is likely to cause database bloat because of the temporary lists Access has to create to keep track of what it is doing. You will find a lot of database Compact & Repair operations will be needed because of those splits.
From a programming standpoint, my advice is to read up on Indexes for Access tables and also learn to use database normalization to the greatest extent.
For instance, if your 100,000 records includes lots of department names or common text values, it might be possible to normalize those fields to numbers joined to a secondary table for lookup purposes. If you have department names that can be longer than four characters then you can have a LONG department ID that in your main table would be shorter than the actual department name.
Since you claim limited knowledge of Access, I believe our best bet will be this advice:
1. Read up on database normalization as a way to shrink the size of your main table's records. The shorter the records, the more you can read in a disk buffer and the faster you can scan the records when a scan is needed.
2. Read up on database table indexes as a way to make your searches more efficient. It is possible to do overkill on indexes, but you certainly can have more than one index on the same table - just different fields of the table.
3. Avoid VBA recordset operations like the plague if at all possible. Learn to use SQL as your primary method of dealing with your big table. VBA code is interpreted (or perhaps it is emulating a hypothetical virtual machine, it's hard to say which). SQL processing, however, is mostly compiled code and is more efficient by a huge factor. Like 10-fold or better. Depending on the size of the table and the size of a record and the complexity of the action, 100-fold improvement is not impossible.
