Microsoft Access is unique among all of the Microsoft Office applications in that it can be used to create entire applications that contain large storehouses of data, versatile data entry forms and sophisticated reports to present the data in a variety of ways. It also goes beyond other applications such as Microsoft Excel in that a single database is able to store and organize large amounts of data from different sources and on different subjects while enabling many users to easily find and work with that data as needed. While an Excel spreadsheet is excellent for analysis and can provide access to multiple users, Access takes it a few steps further and enables users to store and manage volumes of data and present it in many different ways based on the needs of the data users and the intended distribution.
Why Split a Database?
The multi-user environment provides its own challenges. Access is often used as an ad hoc development tool by people outside the I.T. department to design needed solutions that would otherwise have to be purchased for large amounts of money. These applications can end up being used by many people across a company and that means there can be many people accessing the same database file at the same time. If enough people are in the file at once, the performance of the application can degrade pretty quickly. Also, when one person has the database file open, the file is then read-only for anyone else who opens it which means that they will be unable to create or edit their own reports or database queries.