Introduction to Microsoft Access

Microsoft Access is a computer application used to create and manage computer-based databases on desktop computers and/or on connected computers (a network). Microsoft Access can be used for personal information management (PIM), in a small business to organize and manage data, or in an enterprise to communicate with servers. Like any other computer application, in order to use Microsoft Access, you must first install it. After installing Microsoft Access, then you can open it. There are various ways you can open Microsoft Access. It gets launched like the usual products you have probably been using. As such, to start this program, you could click Start -> (All) Programs -> Microsoft Office -> Microsoft Office Access 2010. From now on, that is, for the rest of the lessons, the expression "Microsoft Access" means Microsoft Office Access 2010. If you have a Microsoft Access database such as an E-Mail attachment, a file on a floppy disk, on the network, or in any other means, once you see its icon, you can double-click it. Not only will this action launch Microsoft Access, but also it will open the file. You can also launch Microsoft Access from a shortcut. If you happen to use the software on a regular basis, you can create a shortcut on your desktop or on the Quick Launch area. If you are working on a network of related computers, your database may be located in another computer. In this case the network or database administrator would create a link or shortcut to the drive that is hosting the database. You can then click or double-click this link or shortcut to open the database and, as a result, launch Microsoft Access. Practical Learning: Starting Microsoft Access Start Microsoft Access The Quick Access Toolbar The left side of the title bar displays the Quick Access Toolbar . By default the Quick Access toolbar is equipped with three buttons: Save, Undo, and Redo. On the right side of the Quick Access toolbar, there is the Customize button with a down-pointing arrow. This button is disabled if no database is currently opened. When a database is opened, if you click this button, a menu would appear: The role of this button is to help you decide what buttons to display on the Quick Access toolbar. To add a button to the Quick Access toolbar, click the Customize button and click the desired button from the menu. If the available buttons are not enough or if you don't see a button you want: You can click the More Commands button You can right-click the Quick Access toolbar and click Customize Quick Access Toolbar Any of these actions would open the Access Options dialog box:  To add a command, click it in the middle list and click Add >>. Once you have selected the desired options, click OK. The main or middle area of the top section displays the name of the application: Microsoft Access. On the right side of the title bar, there are three system buttons that allow you to minimize, maximize, restore, or close Microsoft Access.  Under the system buttons, there are two other buttons: Minimize the Ribbon  and Help. The Work Area Under the title bar, Microsoft Access displays an object named the Ribbon. We will come back to it. The Ribbon starts on the left side with the word File, which is selected by default. Under File appears a menu with items such as Save and Open: The middle section is made of two parts. The top part displays the Available Templates label. Under it, there are categories of sample databases. If you click a category, a list of its related sample databases would display To return to the previous window, you can click the Back button . Under Available Templates, there is the Office.com Templates section with 5 icons by default that each represents a category. To select a category, click its corresponding icon. A new page would display one or more sample databases: If you see a sample database you like, click it, give it a name, and click Download. The right side is a column with two parts. The top part is used to show a preview depending on what is selected in the middle section. The bottom side allows you to specify a name for a database and to create or download a database.   The Database as a File A Microsoft Access database is primarily a Windows file. It must have a location, also called a path, which indicates how the file can be retrieved and made available. Although you can create a database on the root directory such as the C: drive, it is usually a good idea to create your files, including your databases, in an easily recognizable folder. When you installed the computer (or when it was installed), the operating system might have created a folder called Documents that provides a convenient place for you to create your files. If various people use the same computer, there is a different Documents folder for each one. When you log in, the computer (the operating system) locates your corresponding Documents folder and makes it available. As a normal computer file, a Microsoft Access database has a file extension. If you are creating a database that follows Microsoft Access 2007 and 2010, the database must have the extension .accdb. When you create a database, if you specify only a name (and path), Microsoft Access would automatically add the .accdb extension. If you want to create a database that is compatible with previous versions, use the extension .mdb but you must explicitly add that extension. An example of a database name would be Exercise.mdb. Designing and Creating a Microsoft Access Database   Introduction Microsoft Access is an application used to create computer databases. It is equipped with all the tools you will need to start a project, to end it, and to distribute it. That's the interface side. The creativity side will come from you. That is, you must define what type of product you want to create, how it will be used, who (what type of user) will use it, and where it will be used. Designing a Database Before creating a database, you should plan and design it. For example, you should define the type of database you want to create. You should create, in writing, a list of the objects it will contain: employees, customers, products, transactions, etc. For each object, you should create a list of the pieces of information the object will need for its functionality: name(s), contact information, profession, etc. You should also justify why the object needs that piece of information. You should also define how the value of that piece of information will be given to the object. As you will see in later lessons, some values are typed, some values must be selected from a preset list, and some values must come from another object. In later lessons, we will see how you can start creating the objects and their content. Creating a Database In our lessons, we will learn different techniques of creating databases. For now, a database is first of all a Windows file. It is mainly created from Microsoft Access. If you have just started Microsoft Access, to create a database, under File, click New. You can then use one of the links in the main (middle) section of the interface: To create a blank database, in the middle section, under Available Templates, click Blank Database To create a database using one of the samples, under Available Templates, click a category from one of the buttons, such  as Sample Templates. Then click the desired buttons: Microsoft Access always suggests a name for the database. You can accept or change it. Use the File Name text box for this purpose. By default, Microsoft Access suggests that the database be created in the Documents folder. If you want it located in another folder, you can click the Browse button . This would open the File New Database dialog box where you can select an existing folder or create a new one using the New Folder button. Once you have specified the name of the database and its location, you can click Create.After specifying the name, click Create. Practical Learning: Creating a Database From a Template If necessary, click File. To create a new database, in the middle section, click Sample Templates Uunder Available Templates, click Marketing Projects In the right section of the screen, set the File Name to Rockville Technologies Click Create Touch Screen Databases Most of the applications we use are provided as graphical objects we access using a mouse and a keyboard. A touch screen application is one that a user uses by touching some objects on the monitor. Touch screen applications have been around for a while. The problem is that, so far, they were expensive and complexe to implement. With the arrival of Microsoft Windows 7, they have become particularly easy. In fact, there no significant thing you must go through to create a touch screen database, just some prerequisite and litle preparation. In order to create a touch screen database, you must first have a touch screen monitor. Their prices have been coming down lately (for these lessons, we were able to purchase an Acer T230H 23-Inch monitor for $340 and it works perfectly) and we expect the price drop to continue. Besides the monitor, the operating system is important. If you are using Microsoft Windows 7, there is not more you need as far as software is concerned. If you are using a previous operating system, you may have to purchase a special monitor and it would come with the necessary software. When creating a touch screen application, there are a few things you should take care of. For example, the objects (Windows controls) of your products should have approprite size. Normally, a user can use a pen or any small object (including a knife if he wants to add some insanity :)) to touch the screen. When it comes to fingers, people have different sizes. For example, if you are creating something to be used by elementary school children, you can use objects that are medium size. If you are creating a database used by adults in a kitchen restaurant or a car repair shop, keep big and oily fingers in mind :). Other than that, experience and creativity will guide you. Database Management   Introduction Unlike some other programming environments, Microsoft Access considers a database as a single Windows file with a name. We will eventually learn that this file can contain various objects that actually make up a database. Therefore, a Microsoft Access database is opened as a normal file. After creating a database, as a computer file, it becomes part of what are known in Microsoft Windows as the most recently used (MRU) documents. When you start Microsoft Access, or if you close the database you are currently working on, the File category displays a list of MRUs. Here is an example: The number of files that the right column can display can be decreased as low as 1 or increased as high as 9. To change this number, in the File section, click Options. In the left frame, click Client Settings. In the right frame and in the Display section, change the value of the Show This Number Of Recent Documents: Database Properties A database holds some internal pieces of information referred to as properties. You can change review and/or change those properties if you want. To access the properties of a database, of course you must first be working on a database. Click File. On the right side, click View and Edit Database Properties Practical Learning: Using Microsoft Access Click File Click View And Edit Database Properties From the Properties dialog, click the General tab. Examine the various sections. Notice the size of the current database Click the Summary tab Make sure the Title is set to Rockville Technologies (otherwise, set it). Click the Subject text box and type Business Regular Activities  Click the Author text box and type Lucien Durand Click the Manager text box and type Christine Lotts Click the Company text box and type Rockville Technologies, Inc. Click the Category text box and type Project Management Click the Keyword text box and type project, budget, management Click the Comments text box and type: This software product is used by the company to manage a project used to build a computer network for a corporate customer. For more information, contact Lucien Durand or Christine Lotts. You can also refer to the web site. Click the Hyperlink Base text box and type http://www.rockvilletechnologies.com Click the Statistics, Contents, and Custom property pages and review their contents The Statistics tab gives you statistics about your database and its summary access The Contents tab shows a list of the components that are part of your database The Custom tab shows, and allows you to customize, the field names associated with your database When you have finished working with the database Properties, click OK (if you click Cancel, any change you have made will be discarded) Opening a Database If you start Microsoft Access just after installing it, the File section would display a normal menu. If you start creating or opening databases, a list of MRUs displays under Close Database. To open a database, if you see its name under File, you can click it. Since a Microsoft Access database is primarily a file, if you see its icon in a file utility such as Windows Explorer, you can double-click it. This would launch Microsoft Access and open the database. If you received a database as an email attachment, you can also open the attachment and consequently open the database file. Closing a Database You can close a database without closing Microsoft Access. To do this, in the File section, click Close Database. Deleting a Database If you have a database you don't need anymore, you can delete it. To delete a database in a file utility such as Windows Explorer: Click the icon of the database to select it and press Delete Right-click the icon and click Delete A warning message would be presented to you to confirm what you want to do. After you have deleted a database, it doesn't disappear from the MRU lists of Microsoft Access. This means that, after a database has been deleted, you may still see it in the File section. If you try opening such a database, you would receive an error. If a database has been deleted and you want to remove it from the MRU lists, you can open the Registry (Start -> Run: regedit, Enter) (be careful with the Registry; when it doubt, don't touch it). Open the following key: HKEY_CURRENT_USER - Software - Microsoft - Office - 14 - Access - File MRU Locate the deleted database and delete its key. The next time you start Microsoft Access, the name of the deleted database would not display in the File section. The Size of a Database A database is primarily a computer file, just like those created with other applications. As such, it occupies space in the computer memory. In some circumstances, you should know how much space a database is using. This can be important when you need to back it up or when it is time to distribute it. Also, when adding and deleting objects from your database, its file can grow or shrink without your direct intervention. Like any other computer file, to know the size of a database, you can right-click it in Windows Explorer or My Computer and click Properties. If you are already using the database, to check its size, you can click File, position the mouse on Manage and click Database Properties. In the Properties dialog box, click General and check the Size label. Practical Learning:  Checking the Size of a Database File From the resources that accompany these lessons, open the Altair Realtors1 database To check its size, click File, and click View and Edit Database Properties In the Properties dialog box, click the General tab and notice the value on the right side of the Size label Click Cancel to close the Properties dialog box Compacting and Repairing a Database As mentioned already, once you have created a database file, it occupies a certain amount of memory space that can grow or shrink without your direct intervention: When you add an object to the database, the database's file grows as needed. When you remove an object, the memory space it was occupying is left empty. This also applies when you keep removing objects: The computer is supposed to recuperate the space those previous objects were using. Unfortunately, that is not always the case. Most of the time, that space is left empty but cannot be accessed by the computer (by other applications). This means that the memory space cannot be made available to other applications. To recover this memory space, you can compact the database. When this is done, the file is shrunk to occupy only the necessary amount of space and free the unused sections: To compact a database, you have many options. To compact and repair the database that is currently opened, and to compact it only once, on the Ribbon: Click File. In the middle section, and click Compact & Repair Database: Click Database Tools and click Compact and Repair Database  The database would be compacted behind the scenes. To compact a database every time you close it, click File and click Options. In the Access Options, in the left list, click Current Database. In the right list, click the Compact on Close check box: And click OK. Practical Learning:  Compacting a Database The Altair Realtors1 database should still be opened. click File then click View and Edit Database Properties Notice the size of the database. Click Cancel Click Compact & Repair Database When the operation has been completed, click View and Edit Database Properties Notice that the database size has been reduced. Click Cancel to close the Properties dialog box. The Ribbon Under the title bar, Microsoft Access displays a long bar called the Ribbon (or Ribbon), whose contens and sections depend on what is currently going on in Microsoft Access: If the Ribbon is taking too much space on your screen, you can reduce its size. To do this, under the system buttons, click the Minimize The Ribbon button . The Ribbon is a long bar made of various tabs, also called property pages. To access a tab: You can click its label or button, such as File, Home, or Create You can press Alt or F10. This would display the access key of each tab: To access a tab, you can press its corresponding letter on the keyboard. For example, when the access keys display, if you press H, the Home tab would display If your mouse has a wheel, you can position the mouse anywhere on the Ribbon, and role the wheel, up or down. If you role the wheel down, the next tab on the right side would be selected. If you role the wheel up, the previous tab on the left would be selected. You can keep rolling the wheel until the desired tab is selected To identify a tab of the Ribbon, we will use the caption on its tab. Most tabs of the Ribbon are divided in various sections, each delimited by visible borders of vertical lines on the left and right. Each section displays a title in its bottom side. In our lessons, we will refer to each section by its title. For example, if the title displays Font, we will call that section, "The Text Formatting section". Some sections of the Ribbon display a button . If you see such a button, you can click it. This would open a dialog box or a window. When Microsoft Access is occupying a big area or the whole area of the monitor, most buttons of the Ribbon appear with text. Sometimes you may need to use only part of the screen. That is, you may need to narrow the Microsoft Access interface. If you do, some of the buttons may display part of their appearance and some would display only an icon. Consider the differences in the following three screenshots: In this case, when you need to access an object, you can still click it or click its arrow. If the item is supposed to have many objects, a new window may appear and display those objects: From this: To this:

 

