Access 2013 All-In-One For Dummies
Access 2013 makes managing your data easy, combining a visual interface with the power of a relational database. Discover 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 Visual Basic for Applications (VBA) scripts to make your database smart, and using SharePoint to publish data to the web.
Designing a Relational Database in Access 2013
When you use a database program like Access 2013, 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 by relational joins, in which a field in one table matches (relates to) a field in another. Here’s 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. (Store birthdate or age, but not both, for example.)
Organize the fields into tables.
Group your fields according to what they describe so that each group becomes a table. 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 of every other code or abbreviation you plan to use in the database. You’ll use these tables to create drop-down menus of values you can choose 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 that identifies the customer who placed the order — a field to match the primary key field in the Customers table. Most relationships are one-to-many, in which one record in one table can match more than one (or no) records in another table.
Tips for Choosing Field Types in Access 2013
When you design a database in Access 2013, you decide what type each field will be. Access provides 12 field types for you to choose among. Choose the field type that best describes the data you want to store in the field and that works for the type of analysis you need to use the field. Here are tips on when to use which type of field.
|Field Type||What It Holds|
|Short Text||Text up to 255 characters long (including spaces and punctuation). Use a Text field, not a Number field, for codes even if they look like numbers, such as phone numbers, zip codes, and other postal codes.|
|Long Text||Text up to 65,536 characters. A Long Text field can contain Rich Text (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, as well as 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.|
|Hyperlink||Text string formatted as a hyperlink. (If you click the link, it takes you to the page.) This field type is especially useful if related information is available on the web.|
|Yes/No||Yes or no (a particular condition is, or isn’t, in effect) — or other two-word sets, such as True/False, On/Off, and Male/Female. Use a Yes/No field if you want to display the field as a check box on forms.|
|Attachment||Stores one or more entire files — pictures, sound, Word documents, even video — in one Attachment field.|
|Calculated||Data created with a formula. Use a Calculated field when a calculated value will be used in many queries, forms, and reports.|
Handy Access 2013 Keyboard Shortcuts
Some people would rather use the keyboard than the mouse, and Access 2013 has plenty of keyboard shortcuts for those people. The following keyboard shortcuts are especially useful in Access 2013. Some keystrokes work anywhere in Access 2013; others work only in specific views, as noted.
|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|
|Del||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’re typing.|
Secrets of Access 2013 Database Design
Here are the Five Commandments of database design, whether you use Access 2013 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 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 rule 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 that you aren’t stuck with having 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.
Avoid 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 to make everything work. Separating the tables from everything else streamlines the whole rigmarole of updating queries, forms, reports, and 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 make a backup copy before making any major changes. Keep several backups, not just the most recent one, in case it takes a while to discover a problem.
Finding the Right Access 2013 Tool for Keeping Garbage Out
If the data that goes into your database through tables and forms is garbage, 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 that 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: This property limits the number of characters. If you know that a field should never exceed four characters, for example, set Field Size to 4.
Format: This property makes the data look right. You can change text to all caps or all lowercase, for example.
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 — if an order number has two letters followed by four digits, for example. Phone numbers and zip codes are other examples of fields in which input masks are useful. Input masks work with the Format field property.
Default Value: This property 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: Data must pass this rule before it’s entered. This property works with the Validation Text property rule.
Required: This property specifies that the field must have a value 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: This property specifies whether a zero-length entry such as (quotes without a space between them) is allowed (only for Text, Long Text, 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’s a check box on the Datasheet tab of the Ribbon.
Lookup Fields: This property allows the user to select a value for a field that’s stored in another field, thereby eliminating many misspellings and standardizing the options for the field. Use the Lookup Wizard to create a lookup field.
Using Visual Basic for Applications Efficiently in Access 2013
When you’re automating an Access 2013 database application by using Visual Basic for Applications (VBA), you can get lost with a blank page 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:
Press F1 at any time to start Microsoft Access help, where you can see examples of VBA code.
Use the Object Browser.
The Object Browser lets you explore the properties and methods of the objects in VBA. In the Visual Basic Editor, choose View, then Object Browser or simply press F2.
Get more help.
Sure, the built-in help tools within VBA are wonderful, but you can also use your favorite browser to search the Internet for help 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 who use them.
Use functions and sub procedures.
Employ 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 may want to put that code in its own procedure.
Convert macros to VBA code.
The macro designer in Access 2013 lets you choose among 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 look if you’d typed it from scratch.
Sharing Data with Other Programs from within Access 2013
Access 2013 is a great database application on its own, but it also plays well with others, allowing you to display and retrieve data from several other programs. If a program supports Visual Basic for Applications (VBA), you can control it from Access. Here’s a brief list of applications that you can take control of and share data with:
Microsoft Excel: Many users of Excel end up getting involved with Access because they need more structure for storing their data. Some of these people don’t make the leap to Access, however, so a strong tie still exists between Access and Excel. You can use an Excel spreadsheet as a table in Access or completely take control of an Excel application from VBA.
Microsoft 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 you’re using large amounts of data. After the data is in SQL Server, you can build your Access forms, reports, macros, and modules to use the data from SQL Server.
Microsoft 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 you may be used to turning. You can also use Word as a reporting tool, where you can create bookmarks to place data from Access and someone else — who may be unfamiliar with Access — can edit the other information in the Word document.
Microsoft SharePoint: SharePoint is Microsoft’s vision for sharing and collaborating with data on the web. From Access 2013, you can share data with a SharePoint server. You can even create a custom web app that stores Access tables, queries, forms (as views), and macros on a SharePoint 2013 server and allows you or anyone else to access these forms on the web — all without having Access 2013 installed.
Microsoft Outlook: Outlook is more than just an e-mail tool; it’s also a contact management system. You can share contact, calendar, and task information with your Access application so that you have to enter it in only one place.