Access is a tool for managing databases carefully structured catalogs of information (or data). Databases can store just about any type of information, including numbers, pages of text, and pictures. Databases also range wildly in size they can handle everything from your list of family phone numbers to a ginormous product catalog for Aunt Ethel Discount Button Boutique.
MS Access is not just a database but also provides fully features functionality for development of Forms, Reports, Macros, Modules etc so that information in the database can be presented in better way. Access is the only database which provides in build presentation features. The presentation features are so refined that there are many features like sub-forms, auto-creation of forms etc are missing in the today environments like Visual Studio 2010 and Java. That is why; MS Access is the king of small businesses because of its legendary powers of customization. Though you can use virtually any database product to create a list of customer orders, only Access makes it easy to build a full user interface for that database.
Designing your database
This task involves creating tables to hold data, queries that can ferret out important pieces of information, forms that make it easy to enter information, and reports that produce attractive printouts.
Dealing with data
This task involves adding new information to the database, updating what is there, or just searching for the details you need. To do this work, you use the tables, queries, forms, and reports that you have already built.
Access is not the only Office product that can deal with lists and tables of information. Microsoft Excel also includes features for creating and managing lists. So what is the difference?
Although Excel is perfectly good for small, simple amounts of information; it just cannot handle the same quantity and complexity of information as Access. Excel also falters if you need to maintain multiple lists with related information (for example, if you want to track a list of your business customers and a list of the orders they have made). Excel forces you to completely separate these lists, which makes it harder to analyze your data and introduces the possibility of inconsistent information. Access lets you set up strict links between tables, which prevents these problems.
Access also provides all sorts of features that do not have any parallel in the spreadsheet world, such as the ability to create customized search routines, design fine-tuned forms for data entry, and print a variety of snazzy reports.
Of course, all this is not to say that Access is better than Excel. In fact, in many cases you might want Excel to partner up with Access. Excel shines when crunching reams of numbers to create graphs, generate statistics, or predict trends. Many organizations use Access to store and manage information, and then export a portion of that information to an Excel spreadsheet whenever they need to analyze it.
Microsoft provides another database product; the industrial-strength SQL Server, which powers everything from Microsoft own search engine to the NASDAQ stock exchange. Clearly, SQL Server is big business, and many Access fans wonder how their favorite database software compares.
One of the most important differences between Access and database products like SQL Server is that Access is a client-side database. In non-techie terms, that means that Access runs right on your personal computer. Database engines like SQL Server are server-based: They store the data on a high-powered server computer, which you access from a garden variety PC. (This interaction happens over a local network.)
Server-based databases are much more complex to set up and maintain, but they provide enhanced performance and rock-solid stability, even when thousands of people use them at once. However, the only people that require high-end databases like SQL Server are large organizations. Amazon.com would not last 5 minutes if it had to rely on an Access database. But Access works just fine for most small and mid-sized businesses. It is also perfect for personal use.
Another important difference between Access and server-side database products is that Access is an all in one solution for storing and interacting with data. Server side database engines like SQL Server focus exclusively on storing data (and sending that data to other computers when they request it). However, this single-minded design has a sizable price. An ordinary person can not directly edit a database that is stored by SQL Server. Instead, you need to use yet another program that can talk to SQL Server and ask for the information it needs. In most cases, this program needs to be hand-built by a savvy programmer. In other words, if you are using SQL Server, you need to write a whole application before you can effectively use your database.
The best option for mid-sized to large-sized project is to use Access as a front end and SQL Server as backend. This way power of SQL Server in managing and storing huge data and presentation skills and quick programming of MS Access can be utilized.