1 / 19

Designing Tables in Microsoft Access

Learn how to design tables and relationships in MS Access efficiently to establish a strong foundation for your database application. Discover essential tips, techniques, and best practices.

sitara
Download Presentation

Designing Tables in Microsoft Access

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Designing Tables in Microsoft Access By Ed Lance

  2. About Me • Independent Database Consultant • Worked with Access since 1.0 • Many years designing, developing, tuning, and developing with databases. • SQL Server and .Net development consulting • http://SanDiegoDataPro.com

  3. Topics • Introduction • Creating Tables • Setting up Relationships

  4. Introduction • This presentation will cover designing tables and relationships in MS Access • Not covering concepts much (i.e. relational DB theory; I have slides on that). • Not covering linking external tables.

  5. IMPORTANT! • Tables and relationships are the foundation of your Access application • It’s vitally important that you get them properly set up BEFORE you work on queries, forms or reports. • Avoids road blocks and kludges • Designing other objects goes faster in Access • You need to have a working design, (normalized) before you begin.

  6. Creating a Table • Several ways to start • Datasheet View: Just start entering data. • Design View: Usual way, gives you all the properties • Table Wizard: Can save some time if you have common data, and help you learn • Tables can get created other ways as well; Make table queries, importing etc.

  7. Table Design View • Allows you to edit the structure of the table and set properties for fields (columns) • Pic here

  8. Adding Fields • Enter Field Name, Data Type and Description • Field Names can contain spaces • Naming Conventions? • Description is optional but is good for ‘self documenting’ applications. (Watch out, shows up in status bar in forms!) • Setting Primary Key

  9. Data Types • Following data types are available: • Text: Character data up to 255 chars • Memo: Up to 64K chars • Number: Integer and Float types • Date/Time: Dates and Time values • Currency: Currency values without rounding loss • Autonumber: Long Int., Access manages • Yes/No: bit value. • OLE Object: Binary objects, e.g. Word, Excel docs • HyperLink: 64000 chars, treated as hyperlink

  10. Field Properties • Properties in the General Tab: • Field Size: Number of chars or size of numeric type • Format: How are those number displayed • Input Mask: For data entry • Caption: Used by form designer • Default Value: If nothing entered in field • Validation Rule/Text: validation expressions • Required: Don’t allow Null values • Allow Zero Length: Allow “” in text fields • Indexed: Is this field indexed • Others: Unicode, IME, Smart Tags

  11. Lookup Tab • Allows you to set the default control that displays the value. • Shows up everywhere. • Can be overridden in forms & reports • For text and number, can use a combo or list box. • For Yes/No, can use checkbox or combo box

  12. Indexes • An Index is like a hidden lookup table on one or more fields. • Can have a huge impact on Select queries • Can slow down inserts & updates • Deciding what fields to index is an art. • At least get Primary Key and foreign keys • Good naming makes this automatic • An index can be on more than one field.

  13. Indexes • Index dialog • Index Name, Field Name, Sort Order • Properties • Primary – Is field part of primary key • Unique – Does the index enforce unique values • Ignore Nulls – Is Null considered a value? • Changing index is changing table design, must save table.

  14. Relationships • Very important! • Setting relationships at design time on tables means Access is responsible for referential integrity, not you… • Also, relationships are recognized automatically when designing queries, forms, reports

  15. Relationship Window • Used to manage relationships and show graphically. • Again, good naming helps you out. • Window is also saves layout of itself, so it will ask you to save.

  16. Relationship Window • Double-click relationship line to get properties • Relationships can be more than one field. • Referential Integrity • It’s a good thing… • Cascading Updates. Not an issue if you always use Autonumber PKs • Cascading Deletes. Useful but dangerous.

  17. Reviewing the Table • After you are done making tables, open them all up and check them out. • Subdatasheets. Do we need these? • Print relationship window for documentation.

  18. Design Tips (IMHO) • Get table design down before you rush into it. • Name the PK field the table name w/o prefix (tblProducts  ProductsID) • Name foreign keys same as the PK they join to. • Always use Autonumber PKs (this started an argument at a dev. convention) • Don’t name fields reserved words. • Use appropriate data types. • Set relationships on tables, enforce referential integrity. • Create indexes, but not too many. Watch for ones Access creates that you didn’t need.

  19. Questions?

More Related