Not sure I get that (maybe it's the terminology). You don't post data to lookup tables beyond the initial static data you want to "look up". Sure, you add additional lookup values, but this isn't something you do on a regular basis. Then there is the "possible accounts was so numerous". How many is that - 100,000? Even that should not matter. Those and the fact that you now have multiple tables for the same type of entity suggests you have lack of normalization issues. VBA will not solve design flaws, if that's where you are at.
Lookup tables are typically for types of things (entities) wherein their primary use is to restrict the input of types in a record to those that are in the table.
At the minimum you might need tables for accounts, types of accounts and one for transactions (unless you also want one for transaction types). The concept is to relate all of those as pairs by having properly designed common fields between them.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.