Can I plan for a split Access database?

[cmo187265]

Introduction:
I am creating a sport card database to record inventory and transactions. I have a few good posts on this forum if you are able to search by my username if it helps understand better.

Issue:
I anticipate, of the many normalized tables, 3 tables will continue to grow through the life of the database I and understand that Access has a resource limit of 2GB. I anticipate for example a table TBL_Inventory and a table TBL_Transaction s to grow indefinitely. I also expect that tables TBL_Player and TBL_Teams, for example, will continue to grow as new players enter their respective sports leagues and as expansion teams are added to the leagues as the leagues continue to grow.

Question:
What is the best practice to accommodate the finite resource issues of Access? (i.e. 1 Front End and Multiple Backends?). As an extension of the question above, is it possible or recommended to create multiple databases upfront to accommodate the growing tables? I ask because I can find very little explaining how to do this as most articles (through exhaustive searching) only explain how to split EXISTING databases and do not explain how to develop multiple databases upfront.

Additional Info:
I am likely to be the only user of this database.

Thanks in advance for anyone who answers!

[isladogs]

The 2 GB limit is a lot of data. That is, providing you aren't using attachment fields which will cause database bloat, and are not repeatedly adding and deleting tables.
From your description, I would expect a single backed to be perfectly satisfactory for many years. However if the BE gets much above 1 GB, start planning your next move. Rather than split the data into several BEs (which may cause performance issues if you need to run queries on data from different BEs), you might then be better planning to upsize to SQL Server Express which has a size limit of 10 GB and is free.

[twinnyfo]

I am with isladogs, here. I have tons of data after 13 years working on military promotions data and we have barely 50MB of data. If you compact your BE frequently (as well as making frequent backups), your size limit should be fine. If you have attachment fields, then I would recommend 1) not having attachment fields and 2) storing those attachments in a separate folder, with the DB holding a reference to the file path\file name.

Hope that hepps!