First, you are not in trouble until a single table reaches 1 Gb, because at that point, an UPDATE that modifies every record will probably crash.
I frequently had tables with tens of thousands and even hundreds of thousands of records, for which serious normalization allowed me to compress the heck out of what I was storing. As I recall, I had maybe 25-30 fields in the main table and a few descriptive (one/many) tables had more fields than that. The down side of serious normalization is that to get an "aggregate" record, I had five-way or more JOIN queries.
The tricks you play to make this work are simple, though.
1. Split the BE into two or three files. EACH can be up to 2 GB, but typically you would split at the point where a file became over 1 GB.
2. Archive things to external files in another format. For instance, I never kept more than six months of event logs online. Once per month I archived the event logs to a text file, after which I erased the records followed by a Compact & Repair. This app also had records that could reach a state of "all required actions complete" and once those records exceeded 90 days old, we archived them to a text report as well. (The times were based on probable need for reports reaching back to some actions.)
3. We moved some temporary tables out of the back end and into the front end, and used the "copy new FE before launch" method to get rid of bloat.
Eventually, if you have records that go back far enough, you will reach a point where they can be archived. If so, your app will reach an "equilibrium" state and you will know just how close you are to the ragged edge of ruin. However, I've never personally brought any app to its knees on size concerns. (Did, however, have speed issues now and then.)
More likely than not, you will find that size won't be the driving force to make you move away from Access. It will be the need to have a dedicated BE active SQL type of server so that you can send pass-thru queries and have the results processed on the server. As long as you are using native Access and a split FE/BE configuration, you take more time with network delays than anything else. Having an active server as a BE cuts back on network load BIG-time.