4_DB_development_method_l4_v1
Оценка 5

4_DB_development_method_l4_v1

Оценка 5
docx
09.05.2020
4_DB_development_method_l4_v1
4_DB_development_method_l4_v1.docx

1.                  Figure 1-1. When you start Access, you see this two-part welcome page. On the left is a list of recently opened databases (if you have any). On the right is a list of templates that you can use to create a new database.

TIP

If you already have Access open and you’ve been working with another database, just choose File→New to create a new database. You’ll get the same list of templates as when you first launch Access.

2.                  Click the “Blank desktop database” template.

When you choose to create a blank database, that’s exactly what you get—a new, empty database file with no tables or other database objects. Starting from scratch is the best way to learn about Access. It’s also the favorite choice of database experts, who prefer to create everything themselves so it’s exactly the way they like it.

Other templates let you create databases that are preconfigured for specific scenarios and certain types of data. The box on Templates: One Size Fits Some has more information.

The cool-sounding “Custom web app” template is a special case. It lets you create a web-enabled database that runs on SharePoint. You’ll explore this new feature (and its limitations), in Chapter 20.

No matter which template you click, Access pops open a new window that lets you choose a name and location for your new database (Figure 1-2).

UP TO SPEED: TEMPLATES: ONE SIZE FITS SOME

The example in this section shows you how to create a blank database. However, if you scroll down (on the right side of the Figure 1-1), you’ll find a long list of prebuilt databases, which are known as templates. Templates aim to save you the work of creating a new database and let you jump straight to the fine-tuning and data-entry stage.

As you might expect, there’s a price to be paid for this convenience. Even if you find a template that stores the type of information you want to track, you might find that the predefined structure isn’t quite right. For example, if you choose to use the Home Inventory template to track all the stuff in your basement, you might find that it’s missing some information you want to use (like the projected resale value of your stuff on eBay) and includes other details you don’t care about (like the date you acquired each item). To make this template work, you’ll need to change the design of your table, which involves the same Access know-how as creating one.

In this book, you’ll learn how to build your own databases from the ground up and customize every square inch of them. Once you’re an Access master, you can spend many fun hours playing with the prebuilt templates and adapting them to suit your needs. To give it a whirl, click one of a dozen or so templates that are shown in the main Access window. Or, even better, hunt for more by using the Search box at the top of the Access window, which scans through the thousands of templates available on Microsoft’s Office website.

3.                  Type a file name for the database you’re about to create.

Access stores all the information for a database in a single file with the extension .accdb (which stands for “Access database”). Don’t stick with the name Access picks automatically (like “Database1.accdb”). Instead, pick something more descriptive. In this example, Bobblehead.accdb does the trick.

As with any other file, Access files can contain a combination of letters, spaces, numbers, parentheses, hyphens (-), and the underscore (_). It’s generally safest to stay away from other special characters, some of which aren’t allowed.

This database will be named Bobblehead.accdb. As you can see by the file path below the File Name box, it will be saved in the folder C:\Users\matthew\Documents. You can edit the file name by typing in the File Name box, and you can browse to a different folder by clicking the folder icon.

Figure 1-2. This database will be named Bobblehead.accdb. As you can see by the file path below the File Name box, it will be saved in the folder C:\Users\matthew\Documents. You can edit the file name by typing in the File Name box, and you can browse to a different folder by clicking the folder icon.

NOTE

Depending on your computer settings, Windows may hide file extensions. Instead of seeing the Access database file MyScandalousWedding.accdb in file-browsing tools like Windows Explorer, you may just see the name MyScandalousWedding (without the .accdb part on the end). In this case, you can still tell the file type by looking at the icon. If you see a small Access icon next to the file name, that’s your signal that you’re looking at an Access database.

4.                  Choose the folder where you want to store your database.

Like all Office programs, Access assumes you want to store every file you create in your personal Documents folder. If this isn’t what you want, click the folder icon to show the File New Database window, browse to the folder you want (Figure 1-3), and then click OK.

The File New Database window lets you choose where you’ll store a new Access database file. It also gives you the option to create your database in the format used by older versions of Access (.mdb), instead of the more modern format used by Access 2007, Access 2010, and Access 2013 (.accdb). To change the format, simply choose the corresponding Access version from the “Save as type” list, as shown here.

Figure 1-3. The File New Database window lets you choose where you’ll store a new Access database file. It also gives you the option to create your database in the format used by older versions of Access (.mdb), instead of the more modern format used by Access 2007, Access 2010, and Access 2013 (.accdb). To change the format, simply choose the corresponding Access version from the “Save as type” list, as shown here.

5.                  Click the big Create button (under the File Name box).

Access creates your database file and then shows a datasheet where you can get to work creating your first table.

POWER USERS’ CLINIC: TELLING ACCESS WHERE TO STORE YOUR DATABASES

Access always assumes you want to store databases in your Documents folder. And though you can choose a different location every time you save or open a database, if there’s another folder you need to visit frequently, then it makes sense to make that your standard database storage location. You can configure Access to use this folder with just a few steps:

