Access 2010 makes managing your data easy, combining a visual interface with the power of a relational database. Learn how to organize data into tables, design forms, and reports for editing and presenting information, and create queries for selecting and combining information. The next steps are writing macros and VBA scripts to make your database smart, and publishing data to the Web using SharePoint.
>
>
How to Design a Relational Database in Access 2010
When you use a database program like Access 2010, you can’t just start entering data. Instead, you need to create a relational database design, dividing your information into one or more tables, each made up of fields (which are like the columns in a spreadsheet). Tables are connected using relational joins, where a field in one table matches (relates to) a field in another. Here is the general idea.
Identify your data.
Make a list of the possible fields (pieces of information), including text, numeric, date, true/false, and other types of data.
Eliminate redundant fields.
Don’t store the same information in more than one place. If you can calculate one field from another, store only one. (For example, store birth date or age, but not both.)
Organize the fields into tables.
Group your fields according to what they describe, so each group becomes a table. For example, an order entry database for a store might have one table for customers, one for products, and one for orders.
Add tables for codes and abbreviations.
Plan to include a table of state names and two-letter codes, and a table every other code or abbreviation you plan to use in the database. You’ll use these tables to create dropdown lists of values you can choose from when entering records.
Choose a primary key for each table.
The primary key is the field that uniquely identifies each record in the table. You can tell Access to assign a unique ID number to each record by using an AutoNumber field.
Link the tables.
See which tables contain fields that match fields in other tables. In an order entry database, the Orders table must contain a field to identify the customer who has placed the order — a field to match the primary key field in the Customers table. Most relationships are one-to-many, where one record in one table can match more than one (or no) records in another table.
>
>
>
Tips for Choosing Field Types in Access 2010
When you design a database in Access 2010, you decide what type each field will be. Here are tips for when to use which type of field.
| Field Type | What It Holds |
|---|---|
| Text | Text up to 255 characters long (including spaces and punctuation). Use a Text field, not a Number field, for codes — such as phone numbers, ZIP codes, and other postcodes — even if they look like numbers. |
| Memo | Like a Text field, but more of them — up to 65,536 characters. A memo field can contain rich (formatted) text, and you can set it to Append Only, so that it can accumulate text notes, without allowing the user to delete what’s already there. |
| Number | Only numbers. You may use + or – before the number, and a decimal point. If you plan to do math with a field, use a Number or Currency field. |
| Currency | Numbers with a currency sign in front of them ($, ¥, and so on). |
| AutoNumber | Numbers unique to each record and assigned by Access as you add records, starting at 1. Use an AutoNumber field as the primary key field for most tables. |
| Date/Time | Dates, times, or both. |
| OLE Object | Object Linking and Embedding. Don’t use it when creating a new database; use the new Attachment type instead because it stores data more efficiently. |
| Hyperlink | This text string is formatted as a hyperlink. (If you click the link, it takes you to the page.) This is especially useful if there’s related information on the Web. |
| Yes/No | Yes or no (a particular condition is, or isn’t, in effect) — or other two-word sets, such as Yes/No field if you want to display the field as a check box on forms. |
| Attachment | You can store one or more entire files — pictures, sound, Word documents, even video — in one Attachment field. |
| Calculated | You enter a formula that Access uses to calculate the value of this field based on other fields in the table. Use a Calculated field when a calculated value will be used in many queries, forms, and reports. |
>
>
>
Handy Access 2010 Keyboard Shortcuts
These keyboard shortcuts are especially useful in Access 2010. Some keystrokes work anywhere in Access 2010, while others work only in specific views, as noted.
| Key or Combination | Action |
|---|---|
| F1 | Displays the Help window. |
| Ctrl+F1 | Hides or displays the Ribbon. |
| F5 | Goes to the record with the record number you type. |
| F6 | Moves the focus to another area of the window. |
| F7 | Checks the spelling in the selected object. |
| F11 | Hides or displays the Navigation pane. |
| Delete | Deletes the selected object. |
| Alt+Enter | In Design view, displays the properties of the selected object. |
| Ctrl+C | Copies the selected text or objects to the Clipboard. |
| Ctrl+F | Finds text (with the option to replace it) in the open table, query, or form. |
| Ctrl+N | Starts a new database. |
| Ctrl+O | Opens a database. |
| Ctrl+P | Prints the selected object. |
| Ctrl+S | Saves the selected object. |
| Ctrl+V | Pastes the contents of the Clipboard to the active window. |
| Ctrl+X | Deletes the selected text or object and saves it in the Clipboard. |
| Ctrl+Z | Undoes the last action that can be undone (our all-time favorite!). |
| Ctrl+; | Types today’s date. |
| Ctrl+” | Duplicates the entry from the same field in the previous record. |
| Esc | Cancels what you are typing. |
>
>
>
Secrets of Access 2010 Database Design
Here are the Five Commandments of database design, whether you use Access 2010 or another database program. A well-designed database makes maintaining your data easier.
Store information where it belongs, not where it appears. Where you store information has nothing to do with where it appears. In a spreadsheet, you type information where you want it to appear when you print the spreadsheet, but databases work differently. In a database, you store information in tables based on the structure of the information. A piece of information may appear in lots of different reports, but you store it in only one field in one table.
Store information as it really exists, not as you want it to appear in a specific report. This is a corollary to the first rule. If you want book titles to appear in all uppercase (capital) letters in your purchase orders, Access can capitalize the titles for you. Store the book titles with correct capitalization so you aren’t stuck with them in all caps on every report. Access has lots of built-in functions that can adjust the way that text, numbers, and dates are formatted.
Garbage in, garbage out (GIGO). If you don’t bother to create a good, sensible design for your database — and if you aren’t careful to enter correct, clean data — your database will end up full of garbage.
A well-designed database is easier to maintain than a badly designed one because each piece of information is stored only once, in a clearly named field in a clearly named table, with the proper validation rules in place. Yes, it sounds like a lot of work, but cleaning up a database of 10,000 incorrect records is (pardon the understatement) even more work.
Separate your data from your programs. If you create a database to be shared with (or distributed to) other people, store all the tables in one database (the back end) and all the other objects in another database (the front end). Then you can link these two databases together to make everything work. Separating the tables from everything else streamlines the whole rigmarole of updating queries, forms, reports, or other stuff later without disturbing the data in the tables.
Back up early and often. Okay, this tip isn’t about design, but it’s too important to omit: Make a backup of your database every day. With luck, your office already has a system of regular (probably nightly) backups that includes your database. If not, make a backup copy of your database at regular intervals, and certainly before making any major changes. Keep several backups, not just the most recent one, in case it takes a while to discover a problem.
>
>
>
How to Keep Garbage Out of Your Access Database
If the data that goes into your database through tables and forms is garbage, then any output or analysis you do with queries and reports will give you garbage too. Fortunately, Access offers lots of tools to help you make sure that the data that goes in each field is the data that’s supposed to go in that field. You can find many of the tools to keep garbage out in Table Design view:
Data type: Use the correct data type to prevent data of the wrong type from being entered.
Field Size: Limits the number of characters. For instance, if you know that a field should never exceed four characters, set the field size to 4 characters.
Format: Makes the data look right. For instance, you can change text to all caps or all lowercase. Input masks, for example, work with the Format field property.
Input Mask: An input mask limits the information allowed in a field by specifying what characters you can enter. Use an input mask when you know the form the data should take — for instance, if an order number has two letters followed by four digits. Phone numbers and ZIP codes are other examples of fields where input masks are useful.
Default Value: Defines a value that appears by default if no other value is entered. The default value appears in the field until another value is entered.
Field or Record Validation Rule: A rule that data must pass before it is entered. This property works with the Validation Text property rule.
Required: Specifies that the field must have a value in order for you to save the record. When no value is entered, Access doesn’t create a new record when Tab or Enter is pressed, and the New Record button is grayed out.
Allow Zero Length: Specifies whether a zero-length entry such as “” (quotes without a space between them) is allowed (only for Text, Memo, and Hyperlink fields). A zero-length field allows you to differentiate between information that doesn’t exist, and a null value (blank) that is unknown or hasn’t been entered. When this option is set, it allows a zero-length string in a required field. You may want to use an input mask to make a zero-length field look different from a null value when both are allowed.
Indexed: When you choose to index a field, you can specify that no duplicate values are allowed in the field. This property is also accessible from Datasheet view — it is a check box on the Datasheet tab of the Ribbon.
Lookup Fields: Allow the user to select a value for a field that is stored in another field. This eliminates many misspellings and standardizes the options for the field. Use the Lookup Wizard to create a Lookup field.
>
>
>
Using VBA Efficiently in Access 2010
When automating an Access 2010 database application using VBA, you can get lost with a blank page on which to begin writing code. Where do you start? Here are a few simple guidelines to follow that will have you writing VBA code like a pro:
Get help. Yes, this article lists a few ways to enhance using VBA in Access, but press F1 at any time to start Microsoft Office Access Help, where you can learn about and see examples of VBA code.
Use the Object Browser. The Object Browser lets you explore the different properties and methods of the objects in VBA. From the VBA Editor window, choose View → Object Browser — or simply press F2.
Get more help. Sure, the built-in help tools within Access 2010 VBA are wonderful, but you can also use your favorite browser to search the Internet for help on a variety of topics on writing VBA code. You can even find examples that you can steal — or borrow — for your own project.
Handle your errors. Even the most perfect programmer can’t stop errors from occurring, but he or she can stop them from bringing a program to a grinding halt. Use VBA’s built-in error handling with the On Error Goto and Resume statements to trap errors and change the program flow so that your applications don’t frustrate the people using them.
Use functions and sub procedures. Use functions and sub procedures to handle tasks that will be performed by different areas of your program. As a general rule, if you find yourself copying and pasting code from one area of a program to another, you might want to put that code in its own procedure.
Convert macros to VBA code. The Macro designer in Access 2010 lets you click and choose from lists of predefined tasks to automate your application. Build a macro that does what you want, and then convert that macro to VBA code so you can see how it would like if you typed it from scratch.
>
>
>
Sharing Data with Other Programs from within Access 2010
Access 2010 is a great database application on its own, but it also plays well with others. You can display and retrieve data from a number of different programs. If a program supports VBA, then you can control it from Access. Here’s a brief list of Microsoft applications you can take control of and share data with:
Excel: Many Excel users end up getting involved with Access because they need more structure to storing their data. However, some of these people don’t, so there is still a strong tie between Access and Excel. You can use an Excel spreadsheet as a table in Access, or completely take control of an Excel application from Access VBA.
SQL Server: SQL Server is like Access on steroids — at least from the table and query standpoint. You can move your data from Access to SQL Server to improve speed and performance when using large amounts of data. Once the data is in SQL Server, you can build your Access forms, reports, macros, and modules to use the data from the SQL Server.
Word: Most people who use Access have also used Word. Whether you’re writing a letter or making a list of tasks to do, Word is where many of us turn. You can also use Word as a reporting tool where you can create bookmarks to place data from Access, so that you can have someone unfamiliar with Access change the static data in the report.
SharePoint: SharePoint is Microsoft's vision for sharing and collaborating with data on the Web, and from Access 2010, you can share data with a SharePoint Server. You can even create forms and reports in Access and publish them to a SharePoint site so that users can use them from within their browsers — all without having Access 2010 installed.
Outlook: Outlook is more than just an e-mail tool; it’s also a contact management system. Using Access 2010, you can send e-mails and collect data directly from the received e-mail messages. You can also share the contact, calendar, and task information with your Access application so that you have to enter this data in only one place.
>
>
dummies
Source:http://www.dummies.com/how-to/content/access-2010-allinone-for-dummies-cheat-sheet.html
No comments:
Post a Comment