ABC ~ All 'Bout Computers
The Online Web-azine for Computer Enthusiasts
-- brought to you by

contents page for this issue

This Is the House That Access Builds:
Microsoft Access Database Structure Explained in the Simplest of Terms
~~ by Linda Johnson, Linda's Computer Stop

When I teach Beginner Access classes, I find, because an Access file is setup so differently from other file types, people have a hard time understanding what each part is and that it's all part of one file.  My students are always trying to "save" a table or report as a separate file and just don't understand the structure of a database and the fact that each part is NOT a separate file, but instead a separate "object" within the database file itself.  When you create a database, you only get one file, with an .mdb extension - all the tables, reports, queries, forms, etc. are all inside this one file.

So, I tell my students a database file is like a house, and each group of objects are in separate rooms inside it, and inside each room there is furniture, which are equivalent to the objects themselves .... and the individual records are equivalent to the people sitting in the furniture in the rooms.

Here's a picture of the "floor plan" to make it a bit clearer.

When you first open the database, you see the main database window which shows the objects (rooms) on the left.  I tell my students "When you are in this window, you have just walked in the front door of the house and you are in the hallway but have not yet entered any room". The "hallway", in the picture above, is the center part which says "Database" - this is equivalent to the large white database window you see in Access.  Once you click on any of the "rooms" in Access's object bar on the left side of the database window, you enter that room.

Once inside a room, you see all the objects in there (this is the furniture), but you cannot yet see the records within these objects (the people).  If you click on Tables, you see all the tables in that room.  If you click on Forms, you see all the forms in that room.  And so on.

If you double click on any piece of "furniture" (for example, inside the Tables "room", you will see "furniture" named something like tblCustomers, tblEmployees, etc.), it opens to show you the people, or the individual records included in that object.

But, remember, we are still inside ONE HOUSE, or, in computer terms, one file.

Sometimes, companies have multiple databases, and each database will be an individual "house" (file).  Many companies utilize what are called front-end and back-end databases.  Companies do this to maintain the integrity of their main tables and to keep them safe from users who shouldn't be messing with them.  In this case, there are two databases.  One is the back end that includes these tables and is only accessible by certain people with rights to it.  The other is the front end that includes all the reports, queries, and forms the user needs, and has links to the tables in the back end.  This looks something like this:

So, the users can make a "phone call" to the backend "house" (database) to find out information about the people (records), but they can't change the furniture (tables) in the backend.

If you don't know what each object does (tables, queries, forms, and reports), read James La Borde's ABC articles that explain them clearly.  Once this structure makes sense to you, James's articles will make more sense too.

Just one more scenario I want to cover:

Let's say you create a new database and want to get some information from another database into your new database.  Let's think of this as building a new house and using a moving company to bring some furniture from your old house.

See this picture:

Inside the "hallway" of your new "house" (new database), you can go to the File menu and choose "Get External Data>Import", then select the name of your old "house" (old database) and you will be given a list of all the "furniture" (objects) in the old house that you can import into the new house.  However, the Access "Import Moving Van" doesn't really remove the furniture from the old house - it simply makes copies of the furniture in the new house.

For those of you who are already using databases and familiar with the structure, I'm sure this seems a bit ridiculous to you .... however, when you have a new user and you are trying to explain database structure, this might help.

Remember that many people (both users and techies alike) refer to Access as "Microsoft's Illegitimate Child" because it's so very different from all the other Office programs.  This simple little tutorial just might make all the difference in the world when you are trying to understand or explain how an Access mdb file is set up.

Linda Johnson is a college instructor of all of the Microsoft Office Programs, as well as Adobe PhotoShop and Windows. She also teaches online distance learning classes in Excel, Outlook, PowerPoint, Publisher, and Word at Eclectic Academy. She has worked helpdesk and teaches and lectures at many local businesses and tech schools in her area. Support this newsletter by checking out Linda's eBooks, MS Word MAGIC!, Book I: Fonts, Fun & Formats and Book II: Table Wizardry, How To Get Started As a Software Trainer, and her newest series of MSOffice eBook Tutorials and CD