Field List gives access to in Excel
Chapter1.Creating Your First DatabaseAlthough Microsoft wont admit it, Access can be intimidatingintimidating enough to trigger a cold sweat in the most confident office worker. Even though Microsoft has spent millions of dollars making Access easier to use, most people still see it as the most complicated Office program on the block. Theyre probably right. Show
Access seems more daunting than any other Office program because of the way that databases work. Quite simply, databases need strict rules. Other programs arent as obsessive. For example, you can fire up Word, and start typing a letter straight away. Or you can start Excel, and launch right into a financial report. But Access isnt nearly as freewheeling. Before you can enter a stitch of information into an Access database, you need to create that databases structure. And even after youve defined that structure, youll probably want to spend more time creating other useful tools, like handy search routines and friendly forms that you can use to simplify data lookup and data entry. All of this setup takes effort and a good understanding of how databases work. In this chapter, youll conquer any Access resistance you have, and learn to create a simple but functional database. Along the way, youll get acquainted with the slick Access user interface, and youll learn exactly what you can store in a database. Youll then be ready to tackle the fine art of database design, which is covered in detail throughout this book. Understanding Access DatabasesAs you already know, a database is a collection of information. In Access, every database is stored in a single file. That file contains database objects, which are the components of a database. Database objects are the main players in an Access database. Altogether, you have six different types of database objects:
Access gurus refer to all these database ingredients as objects because you manage them all in essentially the same way. If you want to use a particular object, you add it to your database, give it a name, and then fine-tune it. Later on, you can view your objects, rename them, or delete ones you dont want anymore. NoteDesigning a database is the process of adding and configuring database objects. For those keeping score, an Access database can hold up to 32,768 separate objects. In this chapter, youll consider only the most fundamental type of database object: tables. But first, you need to create a blank database you can work with. When you start Access, you begin at the welcome page. From there, youre just a few clicks away from generating a database of your very own. In this chapter, youll slap together a fairly straightforward database. This example is designed to store a list of prized bobblehead dolls. (For those not in the know, a bobblehead doll is a toy figure with an oversized head on a spring, hence the signature bobbling motion. Bobblehead dolls usually resemble a famous celebrity, politician, athlete, or fictional character.) Heres how to create a blank new database:
Once you create or open a database, the Access window changes quite a bit. An impressive-looking toolbar (the ribbon) appears at the top of your screen, and a Navigation Pane shows up on the left. Youre now in the control center where youll perform all your database tasks (Figure1-4). Figure1-4.The navigation pane on the left lets you see different items (or objects) in your database. You can use the navigation pane to jump from a list of products to a list of customers and back again. The ribbon along the top groups together every Access command. This ribbon is the mission control that lets you perform various tasks with your database. The document window in the middle takes up the rest of the window. This window is where youll do your work, such as designing tables and entering data. If you havent used the ribbon before (either in Access or in another Office program), the Introduction covers the basics of how the ribbon works. Otherwise, carry on to the next section, where youll learn how to add a table to your brand-new, empty database. Building Your First TableTables are information containers. Every database needs at least one tablewithout it, you cant store any data. In a simple database, like the Bobblehead database, a single table (which well call Dolls) is enough. But if you find yourself wanting to store several lists of related information, you need more than one table. In the database BigBudgetWedding.accdb, you may want to keep track of the guests that you invited to your wedding, the gifts that you requested, and the loot that you actually received. In Chapter5, youll see plenty of examples of databases that use multiple tables. Figure1-5 shows a sample table. Figure1-5.In a table, each record occupies a separate row. Each field is represented by a separate column. In this table, its clear that youve added five bobblehead dolls. Youre storing information for each doll in five fields (ID, Character, Manufacturer, PurchasePrice, and DateAcquired). Before you start designing this table, you need to know some very basic rules:
When you first create a database, its almost empty. But to get you started, Access creates your first database objecta table named Table1. The problem is, this table begins life completely blank, with no defined fields (and no data). If you followed the steps in the previous section to create a new database, youre already at the Datasheet view (Figure1-5), which is where you enter data into a table. All you need to do is customize this table so that it meets your needs. You can customize a table in two ways:
The following steps show you how to turn a blank new table (like Table1) into the Dolls table by using the Datasheet view:
NoteTechnically, you dont need to save your table right away. Access prompts you to save it when you close the datasheet (by clicking the X at the document windows top-right corner), or when you close Access. As you can see, creating a simple table in Access is almost as easy as laying out information in Excel or Word. If youre itching to try again, you can create another table in your database by choosing CreateTablesTable from the ribbon. But before you get to that stage, it makes sense to take a closer look at how you edit your table. You now have a fully functioning (albeit simple) database, complete with one table, which in turn contains one record. Your next step is filling your table with useful information. This often-tedious process is data entry. To fill the Dolls table, you use the same datasheet you used to define the table. You can perform three basic tasks:
Youll probably spend a lot of time working with the datasheet. So settle in. To make your life easier, it helps to understand a few details. As you already know, you can use the arrow keys to move from field to field or row to row. However, you may have a bit of trouble editing a value. When you start typing, Access erases any existing content. To change this behavior, you need to switch into Edit mode by pressing F2; in Edit mode, your typing doesnt delete the stuff thats already in that field. Instead, you get to change or add to it. To switch out of Edit mode, you press F2 again. Figure1-10 shows a close-up look at the difference. Figure1-10.Top: Normal mode. If you start typing now, youll immediately erase the existing text (Hobergarten). The fact that all the text in the field is selected is a big clue that youre about to wipe it out. Bottom: Edit mode. The cursor shows where youre currently positioned in the current field. If you start typing now, youll insert text in between Hober and garten. Edit mode also affects how the arrow keys work. In Edit mode, the arrow keys move through the current field. For example, to move to the next cell, you need to move all the way to the end of the current text, and then press the right arrow key again. But in Normal mode, pressing the arrow keys always moves you from cell to cell. Power users know the fastest way to get work done is to use tricky keyboard combinations like Ctrl+Alt+Shift+*. Although you cant always easily remember these combinations, a couple of tables can help you out. Table1-1 lists some useful keys that can help you whiz around the datasheet. Table1-1.Keys for Moving Around the Datasheet
Table1-2 lists some convenient keys for editing records. Table1-2.Keys for Editing Records
Figure1-11.An Access user has been on an eBay buying binge and needs to add several doll records. With a quick Ctrl+ keystroke, you can copy the date from the previous record into the DateAcquired field of the new record. Access, like virtually every Windows program, lets you cut and paste bits of information from one spot to another. This trick is easy using just three shortcut keys: Ctrl+C to copy, Ctrl+X to cut (similar to copy, but the original content is deleted), and Ctrl+V to paste. When youre in Edit mode, you can use these keys to copy whatever youve selected. If youre not in Edit mode, the copying or cutting operation grabs all the content in the field. Unlike other programs, Access doesnt require that you save your data. It automatically saves any edits you make to the records in a table. This automatic-saving process takes place every time you change a record, and it happens almost instantaneously. It also takes place behind the scenes, and you probably wont notice anything. But dont be alarmed when you exit Access and it doesnt prompt you to save changes, as any change to your data is saved the moment you make it. The rules are a bit different for database objects (Understanding Access Databases). When you add or edit a database object, Access waits until you finish and close the object, at which point it prompts you to save or discard your changes. If youre a bit paranoid and you cant stand the wait, just click the tiny Save icon in the Quick Access toolbar in the top-left corner of the window (it looks like a floppy disk) to save the current database object immediately. NoteRemember, when you click File, you enter Backstage view, which provides a narrow strip of commands (on the left) and a page with options for the currently selected command (on the right). You use Backstage view to open, save, and convert database filessee The Quick Access Toolbar if you need a quick review about how it works. The automatic save feature can pose a problem if you make a change mistakenly. If youre fast enough, you can use the Undo feature to reverse your last change (Figure1-12). However, the Undo feature reverses only your most recent edit, so its no help if you edit a series of records and then discover the problem. It also doesnt help if you close your table and then reopen it. Figure1-12.The Undo command appears in the Quick Access toolbar at the top left of the Access window, so its always available. For these reasons, its a good idea to make frequent database backups. To make a database backup, you simply need to copy your database file to another folder, or make a copy with another name (like Bobblehead_Backup1.accdb). You can perform these tasks with Windows Explorer, but Access gives you an even easier option. First, choose FileSave As. Then, under the File Types heading, choose Save Database As. Finally, under the Save Database As heading, double-click Back Up Database. This opens a Save As window that offers to create a copy of your database, in the location you choose (Figure1-13). Figure1-13.When you choose to create a backup, Access fills in a suggested file name that incorporates the current date. That way, if you have several backup files, you can pick out the one you want. Of course, its still up to you to remember to copy your database backup to another location (like a network server) or to a different type of storage (like a DVD or a USB memory stick), so youre ready when disaster hits. Saving a Database with a Different NameAccess makes this job easy. Just choose FileSave As and click the big Save As button. Access opens a Save As window, where you can browse to a different folder on your hard drive and type a new file name. When youre finished, click Save to seal the deal and create the newly named copy of your database. Keep in mind that once Access creates the new database file, that file is the one it keeps using. In other words, if you create another table or edit some of your data, Access updates the new file. If you want to go back to the old file, you need to open it in Access again. (Alternatively, you can use the backup feature described in the previous section. Like the FileSave As command, the backup feature creates a copy of your database with a new name, but after it makes the backup it carries on using the original version.) Saving a Database in a Different FormatWhen you create a new database, Access uses its modern .accdb format (which is short for Access database). Microsoft introduced the .accdb format with Access 2007, and it still works in Access 2010 and Access 2013. That makes it the go-to choice for new databases. However, there may be times when you need to share your data with people who are using truly ancient copies of Access. Versions before Access 2007 use a different database format, called .mdb (which stands for Microsoft database). And, as you can see in Figure1-14, the .mdb format actually comes in two versions: a really, really old version that supports Access 2000, and an improved that Microsoft introduced with Access 2002 and reused for Access 2003. Figure1-14.To change the format of your database, choose FileSave As (1), click Save Database As (2), and then pick the format you want from the Database File Types section (3). Use Access 2002-2003 Database or Access 2000 Database to save a .mdb file that works with very old versions of Access. The standard .accdb format is the best choice if you dont need to worry about compatibility, because it has the best performance and a few extra features. But if you need to share databases with people running much older versions of Access, the .mdb format is your only choice. TipOlder database formats are less reliable and may not support all of the Access features you want to use. The best approach is to stick with the .accdb format and save a copy of your data in an older format for the people who need it. However, if possible, keep using the modern .accdb format as the master copy of your databasethe one youll use to enter new data and to create your Access queries, reports, and forms. You can also use the old-style .mdb format when you first create a database. Choose FileNew and then click the folder icon next to the File Name box. Access opens the File New Database window (which you saw back in Figure1-3). It includes a Save as type box where you can choose the Access 2002-2003 file format or the even older Access 2000 format. (If youre set on going back any further, say the Access 95 format, your best bet is a time machine.) When you add information to a database, Access doesnt always pack the data as compactly as possible. Instead, Access is more concerned with getting information in and out of the database as quickly as it can. After youve been working with a database for a while, you might notice that its size bloats up like a week-old fish in the sun. If you want to trim your database back to size, you can use a feature called compacting. To do so, just choose FileInfo and click the big Compact & Repair Database button. Access then closes your database, compacts it, and opens it again. If its a small database, these three steps unfold in seconds. The amount of space you reclaim varies widely, but its not uncommon to have a 20 MB database shrink down to a quarter of its size. NoteIf you compact a brand-new database, Access shows a harmless security warning when the database is reopened. Youll learn about this message, and how to avoid it, in the next section. The only problem with the database-compacting feature is that you need to remember to use it. If you want to keep your databases as small as possible at all times, you can switch on a setting that tells Access to compact the current database every time you close it. Heres how:
You can set the Compact on Close setting on as few or as many databases as you want. Just remember, its not switched on when you first create a new database. Once youve created a database, its easy to open it later. The first step is go backstage; choose FileOpen. There youll see a list of all the databases youve viewed most recently (Figure1-15). To open one, just click it. Incidentally, you see the same list of recent databases when you first start Access (Figure1-1). Figure1-15.The Recent Databasess biggest advantage is the way it lets you keep important files at your fingertips by using pinning. To try it, click the thumbtack next to the database file you want to keep. Access moves your database to the top of the list and keeps it there. From this point on, that database wont leave the list, no matter how many databases you open. If you decide to stop working with the database later on, just click the thumbtack again to release it. TipDo you want to hide your recent work? You can remove any file from the Recent Databases list by right-clicking it and choosing Remove from list. And if the clutter is keeping you from finding the databases you want, just pin the important files, right-click any file, and choose Clear unpinned items. This action removes every file that isnt pinned down. Ordinarily, Access tracks the previous 25 databases in the FileRecent list, but you can tell it to keep a shorter or longer list. To change this setting, choose FileOptions, choose Client Settings, scroll down to the Display section, and change the number for Show this number of Recent Documents. You can pick any number from 0 to 50. If you want to open a database thats on your computer but not on the list of recent databases, you can browse your way to the file. Start by choosing FileOpen, and, in the Places list, click Computer (Figure1-16). Click one of the folders youve recently used, and Access shows an Open window listing the files in that location. Or, just click the big Browse button underneath to hunt around in the current folder. When you find the file you want, double-click it. Figure1-16.When you click Places, Access shows you a list of folders in which youve recently opened or saved databases. Click one, and Access shows you the familiar Open window for that folder. TipYou can also grab files from your SkyDrive file-sharing account, if youve configured it in Access. However, this is strictly a one-way street: you can download databases from your SkyDrive folder, but you cant upload new ones from Access. In other words, youll use SkyDrive as a way to transfer databases from one computer to another, not as a permanent home for your databases. If you want to keep your database on the Web, you need the web database feature described in Chapter20. (And to learn more about Microsofts SkyDrive service, visit http://tinyurl.com/skydr.) Finally, as always, you can open a database file from outside Access by simply double-clicking it in Windows Explorer or on your desktop. Designating a Database as TrustedWhen you open a database for the first time, youll notice something a little bizarre. Access pops up a message bar with a scary-sounding security warning (Figure1-17). Figure1-17.This security warning tells you that Access doesnt trust your databasein other words, its opened your file in a special safe mode that prevents your database from performing any risky operations. If youre opening your own recently created database, this security warning is a bit confusing, because right now your database doesnt even attempt to do anything risky. However, once you start building databases with code routines (as described in Part5), or when you start using action queries (Chapter8), its a different story. In those situations, you need to know if Access trusts your database and will allow it to run code and action queries. In the meantime, youre probably wondering what you should do about the message bar. You have two options:
Opening More Than One Database at OnceEvery time you use the FileOpen command, Access closes the current database and then opens the one you chose. If you want to see more than one database at a time, you need to fire up more than one copy of Access at the same time. (Computer geeks refer to this action as starting more than one instance of a program.) Its almost embarrassingly easy. If you double-click another database file while Access is already open, a second Access window appears in the taskbar for that database. You can also launch a second (or third, or fourth) instance of Access from the Start menu, and then use FileOpen to load up a different database in each one. Opening a Database Created in an Older Version of AccessYou can use the FileOpen command to open an Access database created with a previous version of Access. Access handles old database files differently, depending on just how old they are. Heres how it works:
TipYou can tell the current databases format by looking at the text in parentheses in the Access windows title bar. For example, if you open an Access 2002-2003 file, the title bar will include the text (Access 2002-2003 file format). When you open an old-school Access database, youll notice something else has changed. When you open a table, it doesnt appear in a tabbed window like the ones shown in Figure1-19. Instead, the table opens in an ordinary window that can float wherever it wants inside the main Access window. This seems fine at first, until you open several tables at once. Then, youre stuck with some real clutter, as shown in Figure1-18. Figure1-18.In an old-style Access database, different windows can overlap each other. Its not long before the table you want is buried at the bottom of a stack of windows. This somewhat unfriendly behavior is designed to mimic old versions of Access, like Access 2003. But dont worryyou can get back to the slick tabs even if you dont convert your database to the new format. All you need to do is set a single configuration option:
For a retro touch, you can use the same setting to make a brand-new Access database use overlapping windows instead of tabs. Its time to step back and take a look at what youve accomplished so far. Youve created the Bobblehead database and added a single database object: a table named Dolls. Youve filled the Dolls table with several records. You dont have the fancy windows, reports, and search routines that make a database work smoothly, but you do have the most important ingredientorganized data. One issue you havent tackled yet is how you manage the objects in your database. For example, if you have more than one table, you need a way to move back and forth between the two. That tool is the navigation pane, shown in Figure1-19. Figure1-19.Unhappy with the space consumed by the navigation pane? Click the Open/Close button in the top-right corner (circled at the top), and the navigation bar slides out of the way to give more room for the datasheet (bottom). Click the button again to expand it back into view. The navigation pane shows the objects (Understanding Access Databases) that are part of your database, and it lets you manipulate them. However, you dont necessarily see all your database objects at all times. The navigation pane has several different viewing modes, so you can home in on exactly what interests you. When you first create a database, the navigation pane shows only the tables in your database. Thats good enough for nowafter all, your database doesnt contain anything but the tables youve created. (Youll learn how to customize the navigation pane in Chapter14.) To try out the navigation pane, you need a database with more than one table. To give it a whirl, choose CreateTablesTable from the ribbon to add a new blank table. Follow the steps starting on Creating a Simple Table to define the table and insert a record or two. TipNot sure what table to create? Try creating a Collectors table that tracks all the friends you know who share the same bobbleheaded obsession. Now try to come up with a few useful fields for this table (while remembering that theres no need to go crazy with the details yet), and then compare your version to the example in Figure1-20. Once youve added the new table, you see both the new table and the old in the navigation pane at the same time. If you want to open a table, then, in the navigation pane, just double-click it. If you have more than one datasheet open at once, Access organizes them into tabs (see Figure1-20). Figure1-20.Using the navigation pane, you can open as many tables at once as you want. Access gives each datasheet a separate tabbed window. To move from one window to another, you just click the corresponding tab. If youre feeling a bit crowded, just click the X at the far right of the tab strip to close the current datasheet. If you open enough tables, eventually all the tabs wont fit. In this situation, Access adds tiny scroll buttons to the left and right of the tab strip. You can use these buttons to move through all the tabs, but it takes longer. Figure1-21.Do you want to use every square inch of screen space for your data? You can collapse the ribbon (as shown here) by double-clicking any tab. Click a tab to pop it open temporarily, or double-click a tab to bring the ribbon back for good. And if you want to perform the same trick without raising your fingers from the keyboard, you can use the shortcut key Ctrl+F1. Managing Database ObjectsSo far, you know how to open a table using the navigation pane. However, opening tables isnt all you can do with the navigation pane. You can actually perform three more simple tasks with any database object that shows up in the navigation pane:
Access gives you a few more options for transferring database objects and tucking them out of sight. Youll consider these features later in the book. |