You can then click Help on This Error to get some information on how to fix the error. Topical Help In various sections of our lessons, we will use an object called the Properties window, which allows you to change the characteristics of an object (a control): Some options of the Properties window could be difficult to figure out, especially if they are not explicit and if you are not familiar with them. Fortunately, to get help for any item of the Properties window, click it and press F1. The Help window would come up and would display one or more options on the topic you had clicked. If you see a link with the same name of the item you clicked, you can click that link and its explanation would display. Online Help Online help is a program that provides help on Microsoft Access. There are two main types of online help: Microsoft Access ships with a help system. To use it, simply press F1 If you have access to a Microsoft Developer Network (MSDN) CD-ROM or DVD, which is the help system provided to programmers who use Microsoft technologies (such as Microsoft Visual Studio), it includes a section on Microsoft Office Practical Learning: Using the Office Assistant Press F1 In the Access Help window and on the left side of Search, type Create Table Click Search On the list that appears, click Create tables in a database On the Access Help window that appears, read the text, scroll down, and click Create A New Table After reading it, on the toolbar of the Access Help window, click the Back button Close the Access Help window Internet Help Although help on the Internet tends to be disparate, it is still the widest form of help available. This is provided in web sites, web pages, newsgroups, support groups, etc. As the publisher of the database environment, it is only natural to refer to Microsoft corporate web site first for help. The Microsoft web site is divided in categories. A web site is dedicated to Microsoft Access at http://www.microsoft.com/access. You can get help at http://support.microsoft.com. Probably the most visited site of Microsoft for developers of all Microsoft products is http://msdn.microsoft.com. This last site provides a tree list that presents items in categories (like the MSDN CD-ROM or a DVD). Microsoft Access Exit Since Microsoft Access shares the same functionality you are probably familiar with from using other applications, you can close it easily. To close Microsoft Access, you can click File and then click Exit Access To close Microsoft Access from its title bar, you can click its Close button  To close Microsoft Access like any regular window of the Microsoft Windows applications, you can press Alt + F4 To close Microsoft Access using mnemonics, you can press Alt, F, X. Practical Learning: Closing Microsoft Access To close Microsoft Access, click the Close button on the top right corner of the window  http://www.functionx.com/access/Lesson01.htm