1.             Make sure you’ve opened a database or created a new one. You can’t make this change from the window you see when you first start Access.

2.             Choose File→Options. The Access Options window appears.

3.             In the list on the left, choose General.

4.             In the page on the right, look for the “Creating databases” heading. Underneath, you’ll find a “Default database folder” text box. Type the path to the folder you want to use (like C:\MyDatabases), or click Browse to navigate to it.

When you’re finished, click OK to save your changes.

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. You’re now in the control center where you’ll perform all your database tasks (Figure 1-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 you’ll do your work, such as designing tables and entering data.

Figure 1-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 you’ll do your work, such as designing tables and entering data.

If you haven’t 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 you’ll learn how to add a table to your brand-new, empty database.

Building Your First Table

Tables are information containers. Every database needs at least one table—without it, you can’t store any data. In a simple database, like the Bobblehead database, a single table (which we’ll 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 Chapter 5, you’ll see plenty of examples of databases that use multiple tables.

Figure 1-5 shows a sample table.

In a table, each record occupies a separate row. Each field is represented by a separate column. In this table, it’s clear that you’ve added five bobblehead dolls. You’re storing information for each doll in five fields (ID, Character, Manufacturer, PurchasePrice, and DateAcquired).

Figure 1-5. In a table, each record occupies a separate row. Each field is represented by a separate column. In this table, it’s clear that you’ve added five bobblehead dolls. You’re 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:

o        A table is a group of records. A record is a collection of information about a single thing. In the Dolls table, for example, each record represents a single bobblehead doll. In a Family table, each record would represent a single relative. In a Products table, each record would represent an item that’s for sale. You get the idea. When you create a new database, Access starts you out with a new table named Table1, although you can choose a more distinctive name when you decide to save it.

o        Each record is subdivided into fields. Each field stores a distinct piece of information. For example, in the Dolls table, one field stores the person on whom the doll is based, another field stores the price, another field stores the date you bought it, and so on.

o        Tables have a rigid structure. In other words, you can’t bend the rules. If you create four fields, every record must have four fields (although it’s acceptable to leave some fields blank if they don’t apply).

o        Newly created tables get an ID field for free. The ID field stores a unique number for each record. (Think of it as a reference number that will let you find a specific record later on.) The best part about the ID field is that you can ignore it when you’re entering a new record. Access chooses a new ID number for you and inserts it in the record automatically. You’ll learn much more about ID fields on AutoNumber.

UP TO SPEED: DATABASE PLANNING FOR BEGINNERS

Many database gurus suggest that before you fire up Access, you should decide exactly what information you want to store by brainstorming. Here’s how it works. First, determine the type of list you want by finishing this sentence “I need a list of.…” (One example: “I need a list of all the bobblehead dolls in my basement.”)

Next, jot down all your must-have pieces of information on a piece of paper. Some details are obvious. For example, for the bobblehead doll collection, you’ll probably want to keep track of the doll’s name, price, and date you bought it. Other details, like the year it was produced, the company that created it, and a short description of its appearance or condition may require more thought.

Once you’ve completed this process and identified all the important bits of data you need, you’re ready to create the corresponding table in Access. The bobblehead doll example demonstrates an important theme of database design: First you plan the database, and then you create it using Access. In Chapter 5, you’ll learn a lot more about planning more complex databases.

Creating a Simple Table

When you first create a database, it’s almost empty. But to get you started, Access creates your first database object—a 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, you’re already at the Datasheet view (Figure 1-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:

o        Design view lets you precisely define all aspects of a table before you start using it. Almost all database pros prefer Design view, and you’ll start using it in Chapter 2.

o        Datasheet view is where you enter data into a table. Datasheet view also lets you build a table on the fly as you insert new information. You’ll use this approach in this chapter.

The following steps show you how to turn a blank new table (like Table1) into the Dolls table by using the Datasheet view:

1.                  To define your table, simply add your first record.

In this case, that means choosing a bobblehead doll to add to the list. For this example, you’ll use a nifty Homer Simpson replica.

NOTE

It doesn’t matter which doll you enter first. Access tables are unsorted, which means they have no underlying order. However, you can sort them any way you want when you need to retrieve information later on.


 

Скачано с www.znanio.ru

Figure 1-1. When you start Access, you see this two-part welcome page

Figure 1-1. When you start Access, you see this two-part welcome page

To give it a whirl, click one of a dozen or so templates that are shown in the main

To give it a whirl, click one of a dozen or so templates that are shown in the main

Choose the folder where you want to store your database

Choose the folder where you want to store your database

Make sure you’ve opened a database or created a new one

Make sure you’ve opened a database or created a new one

If you haven’t used the ribbon before (either in

If you haven’t used the ribbon before (either in

Family table, each record would represent a single relative

Family table, each record would represent a single relative

If you followed the steps in the previous section to create a new database, you’re already at the

If you followed the steps in the previous section to create a new database, you’re already at the
Скачать файл