Transcription
Access Database DesignTechnical Support ServicesOffice of Information Technology, West Virginia UniversityOIT Help Desk -- 293-4444 x nstructors: Bob Haring-Smith, Kathy FletcherLast revised: January 19, 2006Copyright 2006 West Virginia University
Table of ContentsCourse Description. 1Advanced Table Utilities . 2Exporting Data. 2Importing Data . 3Creating Lookup fields. 6Setting Validation Rule Properties . 8Setting validation rule and validation text properties. 8Setting the default value property . 9Set the format properties . 9Set the input mask property. 9Defining Relationships . 11Relationship Window . 11Referential Integrity . 12Database Utilities . 15Compacting databases. 15Repairing a database . 16Analysis tools. 16Sharing a database . 17Splitting a Database. 19Why would you split a database? . 19How to split a database . 19Maintaining a database after it is split. 19Linked Table Manager. 20Environment Options . 21Database Properties. 21ii
Course DescriptionThis is the fourth in a series of workshops about Microsoft Access 2003. It dealsspecifically with database design and maintenance.The purpose of this installment is to expose you to the possibilities involved in designingyour databases. To introduce you to the various options that Access provides forimporting and exporting data as well as the various utilities provided for maintainingyour databases.Our goal is to assist you to learn the software, understand some basic concepts andshow you some tips and techniques so you can develop your databasemanagement/programming skills over time.The six classes are: Introduction to Access Access Queries Access Reports Access Database Design Access Form Design Access MacrosThank you,The OIT Support Services TrainersWest Virginia UniversityAccess Database Design1
Advanced Table UtilitiesExporting DataAccess allows you to export entire tables or a portion of a table to an Excel spreadsheetas well as other file formats.To export an entire table, follow the steps below.Activity 1: Exporting Access tables to an Excel spreadsheet1. Open the SCHOOL2.mdb database file.2. Click once on the STUDENT table.3. Choose the Export command from the File menu.4. Specify a storage location for the exported file.5. Specify the file format. Select the Microsoft Excel 97-2002 (*.xls) entry in the Save as type pop-upmenu at the bottom of the Export dialog box. After doing so the name of the table should appear in the File name field.6. Modify the file name if necessary.7. Turn On the Save Formatted checkbox to the right of the File name field. This will preserve individual field properties for the exported table.Option: Turn On the Autostart checkbox.This will automatically launch Excel and open the newly exported file.8. Click the Export button.Exporting specific records to an Excel SpreadsheetIf you wish to export just a specific set of records from a table follow the steps below.1. Open the table from wish you wish to export a set of records.2. Select the records you wish to export.3. Follow steps 3 – 7 above.When exporting a subset of a table the Export All buttonchanges it’s appearance and provides 2 options: Export All – Exports the entire table. Save Selection – Exports only the selected records in the table.2Access Database Design
Importing DataAccess allows you to import data from external files. This is accomplished by eitherImporting the data from a file or by Linking to a file.Should you Import or Link a foreign file?In making that decision, consider that Access works faster in its own file format and thatyou can customize that format to meet your needs. Therefore, you should import if youcan; if you cannot import, then link. The exact procedure varies with the type of files youare using and whether you are importing or linking.In general: Research the procedure. Make backup copies of all files. Test the import or link procedure. Test the foreign files in Access thoroughly before discarding backups.Before you import or link to external data, it is important to check that the data isarranged in an appropriate tabular format, and has the same type of data in each field(column) and the same fields in every row.Importing data from an Excel spreadsheet1. Open the database.2. On the File menu, choose Get External Data, and then Click the Import entry to import the data ORClick the Link Tables entry to link to the external data source.3. In the Import (or Link) dialog box, in the Files of type box,select Microsoft Excel (*.xls).4. In the dialog box locate the drive and folder where the spreadsheet file is located,and then double-click its icon.5. Follow the directions in the Import Spreadsheet Wizard dialog boxes.Access Database Design3
Notes on Importing You can import or link all the data from a spreadsheet, or just the data from anamed range of cells. You can create a new table in Access based on the data. You can append the data to an existing table as long as your spreadsheetcolumn headings match the table's field names. If you attempt to append a table which contains a primary key field and yourdata for that field contains null values you will experience an error. Access attempts to assign the appropriate data type to imported fields, but youshould check your fields to make sure that they are set to the data type you want. For example, in an Access database, a phone number or postal code fieldmight be imported as a Number field, but should be changed to a Text fieldin Microsoft Access because it is unlikely that you will perform anycalculations on these types of fields. You should also check and set fieldproperties, such as formatting, as necessary.Notes on Linking If you link to a file and that file is deleted or moved from its original location (i.e., itis placed in a different folder or drive) the link to the file will be severed. You thenhave to use the Linked Table Manager to re-establish the link if the file has notbeen deleted. If you link to a file on a local area network, it is best to use a universal namingconvention UNC* path (if you know it), instead of relying on the drive letter of amapped network drive in Microsoft Windows Explorer. A drive letter can vary ona computer or may not always be defined, whereas a UNC path is a reliable andconsistent way for Microsoft Access to locate the data source that contains thelinked table.* UNC stands for universal naming convention. It is a naming convention for filesthat provides a machine-independent means of locating the file. Rather thanspecifying a drive letter and path, a UNC name uses the syntax\\server\share\path\filename.4Access Database Design
Activity 2: Importing an Access table1. Open the database SCHOOL2.MDB if it is not already open.2. Select the Get External Data command from the File menu and click the Importentry.3. Select the data type of the external file: Access4. Locate the file named Athletics.mdb and click on the Import button.5. Select the table named Ping-Pong.6. Click the OK button.Activity 3: Link to a table in another database1. Open the database SCHOOL2.MDB if it is not already open.2. Select the Get External Data command from the File menu and click the LinkTables entry.3. Select the data type of the external file: dBASE III .4. Highlight the file named Pool.dbf.5. Click the Link button.6. Click the OK button on the SuccessfullyLinked dialog window.7. Click the Close button in the standard openfile dialog box.Note: This table has an arrow icon to its leftside indicating that it has been linked to anexternal file.Access Database Design5
Creating Lookup fieldsThe data entry process can be simplified and the number of typed errors reduced bylooking up or obtaining values from a list. The source data can be obtained from: An Access table with a primary key field. A query that extracts specified columns from a table. A list of values entered into a field property.Access contains a Lookup Wizard; listed as a field data type, that simplifies thecreation of a lookup field.Note however, that using a table or query as a basis for the lookup field implies that thedata source exists before starting the Lookup Wizard.Once the data source is created: Open the table in design view. Move to an empty row where the lookup field is to be placed.Note: you can leave the field name blank and the wizard will assign a field name.6 Select the Lookup Wizard under data type. Respond to the questions posed by the Lookup Wizard.Access Database Design
Activity 4: Create a Lookup Field for Textbook Name1. Open the SCHOOL2.MDB sample database if it is not already open.2. Open the CLASS table in Design view.3. Click in the Data Type area for the first blank row in the table.4. Select Lookup Wizard from the drop-down menu. This will start the Lookup Wizard.5. Screen 1 asks: How do you want your lookup column to get its values? Choose the option to look up the values in a table or query then click theNext button.6. Screen 2 asks: Which table or query? Select the TEXTBOOK table and click Next.7. Screen 3 asks: Which fields contain the values? Select the B-Title field, click the button to add it to the Selected Fields list,and click Next.8. Screen 4 asks: What sort order do you want for your list?Choose B-Title, click Next.9. Screen 5 asks: What column width do you want? Drag the right column edge to see the text names, then click Next.10. Screen 6 asks: What label would you like for your lookup column? Accept the default field name (B-Title) and click the Finish button.11. Screen 7 asks: Prompts you to save? Click Yes.Notice the new field named B-Title in design view of the Class table.This field was the primary key of the textbook table. Also look at the property settings under the Lookup tab.12. Switch to Datasheet view.Notice the new field named B-Title. Click in this field for the first record and pull down the menu. Select a textbook title from the list of books and its name will be inserted in the datasheet.Access Database Design7
Setting Validation Rule PropertiesSetting validation rule and validation text properties The validation rule property specifies the criteria that the input data must meetbefore it can be stored in the table.It is generally a list of acceptable values or an expression. If the data beingentered does not meet the criteria, then Access displays an error message andrefuses to move from the field until the data is corrected. The validation text property defines an error message that will appear when thevalidation rule is violated.Example:In the SCHOOL2 database the Student table contains a Gender field. 1 isentered for female or 2 for male. The validation rule would be “1” Or “2”.The validation text might be something like this: “Data Entry Error: Enter 1 forFemale or 2 for Male.”When you enter the incorrect criteria you will get the following error messagebased on your validation text:8Access Database Design
Setting the default value propertyA default value is the value that is automatically inserted into a field by Access. You canaccept this value or replace it with a different value.Example:In the SCHOOL2 database open the Textbook table in Design view and insert anew field called Required. Under the Field Properties enter “Yes” as the DefaultValue.Save and switch to datasheet view. Enter a new Textbook and notice the defaultvalue.Set the format propertiesThe format property allows you to specify the appearance of the data in the field.Example:In the SCHOOL2 database open the Teacher table in Design view. Put yourcursor in the HireDate field, under field properties click the Format box. Choosea format from the list.Set the input mask propertyThe input mask property allows you to define a pattern for entering data into the field.Example:In the SCHOOL2 database open the Teacher table in Design view. Put yourcursor in the HireDate field, under field properties click the Input Mask box. Clickon the builder button to activate the Input Mask Wizard.Scroll down the list to choose the short date format dd/mm/yyyy.If you don’t enter the exact date format it will give you the following errormessage:Access Database Design9
Activity 5: Setting properties in the teacher table1. Open the SCHOOL 2 database if it is not already open.2. Select the TEACHER table and open it in Design View.3. Change the following field properties:Field NameData TypeT-PhoneTextPropertiesField Size: 15Input Mask Click Build buttonto run the Input Mask Wizard Choose Phone Number Accept all default answers Switch to the Datasheet View and enter a few new records to see how thechanges affect the behavior of the field in the table. Close the table.4. Open the STUDENT table in Design View.Field NameData TypeS-ScoreNumberPropertiesFormat FixedDecimal Places 1Validation Rule: 200 And 900Validation Text: “Score must be between 200 & 900” Switch to the Datasheet View and enter a few new records to see how thechanges affect the behavior of the field in the table. Close the table.5. Open the CLASS table in Design View.Field NameData TypeC-DayTextPropertiesValidation Rule:"mo" Or "tu" Or "we" Or "th" Or "fr" Or "sa"Validation Text:Must be mo, tu, we, th, fr, or saC-CostCurrencyFormat: Currency Switch to the Datasheet View and enter a few new records to see how thechanges affect the behavior of the fields in the table. Close the table.10Access Database Design
Defining RelationshipsAfter you've set up multiple tables in your Microsoft Access database, you need a wayof telling Access how to bring that information back together again. The first step in thisprocess is to define relationships between your tables.A relationship works by matching data in key fields - usually a field with the same namein both tables. In most cases, these matching fields are the primary key from one table,which provides a unique identifier for each record, and a foreign key in the other table.For example, teachers can be associated with the students they're responsible for bycreating a relationship between the teacher's table and the student's table using theTeacherID fields.By creating relationships you prevent the duplication of information in a database.Relationship WindowTo open the relationship window go to Tools Relationships or clickon the Relationship button on the Toolbar.If your database does not have any relationships defined, the Show Table dialog boxwill automatically be displayed. Add the tables you want to relate.Once you have all the tables you want to relate. Define a relationship between 2 tablesby dragging the field that you want to relate from one table to the related field in theother table.Access Database Design11
In most cases, you drag the primary key field (which is displayed in bold text) from onetable to a similar field (often with the same name) called the foreign key in the othertable. The related fields are not required to have the same names (but it’s good practiceto do so since it reminds you where the relationship comes from), but they must havethe same data type and contain the same kind of information. In addition, when thematching fields are Number fields, they must have the same FieldSize property setting.Once you have created the relationships, the Edit Relationships dialog box isdisplayed. Check the field names displayed in the two columns to ensure they arecorrect. You can change them if necessary.Referential IntegrityReferential integrity is a system of rules that Microsoft Access uses to ensure thatrelationships between records in related tables are valid, and that you don't accidentallydelete or change related data.If referential integrity is set on the relationship between the teacher table and the classtable and a teacher is assigned to teach a course, Access will not allow you to deletethat teacher’s record in the teacher table. Also, it will not allow you to assign a teacherto a class if they are not in the teacher table.If you choose to set referential integrity, then two other options are available for youruse:12 Cascading Updates: If you make a change to the primary key in the source table,then that change is cascaded to all related foreign keys in the target table. Cascading deletes: If you delete a primary key in the source table then all relatedrecords in the target table is also deleted.Access Database Design
Activity 6: Create relationships between the school table1. Open the sample database SCHOOL2.MDB if it is not already open.2. Link TEACHER to CLASS by creating a new field in Class. Open the CLASS table in Design View and add the following field:Field NameData TypeDescriptionC-TeachLookupWizardLink to TEACHER, T-LName Follow through the Lookup Wizard to select the Teacher table and thenselect T-LName. Close the Design View of the CLASS table and Save changes if prompted.3. Link STUDENT to CLASS by creating a new linking table ATTEND.Note: the primary key for ATTEND should be the combination of the foreignkeys, ClassID and StudentID.Field NameData TypeClassIDNumberField Size: Long IntegerLink to CLASS, C-IDStudentIDNumberField Size: Long IntegerLink to STUDENT, S-IDStatusTextField Size: 1C CompletedValidation Rule:"C" Or "A" Or "W"A AttendingField Size: SingleStudent’s Final GradeGradeNumberPropertiesDescriptionW WithdrewFormat: FixedDecimal Places: 2 Close the Design View of the ATTEND table and Save changes if prompted.4. Open the Relationships window by choosing Tools Relationships In the Relationship window, choose the Show Table command from theRelationships menu. This displays the Show Table dialog box. Add the TEACHER, STUDENT, CLASS, TEXTBOOK, ATTEND andPingPong tables. Then: Drag T-ID from TEACHER to C-Teach in CLASS Drag S-ID from STUDENT to StudentID in ATTEND Drag C-ID from CLASS to ClassID in ATTEND Drag S-ID from STUDENT to PP-Player in PingPongNote: A relationship already exists between the CLASS and TEXTBOOK tables.This relationship was created by the Look Up Wizard Activity.Access Database Design13
Activity 7: Finish entering the data in the Class table1. Update the C-Teach field in the existing records of the CLASS table by selectingTeacher name from the lookup field.CLASS tableATTEND tableAfter completing this activity, the data shouldappear identical to the databaseSCHOOL3.mdb.If you don’t want to do the data entry, closeSCHOOL2.mdb and open SCHOOL3.mdb.14Access Database Design
Database UtilitiesCompacting databasesWhen you delete a database object or record, it leaves behind an empty hole where theobject previously occupied space. This isn’t a big deal unless, over time, you havedeleted lots of database objects and records. Compacting a database rearranges howthe database is stored and reduces its file size.To ensure optimal performance, you should compact and repair your Microsoft Accessfiles on a regular basis.If you are compacting a shared Microsoft Access database that is located on a server orshared folder, make sure that no one else has it open.Note: The compacting process needs a block of free disk space that is greater than orequal to the file size in order to make a duplicate copy of the database. To avoidproblems during the compacting process, check your free space before executing thecompacting command.Compact a database that is open:1. On the Tools menu, point to Database Utilities, and then click Compact andRepair Database.Compact a database that is not open:1. On the Tools menu, point to Database Utilities, and then click Compact andRepair Database.2. This opens a standard file dialog box.2. Locate and select the database to be compacted.3. A standard file dialog is displayed which prompting you to (re)name the new copyof the database.3. Specify a new name and location for the database.4. Click the Compact button.Access Database Design15
Repairing a databaseOver time, normal wear and tear causes errors to appear in your database, affecting itsperformance. Usually these errors are very minor and can easily be fixed by repairingthe database.Microsoft has combined compact and repair into a single command in Access 2003.When should you use the compact and repair command? If you have been busy adding,editing, and deleting records for a while or if your database seems buggy, seemssluggish, or is generating error messages, it’s a good idea to run the Compact andRepair Database command.Analysis toolsThe Performance Analyzer will examine your database and offer suggestions of waysto improve the performance of the objects. It does not analyze Access itself or theunderlying operating system.To use the performance analyzer:1. Open the database that is to be analyzed.Note: The database needs a reasonable amount of data in the tables before theanalyzer can make realistic projections.2. Select Tools Analyze from the menu and select Performance.3. Select the type of object and then the name of the object that is to be optimized.4. Repeat the until all desired objects are selected or use the All Object Types tabto view a list of all objects in the database. A Select All button is provided tosimplify the selection process.4. Click the OK button to run the analysis.5. The Analyzer examines each of the objects and offers suggestions,recommendations, and ideas.16Access Database Design
The Table Analyzer examines your tables and looks for design flaws. It tests yourtables to see if they conform to the commonly accepted rules of data normalization andthen offers suggestions for improving the tables.To use the table analyzer:1. With the database open, Select Tools Analyze from the menu and selectTable.2. When the Table Analyzer Wizard starts, move to the table selection screen andindicate the table that you want to analyze.3. When you let the wizard automatically analyze your table, it will advise you ofchanges that need to be made (like splitting the table into multiple tables) andthen it will make them for you.Sharing a databaseIf your computer is connected to a network, you and others can work with one Accessdatabase at the same time.There are several ways you can share data in a multi-user environment.Share the entire Access databaseYou can put the entire Access database on a network server or in a shared folder. Thisis the easiest method to implement. Everyone shares the data and uses the sameforms, reports, queries, macros, and modules. Use this strategy if you want everyone touse the Access database the same way or if you can't support users creating their ownobjects.Access Database Design17
Share only the tables in the Access databaseYou can put only the tables on a network server, and keep other database objects onusers' computers. In this case, the Access database's performance is faster becauseonly data is sent across the network.When you split a database into a back end (tables) and a front end, users cancustomize forms, reports, and other objects in their front-end databases withoutaffecting other users. We cover Splitting a Database on the next page.Record LockingWhen you open an Access database file (.mdb) in shared mode, Microsoft Access alsocreates a locking information file (.ldb) with the same file name (for example,Northwind.ldb) and in the same folder as the database file. This locking information filestores the computer name (such as mypc) and security name (such as Admin) of eachshared user of the database. Microsoft Access uses this information to controlconcurrency. In most cases, Microsoft Access automatically deletes the lockinginformation file when the last user closes the database file.In order to setup a shared database you need to instruct Access how to handle ashared database.This is done in the Advanced tab of the Options dialog box.1. On the menu Go to Tools Options.2. Click the Advanced tab in the dialog box.3. Click the Shared radio button in the Default open mode of the dialog box.4. Click the Editedrecord radio button inthe Default recordlocking section of thedialog box.5. Click the OK button toclose the dialog box.18Access Database Design
Splitting a DatabaseFor various reasons, you may decide to split your database into either a front-endapplication or a back-end application. The back-end database contains the tables and isstored on a file server or a shared network drive. The front-end database that links tothe back-end tables contains all the forms, the queries, the reports, the macros, and themodules. The front-end database is distributed to the workstations of the users.Why would you split a database?The following are typical reasons to split a database:1. You are sharing your database with multiple users on a network.2. You do not want your users to be able to make design changes to tables.The most common reason to split a database is that you are sharing the database withmultiple users on a network. You can put only the tables on a network server, and keepother database objects on users' computers. The database's performance is fasterbecause only data is sent across the network. Also, users can customize their forms,reports, and other objects for their individual needs and preferences without affectingother users.How to split a database1. Open the database.2. Choose Database Utilities Database Splitter from the Tools menu.3. Click on Split Database.4. Enter a name for the back-end file of your database.5. Click OK.If you click on the tables tab of your database, you will see that allof the table names now have an arrow next to them. This is a visualindicator that they are linked to data outside of this file.Maintaining a database after it is split.Once a database is split, the “front end” file must be kept in sync with the “back end”file. You must refresh the linked tables if the back end is moved or if the file namechanges.Access Database Design19
Linked Table ManagerTo update the linked tables in a split database1. Go to Tools Database Utilities Linked Table Manager2. Make sure to check the Always prompt for new location check box.3. Choose Select All4. Click OK5. Select the location of your back-end database using the Select New Location ofCategories dialog window.6. Click Open.7. You will then see the followingwindow:20Access Database Design
Environment OptionsThe Access environment options are displayed by selecting the Options command fromthe Tools menu.There are 11 tabs which allow you to control how Access functions and change variousdefault settings.For information about a specific item in the dialog box, click the question mark at the topof the dialog box, and then click the item.Database PropertiesTo display database properties go to the File menu and select Database Properties.You can also right-click in the database window title bar and choose the DatabaseProperties shortcut.There are four tabs which contain different types of information about the database. Thefifth, Custom, allows you to input additional information.For information about a specific item in the dialog box, click the question mark at the topof the dialog box, and then click the item.Access Database Design21
Activity 2: Importing an Access table 1. Open the database SCHOOL2.MDB if it is not already open. 2. Select the Get External Data command from the File menu and click the Import entry. 3. Select the data type of the external file: Access 4. Locate the file named Athletics.mdb and click on the Import button. 5.
