Hi Kenneth. Great question.

You certainly can split an application by subject area if those areas are fairly independent. For example, you could have one database for Sales, another for Inventory, and another for Accounting, with each one containing its own tables, forms, queries, and reports. I also have a video called Database Launcher that shows how to create a main menu database that opens other databases, which can work well for that kind of setup. You can even use separate front ends for different roles, such as managers versus accounting staff.

The usual Access approach, however, is a front-end/back-end split. All of the tables go in one back-end database on the server or shared network location, and each user gets their own local copy of the front-end database containing the forms, reports, queries, and VBA code. That is the model Access is designed around for multi-user use, including record locking and keeping everyone's data in sync. It also makes maintenance, deployment, and backups much easier.

If you split by subject, just be careful with information that crosses those subject areas. Customer data, for example, may be needed by Sales, Orders, Support, and Accounting. Duplicating those tables in multiple databases can lead to synchronization headaches very quickly. But then, of course, different front ends for different purposes can also talk to multiple back ends. You could have a database that literally only contains your customer table, and that table can be linked into three or four other front end databases.

So yes, you can absolutely organize it by concept if that suits your needs. But unless the areas are truly separate applications, I would normally recommend the standard front-end/back-end split. That's the beauty of Access - you can build the architecture that best fits what you're trying to do.

LLAP

See also: Split Your Database

Thank you for the help.