Home

- Pearsoncmg

1. Fed Dae e 5 Form Design Tools Northwind 2007 Database Access 2007 n 7 X Home Create External Data Database Tools Design i Arrange Se H gg aam a da a vi Ii 7 siaine En A 4c GH 19 d 4 peer Pr ny ew oni Gridlines Logo j ae ng Property 7 A la BO ip l E Fields Sheet amp Controls Tools Replace Order Nz Order ID New Create Invoice Ship Order gt Design View Num Lock EEF pt FIGURE 1 11 The design of the Order Details form shows three subforms 20 CHAPTER 1 Access as a Development Tool Reports Turning Data into Information Forms enable you to enter and edit information but with reports you can display infor mation usually to a printer Figure 1 12 shows a report in preview mode To preview any report right click the report in the Navigation Pane and select Print Preview or double click the report you want to preview Notice the colors in the report as well as other details such as the shaded area for the column headings Like forms reports can be elabo rate and exciting yet can contain valuable information Da HY gt Northwind 2007 Database Access 2007 Microsoft Access 8 x Home Create External Data Database Tools in z A z i A5
2. On W s Microsoft Access Table Tools ES Home Create External Data Database Tools Datasheet AeA ayra S lA Gg lt a aa B z uE aby Records at Filter i Size to Switch Find og z 7 7 A gt X z 22 Fit Form Windows we Views Clipboard Font a Rich Text Sort amp Filter Window Find Security Warning Certain content in the database has been disabled Options All Access Objects Tables 2 EJ customers E Customers ID z Company LastName FirstName E mail Addre Customers_ImportErrors m q Company A Bedecs Anna Employee Privileges i _ a 2 Company B Gratacos Solsoi Antonio ja 3 CompanyC Axen Thomas Weld E 4 Company D Lee Christina data ja 5 Company E O Donnell Martin Inventory Transactions E E 6 Company F P rez Olaeta Francisco Invoices 19 pti B 7CompanyG Xie Ming Yang Order Details 5 E 8 Company H Andersen Elizabeth ET this q E Company Mortensen Sven hidez from 0 Company J Wacker Roland jm 11 CompanyK Krschne Peter Orders Status E 12 CompanyL Edwards John Orders Tax Status l IE 13 Company M Ludick Andre Record 4 4 1of29 gt m H YK NoFilter Search 4 fa Employees Inventory Transaction Types Privileges The exa Products people places and events depicted herein are fictitious No association with any real 4 HHHBBEHHReeaee Tah Num Lock mB FIGURE 1 43 After you close and re
3. i Home Create External Data Database Tools Tim EHT oa ESI A fe Al TI l View Paste P aby Records A Filter Find R lt Ciac lt Views Clipboard Sort amp Filter l Find Tables Customers Customers_ImportErrors Employee Privileges Employees Tnventary Transaction Types Welcome to the Northwind Traders sample Inventory Transactions rice database In order to use this sample click Order Detalls Options on the Message Bar and select Enable Order Details Status this content Alternatively open the database from a Trusted Location Orders Orders Status Orders Tax Status Privileges Products 1HH AHHH aa ee The example companies organizations products domain names e mail addresses logos people places and events depicted herein are fictitious No association with any Form View Num Lock FIGURE 1 44 You can modify the view that you are working with by clicking the appropriate tool on the status bar Northwind 2007 Database Access 2007 Microsoft Access z es Filter Y Database Tools eam a pecatas A External Data Find A ay is Sort amp Filter Views Clipboard amp C Customers Customers_ImportErro
4. La Popular E j Options for the current database current Database Datasheet Application Options Object Designers Application Title Proofing Application Icon Browse I Use as Form and Report Icon Display Form inone gt I Display Status Bar Add ins Document Window Options Overlapping Windows Tabbed Documents Resources I Display Document Tabs M Use Access Special Keys I Compact on Close IT Remove personal information from file properties on save W Use Windows themed Controls on Forms M Enable Layout View for this database M Enable design changes for tables in Datasheet view for this database Vv Check for truncated number fields Picture Property Storage Format e Preserve source image format smaller file size C Convert all picture data to bitmaps compatible with Access 2003 and earlier Advanced Customize Trust Center Navigation Display Navigation Pane Navination Antiane j4 FIGURE 1 41 The Current Database options affect the behavior of a specific database 46 CHAPTER 1 Access as a Development Tool 6 Close and reopen the database to see the changes take effect Your screen should now appear as in Figure 1 42 Notice that no tabs appear under the ribbon Cy eS contacts database Database Acces Table Tools xX Home Create External Data Database Tools Datasheet bs 2 ae Property Sheet ES ay i EY Switchboard Manager
5. Run Permissions Source Database Source Connect Str cks Max Records Orientation SubdatasheetName Link Child Fields Subdatasheet Height _ Subdatasheet Expandec the query Customers Database Access 2007 Beta Microsoft Access xX Home Create External Data Database Tools Z i omn oa a at Y Yz al Vi an E ENEAN ere REOTA l Filter a Find jew ste 2 F IATa ox Y lt Views Clipboard Sort amp Filter Northwind Objects w Que Ges Customer Orders Subtorm P orderiD Employees Employee Details 30 Freehafer Neipper Hellung Larsen Employee List 31 Cencini Zare J Employee Orders Subform 32 Neipper Hellung Larsen A Order List 33 Northwind Reports 34 Northwind Tables 35 do Customers 36 dol Employees 37 gol Employees 38 gol Order Details 39 40 a Orders 41 gol Orders Status t do Product oa roducts t 43 gol Products1 a gal Shippers 45 gal Suppliers 46 gt al Suppliers y Record i4 4 30F46 gt M bs XK No Filter Search Num Lock EEE FIGURE 1 77 The results appear with all the selected items in one column Additional Tips and Tricks 71 A Customers Database Access 2007 Query Tools a Create External Data Database Tools Design eS e yA Fei i P pecora bx Filter Z a4 d Mee Views Clipboard Sort
6. 3 Move your mouse pointer away from the mini toolbar The mini toolbar fades away and the text appears with the selected formatting see Figure 1 48 a Contacts database Database Acces Table Tools ox Home Create External Data Database Tools Datasheet 2 Calibri F F al al Waa i iI OAIE E n wo a Records rites e View Paste 7 a D z cords hy cae in Views Clipboard Rich Text Sort amp Filter Security Alert Due to your security settings certain content in the database has been disabl Enable Content Trust Center Z All Access Objects Z Contacts Tables 4 P state Provin Z1P Postal C Country Reg z WebPage Notes z O Contacts CA 93012 sa www infotech Thisis some gex that wa Queries fiz Contacts Extended Forms E contact Details amp contact List Reports W contact Address Book I contact Phone List Cu EECA gt Record M 4 1of1 p m WkNoFilter Search 4 Datasheet View Num Lock FIGURE 1 48 Notice that the word text in the Notes field is bold and italic What s New in Access 2007 51 NOTE If you don t want to apply formatting to a selection simply move your mouse pointer a few pixels away from the toolbar and the mini toolbar disappears NOTE You can apply formatting only in specific situations such as within a Memo fiel
7. 5 After you add all the desired commands click OK to complete the process The Quick Access toolbar now appears with the icons associated with the commands that you added to the toolbar see Figure 1 35 TIP If you want to reset the Quick Access toolbar to its default state click the Reset button on the Customization page of the Access Options dialog box Access Options Popular R Customize the Quick Access Toolbar Current Database Choose commands from Dateiheet Create Tab Object Designers lt Separator gt Advanced Blank Form Blank Report J Business Form Wizard Form Forms Labels 5 More Forms Multiple Items New Object Class Module New Object Diagram New Object Form New Object Macro New Object Module New Object Query J New Object Report New Object Stored Procedure New Object Table Other Proofing Advanced Customize Add ins Trust Center Add gt gt Remove Resources I Show Quick Access Toolbar below the Ribbon What s New in Access 2007 41 71x Customize Quick Access Toolbar For all documents default Save Undo Redo New Object Form New Object Macro New Object Query Reset Modify Cancel a FIGURE 1 34 After you select Add the command appears in the list box on the right side of the dialog box CI nyrirstaccess2007Data Table Tools ox teate External Data Database Tools Datasheet Lina t
8. EA Relationship a X Clear Layout a it Edi Relationships Tools Security Warning Certain Report 35 Direct Relationships ow Table BFAI Relationships Relationships 5 Hide Table B Sh Close content in the database has been disabled Options a Relationships ox a tbiClients tiClientAddr solute H Y Client T Address a tbiada tblcorrespon Corresp a a CompanyN Client a 7 Correspond ClientiD f ContactFirs Address A Correspond Descript ContactLas Address Notes ContactTitl ay v DateSer w Contactiyp 2 pained tbiContactType tolPhoneTypes thiclientPhones baat Y ContactTyp z Phonetype T PhonelD nah ContactTyp S aces DefaultRat PhoneType n None B PhoneNum toirerms A PhoneType Miles a erms z J TermTypelD w TermTypelD thiPaymentMe Oeann TermType PaymentMe 7 wy 6 ROIETOJECS tbitimecardH PaymentMe ethok cy T Project a 9 TimecardD f thitimecards CreditCard A Project TimeCardID j T TimeCardiD pane ars lo oa DateWorke Employeeltt lent Creditc Durchaele Pre Dehelinters M lt i gt Ready Num Lock FIGURE 1 80 Here are the relationships among tables in the time and billing system 74 CHAPTER 1 Access as a Development Tool Summary Before you learn about the practical aspects of Access development you need
9. To select each field you must click it and then click the greater than button gt Click Next Lookup Wizard This wizard creates a lookup column which displays a list of values you can choose from How do you want your lookup column to get its values iI want the lookup column to look up the values in a table or query C will type in the values that I want Cancel lt Back Next gt Finish FIGURE 1 69 The Lookup Wizard assists with the process of creating a multi value field Lookup Wizard Which table or query should provide the values for your lookup column Table Customers_ImportErrors Table Customers2 Table CustomersHTML Table Employee Privileges Table Inventory Transaction Types Table Inventory Transactions Table Invoices xi View E Tables C Queries C Both Cancel lt Back Next gt Finish Dema eee nes e FIGURE 1 70 Select the table that you will use to populate the list Lookup Wizard Which fields contain the values you want included in your lookup column The fields you select become columns in your lookup column Available Fields Selected Fields E mail Address FIGURE 1 71 Select the fields that you want to include in your lookup 68 CHAPTER 1 Access as a Development Tool 7 Designate the sort order for the items in the list see Figure 1 72 Click Next 8 Designate the width of e
10. Access 2007 Microsoft Access ox a Za Home L Create External Data _ Database Tools z n B het 2 Sr Ye fae S z A ie if a View Paste z SEZ Records p Filter 7 Find Re Views Clipboard a Rich Text Sort amp Filter Find Security Warning Certain content in the database has been disabled Options x All Access Objects 3 Startup Screen Tables S E customers 3 Customers _ImportErrors _INO A a Ch a a Employee Privileges a a Employees f E inventory Transaction wees Welcome to the Northwind Traders sample E Inventory Transactio A ao database In order to use this sample click E order Details Options on the Message Bar and select Enable SPERTS this content Alternatively open the database so Orders T from a Trusted Location E orders Tax Status a Privileges E Products The example companies organizations products domain names e mail addresses logos E Purchase Order Details people places and events depicted herein are fictitious No association with any real a eee company organization product domain name email address logo person places or FIGURE 1 46 After you close and reopen the database the status bar no longer appears Working with the Mini Toolbar Microsoft Office Access 2007 offers many text formatting features In earlier versions of Access formatting text required using a menu or displaying the formatting toolbar The mini toolbar enab
11. Access 2007 and other applications such as Excel and SharePoint A H Mr Mon 3 Table Tools Chapter1 Database Access 2007 Microsoft Acc ox E eee Treen j Home Create External Data Database Tools Datasheet a SF Property Sheet E EE i pis EY switchboard Manager 8 EY Z Objet Dependencies 5 G7 Encrypt with Password Visual Relationships SQL Access linked Table a Make Basic 2 Message Bar GB server Database Manager fa Add ins ACCDE Macro Show Hide Analyze Move Data Database Tools All Tables Tables Table a ID Add New Field _ E tablet Table New J Record 4 aiot M gt WNoFilter Seah Datasheet View Num Lock E FIGURE 1 23 The Database Tools tab enables you to perform miscellaneous database related tasks 34 CHAPTER 1 Access as a Development Tool Exploring the Contextual Command Tabs Other tabs are contextual and therefore vary depending on what you are doing For example when you first create a new database Access assumes that your first task will be to create a new table It places you in Datasheet view and the Datasheet tab appears see Figure 1 24 This tab enables you to perform all tasks relating to the process of working with a datasheet These tasks include working with fields and columns modifying the data type and formatting associated with a column and working with relationships I cover eac
12. When you have open forms reports and other objects they appear as tabs on the ribbon see Figure 1 39 You can easily move from object to object by simply clicking each tab Notice in Figure 1 39 that three objects are open Contact List Contacts Extended and Contact Address Book The Contact List form is currently the active tab mS eo Contacts database Database Access 2007 Beta Microsoft Access x Home Create External Data Database Tools View EB Views Clipboard Font Security Alert Due to your security settings certain content in the database has been disabl Enable Content Trust Center All Access Objects vy Tables a amp contacts Queries 2 ew Contaci Collect Data via Email Add From Outlook E mailList Print List E Contacts Extended ID LastName FirstName E mail Address x Business Phone Forms 2 i E contact Details New E contact List Total 0 Reports 2 I Contact Address Book i contact Phone List Record 4 1of1 gt Ms XK NoFilter Search 4 gt Form View Num Lock EEEL FIGURE 1 39 Each open document appears as a tab on the ribbon Showing or Hiding Document Tabs If you prefer the older style of either viewing only one object at a time or of overlapping windows that appear on the taskbar you can change the behavior of Acc
13. see Figure 1 64 A new group appears in the Navigation Pane see Figure 1 65 Ss woe Customers Database Access 2007 Beta Microsoft Access 7x Home Create External Data Database Tools BP Access CB Text File i zeg A Ghword F G synchronize Ve N gx Gi HA s seh fag Excel B XML File Baw esate a aE Fi tet File a ae rie 5 Reconnect All e a ve cel SharePoin pa eate Manage Moveto 3 Imports nr SharePoint List More Exports List or XPS i More E mail Replies SharePoint wy Discard Changes Import 3j Export J Collect Data SharePoint Lists Customer Orders ifs Open Employee Details E Layout View Employee List 24 Design View Employee Orders Export P E Order List Rename Shortcut Hide in this Group Remove Cut a Copy Paste Add to group gt Northwind Forms View Properties Northwind Reports Northwind Tables New Group Ready Caps Lock Num tock FIGURE 1 64 You can right click an object and immediately add it to a new group 3 Enter a name for the new group 4 Notice that the object you selected appears in the new group Drag additional short cuts to the group as desired In addition to what you have learned thus far you can also show or hide the groups and objects in a category In fact you can show or hide some or all of the groups in a custom category and some or all of the objects in a group There are some important
14. Because Access 2007 ships with an integrated data store the SQL Server 2005 Express Database Engine you can develop a client server application on the desktop and then easily deploy it to an enterprise SQL Server database Chapter 22 briefly covers the alter natives and techniques for developing client server applications Alison Balter s Mastering Access 2002 Client Server Development provides details on how to develop Access projects When you reduce the volume of network traffic by moving the processing of queries to the back end Access becomes a much more powerful development solution It can handle huge volumes of data and a large number of concurrent users The main issues usually faced by developers who want to deploy such a wide scale Access application are the following gt The variety of operating systems used by each user gt Difficulties with deployment gt The method by which each user is connected to the application and data gt The type of hardware each user has Although processing of queries in a client server application is done at the server which significantly reduces network traffic the application itself still must reside in the memory of each user s PC This means that each client machine must be capable of running the appropriate operating system and the correct version of Access Even when the correct operating system and version of Access are in place your problems are not over Dynamic link library DLL c
15. Collect and Update Data via E mail Reports Rename E con Hide in this Group con Delete Cut Copy Paste Linked Table Manager i Hl fe wy x Table Properties XK No Filter Search lt Ga gt Record I4 4 1of1 gt Pl be FIGURE 1 37 After you right click a table the context sensitive menu enables you to perform functionality associated with a table i Contacts database Database Acces Table Tools 22X External Data Database Tools Datasheet armea AL Records bx Filter Ene Sort amp Filter es View Paste F Views Clipboard _ Company FirstName LastName E mail Address E contacts Queries 2 g Contacts Extended Forms a E contact Details ree Open H Layout View Design View Export gt Rename Hide in this Group Delete Reports contact Addres contact Phone amp cut Ga Copy By Paste Ey View Properties lt fiof1 Jhr WK No Filter FIGURE 1 38 After you right click a form the context sensitive menu enables you to perform functionality associated with a form 44 CHAPTER 1 Access as a Development Tool Working with Tabbed Documents In Microsoft Office Access 2003 all open documents forms reports and so on appeared on the taskbar Microsoft has replaced this paradigm with that of tabbed documents
16. Project ADP is also no longer supported in Microsoft Office Access 2007 again unless you keep your database in the old Access file format Although supported with the old Access file format it is probably best that you do not do any new develop ment with ADP files If you have existing ADP files that are currently meeting your busi ness needs you don t need to rewrite them at this time If you decide at some point to make major changes to those existing applications that is when you should consider moving them to the new accdb or accde file format and rewriting their functionality as necessary to take advantage of the new features available in Microsoft Office Access 2007 and eliminating the features specific to ADP files Additional Tips and Tricks There are a few additional tips and tricks that you should be aware of when working with Microsoft Office Access 2007 They include advanced Navigation Pane techniques and the process of working with multi valued fields The following sections discuss each of these topics in detail Advanced Navigation Pane Techniques Microsoft Office Access 2007 sports some wonderful Navigation Pane techniques that you should be aware of These include the capability to create custom categories and groups show or hide the groups or objects in a category and remove and restore objects in custom groups Let s start with the process of creating custom categories Here are the steps involved 1 Right cli
17. What s new in Access 2007 The new Access 2007 contains more powerful FIGURE 1 18 You must select a name and a location for the database 30 CHAPTER 1 Access as a Development Tool Ca eS MyFirstAccess2007Database Databa Table Tools X cs Home Create External Data Database Tools Datasheet bZ FW sA e i Insert Data Type E unique ss f eee ae E z Y Delete Format Formatting E Is Required coj View New Add Existing Lookup TE Relationships Object Field Fields Column Rename a 0 Dependencies Views Fields and Columns Data Type and Formatting Relationships All Tables Tablet Table1 2 ID Add New Field Tablet Table New Recora i lofi WK No Filter Search Datasheet View Num Lock E FIGURE 1 19 Access 2007 includes a new tabbed interface Notice that Microsoft Office Access 2007 provides you with a tabbed interface see Figure 1 19 When you create a new blank database Access 2007 provides you with a new datasheet so that you can create the first table contained in the database You can use this technique to create a table or you can create a table in Design view Notice that under neath the tabs is what looks like a fancy toolbar Microsoft refers to this toolbar as the ribbon The next section Getting to Know the Ribbon goes into the details of the ribbon In the sections that follow
18. amp Filter ER Home Northwind Objects w e Property Sheet x esl Customer Orders Subform a Selection type Field Employee Detail dasr RL mployee Details 9 Order ID a General Lookup Employee List E Employees A Description Employee Orders Subform Employees Value Fo Employee ID Input Mask Order List Customer ID Northwind Reports Order Date Northwind Tables Ane uate x Shinnar ID Customers Employees Employees1 p r Field Order ID tEm loyees Value Order Details Orders Orders Orders m Orders Status Products Productsl Shippers Suppliers hHE DEERE ED Suppliers1 Num Lock j Saad Customers Database Access 2007 Beta Microsoft Access 7 X Home Create External Data Database Tools an ae r ai See eh S Wa Vii nA Record al Filt a Find jew ste cords er n gt ae A ox Vv S Views Clipboard amp Font G Rich Text Sort amp Filter Northwind Objects geal Customer Orders Subform a Orders Empl A Employee Details il 1 Employee List 30 9 d Employee Orders Subform 30 6 Order List 31 2 31 7 32 9 6 Customers 32 Employees Employeesi Order Details Orders Orders Status Products Productsl Shippers Suppliers HHEDERGEDED Suppliers1 FIGURE 1 7
19. an example of a table that hasn t been normalized Notice that the CustInfo field is repeated for each order so if the customer address changes it has to be changed in every order assigned to that customer In other words the CustInfo field is not atomic If you want to sort by city you re out of luck because the city is in the middle of the CustInfo field If the name of an inventory item changes you need to make the change in every record where that inventory item was ordered Probably the worst problem in this example involves items ordered With this design you must create four fields for each item the customer orders name supplier quantity and price This design would make it extremely difficult to build sales reports and other reports your users need to effectively run the business 26 CHAPTER 1 Access as a Development Tool Din bed 2D Table Tools Northwind 2007 Database Access 2007 Micros 7 X a Home Create External Data Database Tools Datasheet J z mala Ye ab z i A Calibri u CERSEI 24 u vi E E pale A Filt Sl ena jew e g ecords er ni fe y A274 49 v R Views Clipboard Font m Rich Text Sort amp Filter Find Orderic z Custinfo OrderTota OrderDate Item1Nam Item1Suppli X Item1Qu gt Item1Prict Item2Name M123 Any Street 350 00 11 15 2006 Widget Good Supplier 5
20. ayy Calibri papel Al 2 A x a an prenes a pecoras A a Find vy y eE ox y l g Sort amp Fitter Views Clipboard ta All Tables Table1 Tablet Table Add New Field New ABC Company re K No Filter Record 4 4 1of1 b Search Datasheet View Num Lock mae FIGURE 1 35 After you add three commands to the Quick Access toolbar they appear next to the existing toolbar buttons 42 CHAPTER 1 Access as a Development Tool Getting to Know the Navigation Pane Microsoft has replaced the Database window with the Navigation Pane The Navigation Pane contains the names of all the objects in your database including the forms reports pages macros and modules that compose your database In Figure 1 36 you can see that the Contacts database is composed of one table one query two forms and two reports His Contacts database Database Access 2007 Beta Microsoft Access xX Home Create External Data Database Tools ie a EE 5 R Ale pier ee View Paste ecords ilter Fin s se gy a S Bx li Views Clipboard Font g Rich Text Sort amp Filter All Access Objects Contact List EE amp Contact List Queries New Contact Collect Data via Email Add From Outlook E mail List Print List E Contacts Exten
21. client server database In case you re lost here s an explanation If you buy Access and develop an application that stores the data on a file server in an Access database the workstation performs all data processing This means that every time the user runs a query or report the file server returns all the data to the workstation The workstation machine then runs the query and displays the results in a datasheet or on a report This process generates a significant amount of network traffic particularly if multiple users are running reports and queries at the same time on large Access tables In fact such operations can bring the entire network to a crawl 10 CHAPTER 1 Access as a Development Tool Access as a Front End for Enterprisewide Client Server Applications A client server database such as Microsoft SQL Server or Oracle processes queries on the server machine and returns results to the workstation The server software itself can t display data to the user so this is where Access comes to the rescue Acting as a front end Access can display the data retrieved from the database server in reports datasheets or forms If the user updates the data in an Access form the workstation sends the update to the back end database You can accomplish this process either by linking to these external databases so that they appear to both you and the user as Access tables or by using tech niques that access client server data directly
22. from the Navigation pane drop down choose the query you want to modify and right click and select Design The query pictured in the figure selects data from Purchase Orders Purchase Orders Status and Purchase Price Totals tables and queries Note that you can base queries on tables and on other queries It displays the Creation Date Supplier ID Shipping Fee Taxes and several other fields from the Purchase Orders table the Status from the Purchase Order Status table and the Sub Total expression from the Purchase Price Totals query Chapter 4 What Every Developer Needs to Know About Query Basics and Chapter 12 Advanced Query Techniques both cover queries Because queries are the foundation for most forms and reports I cover them throughout this book as they apply to other objects in the database Forms A Means of Displaying Modifying and Adding Data Although you can enter and modify data in a table s Datasheet view you can t control the user s actions very well likewise you can t do much to facilitate the data entry process This is where forms come in Access forms can take on many traits and they re very flexi ble and powerful 18 CHAPTER 1 Access as a Development Tool Cn id M M s QueryTools Northwind 2007 Database Access 2007 Micros X Home Create External Data Database Tools Design 9 E ATW taal Ja g Insert Columns gt Sf Property Sheet ee nto l 3X YW Delete Colum
23. r a Ae TE Object Dependencies Ez es GE Encrypt with Password Visual Relationships SQL Access Linked Table Make Basic I Message Bar GA server Back End Manager Add Ins ACCDE Macro Show Hide Analyze Move Data Database Tools All Access Objects SA ID Company FirstName LastName E mailAddress Tables a q E Contacts New Queries a g Contacts Extended Forms a E contact Details ES contact List Reports a I Contact Address Book I contact Phone List Record 4 a 1of1 gt hs No Filter Search a gt Datasheet View Num Lock Be ae FIGURE 1 42 After you close and reopen the database no tabs appear under the ribbon Displaying Overlapping Windows Another option is to display overlapping windows Here are the steps involved 1 Click the Microsoft Office button Select Access Options The Access Options dialog box appears Click Current Database Click Overlapping Windows to select it Click OK to close the dialog box D a F YO N Close and reopen the database to see the changes take effect Your screen should now appear as in Figure 1 43 Notice that no tabs appear under the ribbon NOTE The Display Documents Tabs setting is a per database setting You must modify this setting for each database New databases created using Access 2007 show document tabs by default Databases created in earlier versions of Access use overlapping windows by default What s New in Access 2007 47
24. table described later in this chapter You can modify many of the datasheet s attributes and even search for and filter data from within the datasheet If the table is related to another table such as the Northwind Customers and Orders tables you can also expand and collapse the subdatasheet to view data stored in child tables 14 CHAPTER 1 Access as a Development Tool This book does not cover these techniques You can find them in the Access user manual or any introductory Access book such as Sams Teach Yourself Microsoft Office Access 2007 in 24 Hours r 7 Qa EA gt Table Tools Northwind 2007 Database Access 2007 Micro 5 X gt Home Create External Data Database Tools Datasheet BZ Calibri Jif aa Y Ye A 2b a ee ee Se ee eee et ree See MOR a a2 tees A EIS 4 Views Clipboard Font fa Rich Text Sort amp Filter Find customers ID Company LastName FirstName E mail Address Job Title z Busine m 1 CompanyA Bedecs Anna Owner 123 5 2 Company B Gratacos Solsoi Antonio Owner 123 5 8 3 Company C Axen Thomas Purchasing Representati 123 54 B 4 Company D Lee Christina Purchasing Manager 123 5 m 5 Company E O Donnell Martin Owner 123 5 jm 6 Company F P rez Olaeta Francisco Purchasing Manager 123 5 7 Company G Xie Ming Yang Owner 123 5 8 CompanyH Andersen Elizabeth Purchasing Representa
25. the orders details associated with that order When the user clicks to select the Shipping Information and Payment Information tabs she can see additional information about that order As with tables and queries you can also view forms in Design view To view the design of a form right click the Form from within the Navigation Pane and select Design Figure 1 11 shows the Order Details form in Design view Notice the three subforms within the main form Chapter 5 What Every Developer Needs to Know About Forms and Chapter 10 Advanced Form Techniques officially cover forms I also cover forms throughout this text as they apply to other examples of building an application Getting to Know the Database Objects 19 ES Order Details Grder Delete Order ingoia Saig oeer eona Customer Salesperson Anne Hellung Larsen B M ne Hellung Larse H E mail Address Order Date 1 15 2006 2 Order Details Shipping Informetion Payment information Products z Qty Unit Price Discount Total Price Status Northwind Traders Beer 100 14 00 0 00 1 400 00 Invoiced Northwind Traders Dried Plums 30 3 50 0 00 105 00 Invoiced Total 130 1 505 00 Record 4 4 1of48 M XK No Filter FIGURE 1 10 The Order Details form includes customer order and order detail information
26. tie Ss uia C YF Delete Format Formatting E 1s Required Ss View New Add Existing Lookup TEk Relationships Object Field Fields Column E Rename 9 fs8 38 Dependencies Views Fields and Columns Data Type and Formatting Relationships Northwind Objects 2 orders am CUSOmEr Siders SUOTA 1A OrderID Employees Employee Customer OrderDate Shippe geal Employee Details E 30 Els 27 1 15 2006 geal Employee List 31 O cencini Andrew 4 1 20 2006 J e Employee Orders Subform 32 Freehafer Nancy 12 1 22 2006 J E Order list 33 a bate Laura 8 1 30 2006 Northwind Reports z 34 Hellung Larser Anne 4 2 6 2006 Kotas Jan Northwind Tables a 35 o i x 29 2 10 2006 4 Neipper Michael gol Customers m 36 Sergienko Mariya 3 2 23 2006 gal Employees H 37 Thorpe Steven 6 3 6 2006 dal Employeest 38 Zare Robert 28 3 10 2006 gol Order Details 39 8 3 22 2006 OK Cancel eal Orders m 40 10 3 24 2006 41 1 7 3 24 2006 dol Orders Status 1 24 42 1 10 3 24 2006 dol Products 43 1 11 3 24 2006 ei Productst a4 1 1 3 24 2006 al Shippers 45 1 28 4 7 2006 Suppliers 46 7 3 4 5 2006 q gal Supplierst y Record 4 4 10f46 gt mh YK NoFilter Search 4 Ga Datasheet View Num Lock EEE FIGURE 1 68 You can easily select multiple items in a multi valued field 66 CHAPTER 1 Access as a Development Tool Multi valued fields are appropriate for specific situations One of those situations is when you are u
27. to the group You can drag the items individu ally hold down the Ctrl key and click and drag multiple items or right click one of the selected items point to Add to Group and then click the name of the custom group Regardless of the method Access adds the objects to the designated group NOTE When you add a database object from the Unassigned Objects group to a custom group you are creating a shortcut to the object If you remove the object from the custom group you are not removing the object Instead you are removing the shortcut contained in the custom group Additional Tips and Tricks 61 Hiding the Unassigned Objects Group After you have added all your objects to custom groups you might want to hide the Unassigned Objects group The process is quite simple 1 Right click the menu at the top of the Navigation Pane and select Navigation Options The Navigation Options dialog box appears 2 Click to select a category for example Northwind Objects 3 In the Groups for Category pane see Figure 1 62 click to clear the Unassigned Objects check box 4 Click OK to close the dialog box The Unassigned Objects group no longer appears see Figure 1 63 I 2 r Grouping Options Click on a Category to change the Category display order or to add groups Categories Groups for Northwind Objects Northwind Forms M Northwind Reports M Northwind Tables Tables and Related Views Clear check box to hi
28. trusted Macros including embedded macros are covered in Chapter 7 What Are Macros and When Do You Need Them To run a macro select Macros from the Navigation Pane right click the macro you want to run and then click Run Access then executes the actions in the macro To view a macro s design right click the macro in the Navigation Pane and select Design View The macro pictured in Figure 1 13 has four columns The first column enables you to specify a condition The action in the macro s second column won t execute unless the condition for that action evaluates to True The third column shows you the arguments for that line of the macro and the fourth column lets you document the macro In the bottom half of the Macro Design window you specify the arguments that apply to the selected action In Figure 1 13 the selected action is OpenForm which accepts six arguments Form Name View Filter Name Where Condition Data Mode and WindowMode 3x Da iain Macro Tools Northwind 2007 Database Access 2007 Microso Home Create External Data Database Tools Design S Single Step Ja Insert Rows B XYZ Taj p N Builder Z Delete Rows Ba i Run Show All Macro Conditions Arguments Actions Names Tools Rows Show Hide AutoExec Condition Action Arguments Comment SetDisplayedCategories Yes Northwind Traders EI Not CurrentProject IsTruster OpenForm a Startup Screen For
29. version of Access to the Access 2007 file format Access 2007 will strip all user level security settings from the database and Access 2007 security will apply You will learn much more about security in Chapter 31 Database Security Made Easy What Happened to Replication Replication is not supported in Microsoft Office Access 2007 unless you keep your data base in the old Access file format If you open an existing mdb file where replication has already been implemented the replication will be supported You can also use Access 2007 to replicate a database created in an earlier version of Access as long as you do not convert that database to the new file format You will not be able to convert a replicated database to the Access 2007 file format However there is a solution which involves manually re creating the database in the Access 2007 file format You should do this only if you feel that the benefits afforded by the Access 2007 file format outweigh the benefit received from replication If you do decide to manually re create the database you must first make sure that all hidden and system objects are available Then do the following 1 Open the replica that you want to convert using the same version of Access in which you created it Select Tools Options Click the View tab The Options dialog box appears as in Figure 1 49 In the Show section select Hidden Objects and System Objects a F YW N Click OK to apply
30. vi Pate bZ Record Al Filt iey Find ee iew aste s 7 ecords ilter in i 7 z 5 7 Views Clipboard Font a Rich Text Sort amp Filter Find All Access Objects x Tables Queries Wednesd Forms z Quarterly Sales Report Reports 2 a2 2006 E Customer Address Book E Customer Phone Book Northwind Traders Syrup 500 00 0 00 0 00 47 E Employee Address Book Northwind Traders Cajun Seasoning 0 00 0 00 0 00 Employee Phone Book 1 Northwind Traders Olive Oil 533 75 50 00 50 00 f Invoice F Northwind Traders Boysenberry Spre 0 00 0 00 0 00 OOD Seles HERO Northwind Traders Dried Pears 0 00 0 00 0 00 E IE Product Category Sales by Northwind Traders Curry Sauce 1 120 00 800 00 0 00 1 Product Sales by Category Northwind Traders Fruit Cocktail 0 00 0 00 0 00 E Product Sales by Total Rev Northwind Traders Chocolate Biscuit 230 00 0 00 0 00 f E Product Sales Quantity by Northwind Traders Marmalade 3 240 00 0 00 0 00 3 E Quarterly Sales Report L_ Northwind Traders Scones 200 00 0 00 0 00 E Supplier Address Book Northwind Traders Beer 5 418 00 0 00 0 00 5 E supplier Phone Book Northwind Traders Crab Meat 1 472 00 736 00 0 00 2 Gl Top Ten Biggest Orders vilaf m gt Report View Num Lock IERE A FIGURE 1 12 This preview of the Quarterly Sales Report displays information in the report If you haven t guessed yet you can view reports in Design view as shown in Figur
31. your settings and close the Options dialog box Other New Features Found in Access 2007 53 FIGURE 1 49 The Options dialog box allows you to view hidden and system objects Re Creating the Database Next you must manually re create the database Here s how 1 Create a blank Access 2007 database and open it 2 Close the table called Table1 without saving it 3 Click the External Data tab see Figure 1 50 japter1 Database Access 2007 Microsoft Access External Data Options FIGURE 1 50 You use the External Data tab to import and export data 54 CHAPTER 1 Access as a Development Tool 4 In the Import group select Access The Get External Data Access Database dialog box appears see Figure 1 51 Get External Data Access Database 21x Select the source and destination of the data Specify the source of the data Eile name c DaTA Clients sams Mastering Access 2007 Desktop Development Browse Specify how and where you want to store the data in the current database Import tables queries forms reports macros and modules into the current database If the specified object does not exist Access will create it If the specified object already exists Access will append a number to the name of the imported object Changes made to source objects including data in tables will not be reflected in the current database C Link to the data source by creating a link
32. z Modules x _Ready i Num Lock FIGURE 1 1 The Navigation Pane displays categories for each type of database object Tables A Repository for Your Data Tables are the starting point for your application Whether your data is stored in an Access database or you are referencing external data by using linked tables all the other objects in your database either directly or indirectly reference your tables To view all the tables that are contained in the open database select Tables from the Navigation Pane drop down as shown in Figure 1 2 Note that you won t see any hidden tables unless you have checked the Hidden Objects check box in the Navigation Options dialog box as shown in Figure 1 3 If you want to view the data in a table double click the name of the table you want to view Getting to Know the Database Objects 13 Chap24Ex Database Access 2007 Microsoft Access 1 Security Warning Certain content in the database has been disabled iptions Tables and Related Views Created Date Modified Date Queries Forms Reports Modules All Access Objects Customer name order d FIGURE 1 3 The Navigation Options dialog box allows you to show hidden tables Access displays the table s data in a datasheet which includes all the table s fields and records see Figure 1 4 Note that I have collapsed the Navigation Pane so that you get a better view of the
33. 06 Northwind Tables O Neipper Michael 10 gal Customers 36 Sergienko Mariya 3 2 23 2006 gal Employees B 37 Thorpe Steven 6 3 6 2006 aE Employeesi 38 O zare Robert 28 3 10 2006 39 8 3 22 2006 a Order Details OK Cancel oo Orders 40 10 3 24 2006 41 1 7 3 24 2006 aol Orders Status 42 1 10 3 24 2006 aol Products 43 1 11 3 24 2006 do Productst 44 1 1 3 24 2006 a shippers 45 1 28 4 7 2006 al Suppliers 46 7 3 4 5 2006 al gol Suppliers y Record 4 10f46 gt D gt K No Filter Search 4 Eu gt Datasheet View Num Lock Emer FIGURE 1 75 The completed multi valued field enables you to select multiple items from the list Exploring the Effect of Multi valued Fields on Queries There are a couple of issues that you need to know about multi valued fields when working with queries To illustrate these items follow these steps 1 Open a database and click the Create tab 2 Select Query Design from the Other group The Show Table dialog box appears 3 Select the table containing the multi valued field and click Add 4 Click Close to close the Show Table dialog box Your screen should appear as shown in Figure 1 76 5 Click and drag the desired fields to the query grid Make sure you select the multi valued field 6 Click Run in the Results group The results appear as in Figure 1 77 Notice that the multi valued field appears with all the selected items in one column separated by commas As an alternative you c
34. 50 00 Gadget Anywhere CA al 2 123 Any Street 175 00 11 18 2006 Nut Some Supplier 3 0 15 Nail Anywhere CA 3 23 Any Street 35 00 12 20 2006 Bolt Another 7 0 75 Telescope M Anywhere CA Supplier Z 4 23 Any Street 75 00 1 13 2007 Pencil Some Supplier 25 1 20 Ski Poles 2 Anywhere CA a 5 45 Another 51 00 1 15 2007 Eraser Another 30 0 90 Saddle Street Supplier Somewhere 6 45 Another 9 245 00 1 28 2007 Computer Good Supplier 4 1 527 00 Bolt Street Somewhere New Record 4 4 1of6 gt Mb MG NoFilter Search 4 gt Ready Num Lock TEOR FIGURE 1 15 This table hasn t been normalized Figure 1 16 shows the same data normalized Notice that I ve broken it out into several different tables tolCustomers tblOrders tblOrderDetails and tb1Suppliers The tb1Customers table contains data that relates only to a specific customer eb D gt e s Relationships thiCustomers CustomerID CompanyName Address City State tblOrders 9 OrderD CustomerID OrderDate Navigation Pane Relationship Tools Normalization Database Access 2007 Mic ae Home Create External Data Database Tools Design X Clear Layout Hide Table ba EA Relationship Report BA Direct Relationships Edit Show X Close Relationships Table B2 Al Relationships tblOrderDetails 9 OrderD F LineNumber ItemName SupplierID QuantityOrdered tblSuppliers ax 9 S
35. 9 Each employee appears in a different row for each order 72 CHAPTER 1 Access as a Development Tool Practical Examples The Application Design for a Computer Consulting Firm Consider a hypothetical computer consulting firm that wishes to track its time and billing with an Access application First look at the application from a design perspective The system will track client contacts and the projects associated with those clients It will enable the users to record all hours billed to and expenses associated with each client and project It will also let users track pertinent information about each employee or subcontractor I have based the tables in the system on the tables produced by the Database Wizard I modified them somewhat and changed their names to follow the Reddick naming conventions Twenty one tables will be included in the system You will build some of these tables in Chapter 2 You can find all of them in the application data bases on the sample code CD ROM gt tb1Clients This table contains all the pertinent information about each client it s related to tb1Projects the table that will track the information about each project associated with a client gt tblClientAddresses This table contains all addresses for each client it s related to tb1Clients and tblAddressTypes gt tblAddressTypes This table is a lookup table It contains all valid address types for a client it s related to tb1ClientAddress
36. CHAPTER 1 Access as a Development Tool Why This Chapter Is Important In talking to users and developers I find that Access is a very misunderstood product Many people think that it is just a toy for managers or secretaries wanting to play with data Others feel that it is a serious developer product intended for no one but experienced application develop ers This chapter dispels the myths of Access It helps you decipher what Access is and what it isn t After reading the chapter you will know when Access is the tool for you and when it makes sense to explore other products What Types of Applications Can You Develop in Access I often find myself explaining exactly what types of appli cations you can build with Microsoft Access Access offers a variety of features for different database needs You can use it to develop six general types of applications gt Personal applications gt Small business applications gt Departmental applications gt Corporationwide applications gt Asa front end for enterprisewide client server applications gt Intranet Internet applications IN THIS CHAPTER gt Why This Chapter Is Important gt What Types of Applications Can You Develop in Access gt Access as a Scalable Product gt What Exactly Is a Database gt Getting to Know the Database Objects gt Object Naming Conventions gt Hardware Requirements gt How Do Get Started Developing an Access App
37. New Features Found in Access 2007 Microsoft Office Access 2007 includes greatly improved importing and exporting features For example you can now export to PDF and XPS fields You can also save your import ing and exporting specifications so that you can reuse them later I cover these features in Chapter 20 Using External Data Microsoft Office Access 2007 is tightly integrated with Microsoft Office Outlook 2007 You can both collect and update data using Microsoft Office Outlook 2007 When you use the new Data Collection feature Microsoft Office Access 2007 can automatically create a Microsoft Office InfoPath 2007 or HTML form It can then embed that form in an email message You can then send it to selected Outlook contacts or even to contacts stored in an Access database When the recipient fills out the form and returns it you can seam lessly store the resulting data in your Microsoft Office Access 2007 database In addition Microsoft has completely revamped security in Microsoft Office Access 2007 The User Security model has been completely eliminated in Access 2007 unless you keep your database in the old Access file format mdb or mde and that database already has user level security applied In other words if you open a database created in an earlier version of Access and that database already has security applied Access 2007 will support user level security for that database If you convert a database created in an earlier
38. Save and Undo Notice the Save Print and Undo buttons in Figure 1 26 These buttons are all on the Quick Access toolbar you can easily access them at any time You can customize the Quick Access toolbar to What s New in Access 2007 35 include the commands that you use most often You also can modify the placement and size of the toolbar As you can see the small toolbar appears above the command tabs To change the placement of the Quick Access toolbar simply right click the toolbar and select Show Quick Access Toolbar Below the Ribbon The toolbar appears below the ribbon see Figure 1 27 Os MyFirstAccess2007Database Databa Table Tools parecer Home a Create External Data Database Tools Datasheet xc 4 cat ee E Ea 7 al Se m HE E Records ra Filter Sy Find Sort amp Filter Gridlines Horizontal E Tablet Table Gridlines Vertical TI Gridlines None Record 4 4 1of1 gt gt KNoFilter Search Datasheet View Num Lock leiki FIGURE 1 25 The gallery gives you a preview of the effect that the selected choice will make ages MyFirstAccess2007Database Databa Table Tools ax External Data Database Tools _ j Datasheet i 7 vA mo h Insert Data Type Unique znl F H De
39. ach column see Figure 1 73 Click Next 9 In the last step of the wizard Access prompts whether you want to allow multiple values in the lookup see Figure 1 74 Select the Allow Multiple Values check box and click Finish The resulting drop down appears in Figure 1 75 10 Save the table FIGURE 1 74 Click to select the Allow Multiple Values check box Additional Tips and Tricks 69 Oy A Customers Database Access 2007 Table Tools Sox Home Create External Data Database Tools Datasheet BZ j Fl me i Insert Data Type Number F Unique A Eal H i z paeen Eks Cat woe e Y Delete Format Formatting gt F Is Required es View New Add Existing Lookup Relationships Object Field Fields Column Rename 9 f8 08 Dependencies Views Fields and Columns Data Type and Formatting Relationships Northwind Objects orders Bar CUstomer Orders SUBTOR 2 OrderID Employees Employee Customer OrderDate Shippe geal Employee Details 30 9 27 1 15 2006 a Employee List 31 Cencini Andrew 4 1 20 2006 A geal Employee Orders Subform 32 O Freehafer Nancy 12 1 22 2006 ji B Order List 33 S Giussani Laura 8 1 30 2006 Northwind Reports z 34 Hellung Larser Anne 4 2 6 2006 Kotas Jan it 2 35 A 29 2 10 20
40. ams Chap10Exaccdb ZADATANClients Chap11Original accdb ie print p B d800alison ch DATA ChapiLacedb z S DATA Clients sams ChapiLaccdb RS Manage gt a Email Gy Publish FIGURE 1 28 The Microsoft Office Access button provides a menu necessary to perform commonly used commands WS MyFirstaccess2007Database Databa Table Tools rex Home Create External Data Database Tools Datasheet G gt E Views Clipboard amp Font Rich Text e a mm du diesa a ANERE ESEN E a View Paste 5 af aes E4 Becorcs 4 Filter 5 pog Sort amp Filter All Table Paste ctri v a Tablel Paste the contents of the Add New Field e e _ New aBc Company Record 4 4 1of1 gt M WNoFilter Search Datasheet View Num Lock Ee FIGURE 1 29 When you hover your mouse pointer over a command associated with a keyboard shortcut the shortcut appears as a ToolTip 38 CHAPTER 1 Access as a Development Tool Another way in which you can identify keyboard shortcuts is to press your Alt key while on a particular tab All the Alt key shortcuts appear as small indicators see Figure 1 30 For example when you press Alt with the Home tab active you can see that Alt F will access the Microsoft Office Access button mn oo gt Table Tools Chapterl Database Access 2007 Microsoft Acc x A r 5
41. an see the Employees field expanded so that each Employee value appears on a separate row To do this simply change the field row to read Employees Value see Figure 1 78 The Value property causes Access to display the multi valued field in expanded form so that each value appears in a separate row see Figure 1 79 Notice that for each order the EmployeeID associated with that order appears on a different row in the query result 70 CHAPTER 1 Access as a Development Tool Create A Customers Database Access 2007 Query Tools External Data Database Tools Design Northwind Objects IPSS customer Orders Subfor a E E20 seq 2 mnc gt gt Eeanenysnen Pa re ole K B WP Delete Columns or ble Nam view i Ea a AA Ie A Return All eee Sh Parameters ew Results Query Type _ Query Setup a _Show Hide _ Employee Details i Enae Employee List 9 Order ID Employee Orders Subform E Employees Employees Value Order List Employee ID CustomerID Northwind Reports Northwind Tables Customers Employees Order Date Employees1 Order Details Orders Orders Status Products Productsl Shippers Suppliers hHEbEEoEDED Suppliers1 Num Lock FIGURE 1 76 After you close the Show Table dialog box Access places you in Design view of Property Sheet x Selection type Query Properties utput All Fiel Top Values
42. at begins as a small business application running on a standalone machine to an enter prisewide client server application If you design your application properly you can accomplish the scaling process with little to no rewriting of your application This feature makes Access an excellent choice for growing businesses as well as for applications you are testing at a departmental level with the idea that you might eventually distribute them corporationwide The great thing about Access is that even acting as both the front end and back end with data stored on a file server in Access tables it provides excellent security and the capabil ity to establish database rules previously available only on back end databases You can apply referential integrity rules at the database level ensuring that for example users do not enter orders for customers who don t exist You can enforce data validation rules at either a field or record level maintaining the integrity of the data in your database In other words many of the features previously available only on high end database servers are now available by using Access s own proprietary data storage format What Exactly Is a Database The term database means different things to different people For many years in the world of xBase dBASE FoxPro CA Clipper database was used to describe a collection of fields and records Access refers to this type of collection as a table In a client server
43. ated Views Employees M Orders Status Products Suppliers Unrelated Objects Delete Item Add Group Delete Group Rename Group Rename Item I Show Hidden Objects IV Show System Objects C Single ciick Double cick I Show Search Bar r Display Options j Objects with OK Cancel FIGURE 1 54 Use the Navigation Options dialog box to indicate that you want to display system objects 56 CHAPTER 1 Access as a Development Tool 11 Select Show System Objects in the Display Options section Click OK to close the dialog box 12 Create a Make Table query for each table in the database The Make Table query will take all the data in the old table and create a table in the new database with the same data If the s_GUID is a primary key that acts as a foreign key in other tables you must include the s_GUID field in the new table There is no need to copy the s_Lineage and s_Generation fields to the new table 13 Run the Make Table queries This will create the tables in the new database It s important to note that the new table will not inherit any of the field properties and it will not inherit the primary key setting from the original table 14 In the new database create the same index and primary key used in the replica s tables 15 Create the necessary relationships for each table in the new database 16 Save your new database What Happened to ADP Files Access Data
44. ck the menu at the top of the Navigation Pane A cascading menu appears see Figure 1 55 Additional Tips and Tricks 57 os Category Sort By View By Show all groups A Paste Ea Navigation Options Search Bar FIGURE 1 55 A cascading menu enables you to control the behavior of the Navigation Pane 2 Select Navigation Options The Navigation Options dialog box appears see Figure 1 56 3 Click Add Item to add a category Your dialog box appears as in Figure 1 57 Navigation Options Employees Orders Status Products Suppliers FIGURE 1 56 The Navigation Options dialog box enables you to manipulate important features of the Navigation Pane 58 CHAPTER 1 Access as a Development Tool Navigation Options 2hx r Grouping Options Click on a Category to change the Category display order or to add groups Groups for Custom Category 1 Tables and Related Views M Unassigned Objects Add Item Delete Item Rename Item Add Group Delete Group Rename Group FIGURE 1 57 You can easily add a category 4 Type the name of the new category p Display Options Open Objects with I Show Hidden Objects I Show System Objects C Single dick Double click I Show Search Bar x ca to the Navigation Pane 5 Use the up and down arrows to move the category up or down in the list 6 Click OK to close the dialog box If you left click the Navigati
45. d where the Text Format property is set to Rich Text What s New with Forms The number of new features available with forms in Access 2007 is so vast that I will provide an overview here and then will supply the details in Chapter 5 The features new to forms include the following gt The ability to quickly create a form with Quick Create gt Anew view called Layout view gt The ability to work with Stacked and Tabular layouts gt Split forms gt Alternating background colors gt New filtering features for form data What s New with Reports Reports also sport a plethora of new features Many of the features are similar to those provided for reports They include the following gt The ability to create a report with Quick Create gt Anew view called Layout view gt The ability to work with Stacked and Tabular layouts gt New Group Sort and Totals features The Exciting World of Pivot Tables and Pivot Charts Access 2002 2003 and 2007 enable the user to view any table query or form in PivotTable or PivotChart view Pivot tables and pivot charts enable users to easily perform rather complex data analyses This means that you can perform many of the data analysis tasks once left to Microsoft Excel directly within Microsoft Access Pivot tables and pivot charts are available in subforms as well and you can programmatically react to the events that they raise 52 CHAPTER 1 Access as a Development Tool Other
46. de the group in the Navigation Pane Add Item Delete Item Rename Item Add Group Delete Group Rename Group Display Options Open Objects with I Show Hidden Objects 7 Show System Objects ongea vawe I Show Search Bar x oa FIGURE 1 62 Click to clear the Unassigned Objects check box Customers Database Access 2007 Beta Microsoft Access x Home Create External Data Database Tools Ba BP Access P Text File E Ra m A hwo ee A GP synchronize E7 Bg Excel BF XML File Ea GA Ter Fie Fa Reconnect All Saved 4 Saved Excel SharePoint PDF sm Create Manage Move to hi Imports ite SharePoint List f3 More Exports list or xps Su More Email Replies SharePoint dy Discard Changes Collect Data SharePoint Lists Export Ready Caps Lock Num Lock FIGURE 1 63 The Unassigned Objects group no longer appears 62 CHAPTER 1 Access as a Development Tool Creating a New Custom Group Containing an Object Found in an Existing Group Another trick is to create a new custom group containing an object found in an existing group To complete this process you must have a custom category and group containing at least one item Here s the process 1 Use the Navigation Pane to view the object you want to place in the new group 2 Right click the object and select Add to Group New Group
47. ded ID LastName FirstName E mail Address z Business Phone Forms 2 i ES contact Details New E contact List Total 0 Reports a i contact Address Book E contact Phone List Record M 4j1of1 gt k SK NoFilter Search lt a gt Num Lock EEEL FIGURE 1 36 The Navigation Pane enables you to select and work with the appropriate data base object Applying a command to a database object is easy simply right click the object and a context sensitive menu appears For example the context sensitive menu associated with the Contacts table enables you to open design import export delete and perform other important functionality necessary when administering a table see Figure 1 37 Another example is the context sensitive menu that appears when you right click a form Notice in Figure 1 38 that the options for a form are quite different from those for a table They include the ability to work with the form in various views to export rename and delete the form as well as to view form properties What s New in Access 2007 43 Contacts database Database Acces Table Tools 2X External Data Database Tools Datasheet Sraa peores Filter Gi Ks Y vahi View Paste F Views Clipboard Sort amp Filter FirstName LastName E mail Address Company Q i is Open 74 Design View Import gt amp con Export gt amp Con ey
48. determine whether you will allow the user to update each data element and whether it s entered or calculated then you can figure out whether you have properly normalized your table structures Normalization Made Easy Normalization is a fancy term for the process of testing your table design against a series of rules that ensure that your application will operate as efficiently as possible These rules How Do Get Started Developing an Access Application 25 are based on set theory and were originally proposed by Dr E F Codd Although you could spend years studying normalization its main objective is an application that runs efficiently with as little data manipulation and coding as possible Chapter 3 covers normalization and database design in detail For now here are six of the basic normaliza tion rules 1 Fields should be atomic that is each piece of data should be broken down as much as possible For example instead of creating a field called Name you would create two fields one for the first name and the other for the last name This method makes the data much easier to work with If you need to sort or search by first name separately from the last name for example you can do so without extra effort 2 Each record should contain a unique identifier so that you have a way of safely identifying the record For example if you re changing customer information you can make sure you re changing the information associated wi
49. e Figure 1 61 NOTE You can create a maximum of 10 custom categories Of course you can rename or delete categories at any time x j Customers Database Access 2007 Beta Microsoft Access 3 Home Create External Data Database Tools fs BP Access Cp Text File EA E gt SaWord E E Ez E Synchronize 63 33 FP Tex File Reconnect All Create Manage Eg Excel EF XML File Saved Excel SharePoint PDF Move to Imports i SharePoint List 3 More List orxps SUMore E mail Replies SharePoint 2 Discard Changes Collect Data Import ___Export_ _SharePoint Lists _ Northwind Objects Northwind Forms Northwind Reports Northwind Tables Unassigned Objects Customers Employees Employees1 Saved Exports gt gt gt gt gt gt gt gt Order Details Orders Orders Status Products Products1 Shippers Suppliers Suppliers Customer List Customer Orders Subform HH HOOF eaee aoa Employee Details Num Lock FIGURE 1 61 After you close the dialog box the new groups appear within the category Adding Objects to Custom Groups You are now ready to add objects to your custom groups Here s how 1 Click to select the category to which you want to add the new objects 2 In the Unassigned Objects group select the objects you want to include in your custom group and then move them
50. e 1 11 To view the design of any report right click the report in the Navigation Pane and select Design View Figure 1 12 illustrates a report with many sections in the figure you can see a Report Header Page Header Detail section Page Footer and Report Footer just a few of the many sections available on a report Just as a form can contain subforms a report can contain subreports Chapter 6 What Every Developer Needs to Know About Reports and Chapter 11 Advanced Report Techniques cover reports I also cover them through out the book as they apply to other examples Macros A Means of Automating Your System Macros in Access aren t like the macros in other Office products You can t record them as you can in Microsoft Word or Excel and Access does not save them as Visual Basic for Applications VBA code With Access macros you can perform most of the tasks that you Getting to Know the Database Objects 21 can manually perform from the keyboard menus and toolbars Macros enable you to build logic into your application flow Available in Microsoft Office Access 2007 are embedded macros Instead of appearing in the Navigation Pane as a separate object an embedded macro is part of the object to which it is associated When you modify an embedded macro it does not affect any other macros or objects in the database Because you can prevent embedded macros from performing certain potentially unsafe operations they are
51. e General Declarations section and CreateInvoice function Hardware Requirements 23 Object Naming Conventions Finding a set of naming conventions and sticking to it is one of the keys to successful development in Access or any other programming language When you re choosing a set of naming conventions look for three characteristics gt Ease of use gt Readability gt Acceptance in the developer community The naming conventions that I use in this book were derived from the Leszynski Reddick naming conventions that were prominent in Access versions 1 x and 2 0 These standards were adopted and used extensively by the development community and can be found in most good development books and magazine articles written in the past few years These conventions give you an easy to use consistent methodology for naming the objects in all these environments Appendix A Naming Conventions is available for download at www samspublishing com and includes a summarized version of the conventions for naming objects I ll be using them throughout the book and highlighting certain aspects of them as they apply to each chapter Hardware Requirements One of the downsides of Access is the number of hardware resources it requires The requirements for a developer are different from those for an end user so I have broken the system requirements into two parts As you read through these requirements be sure to note actual versus recom
52. e a join line between tables with a number 1 and an infinity symbol This indicates a one to many relationship between the tables If you double click the join line the Edit Relationships dialog box opens see Figure 1 7 In this dialog box you can specify the exact nature of the relationship between tables The relationship between Customers and Orders for example is a one to many relationship with referential integrity enforced This means that the user cannot add orders for customers who don t exist Notice that the check box to Cascade Update Related Fields is not checked This means that the user cannot update the CustomerID of a customer in the Customers table Because Cascade Delete Related Records is not checked the user cannot delete customers from the Customers table if they have corresponding orders in the Orders table Edit Relationships 21x Table Query Related Table Query ce Customers v Jorders v Cancel 1D Z Customer 1D E Join Type Create New Hv Enforce Referential Integrity I Cascade Update Related Fields I Cascade Delete Related Records Relationship Type One To Many FIGURE 1 7 The Edit Relationships dialog box lets you specify the nature of the relationship between tables Chapter 3 Relationships Your Key to Data Integrity extensively covers the process of defining and maintaining relationships It also covers the basics of relational database desig
53. e the Quick Access Toolbar Current Database Choose commands from Customize Quick Access Toolbar Datasheet Popular Commands X For all documents default X Object Designers Proofing i lt Separator gt Cache List Data Advanced Create E mail Customize a Email lt gt Export to Excel spreadsheet Export to SharePoint list Export to text file s Form Fa Import Access database fx Import Excel spreadsheet iT Import SharePoint list E Import text file Add ins Trust Center Resources Add gt gt Remove Manage Replies Mode gt New New Object Form New Object Macro al New Object Query MI New Object Report Reset Modify IT Show Quick Access Toolbar below the Ribbon OK Cancel A FIGURE 1 33 The Customization page of the Access Options dialog box enables you to customize the Quick Access toolbar 2 Use the Customize Quick Access Toolbar drop down list to determine whether your changes will apply for all documents databases or for only the specific document that you are working with 3 Select a command from the list box on the left side of the dialog box and click Add to add it to the list box on the right side of the dialog box For example in Figure 1 34 the Blank Form command has been added from the Create Tab options 4 Use the up and down arrows on the right side of the dialog box to move the command up or down within the list of existing commands
54. ed table Access will create a table that will maintain a link to the source data Changes made to the data in Access will be reflected in the source and vice versa NOTE If the source database requires a password the password will be stored with the linked table Cancel FIGURE 1 51 The Get External Data Access Database dialog box prompts you to locate the database whose objects you are importing 5 Browse to locate the replicated database and then click Open 6 In the Get External Data Access Database dialog box click Import Tables Queries Forms Reports Macros and Modules into the Current Database and then click OK The Import Objects dialog box appears see Figure 1 52 i Tables Queries Forms Reports Macros Modules aie ae _ Employee Privileges Employees Inventory Restocking Cancel Inventory Restocking Details Inventory Transactions Select All Invoices Order Details Deselect All Orders Orders Status _Sations gt gt Purchase Order Details Purchase Order Status Purchase Orders Sales Reports Shippers Strings Suppliers FIGURE 1 52 The Import Objects dialog box prompts you to select the objects you want to import Other New Features Found in Access 2007 55 7 Click to select the objects that you want to import into the new database If you want to import all objects click Select All on each tab Do not select any tables You will handle t
55. environment database refers to all the data schema indexes rules triggers and stored procedures associated with a system In Access terms a database is a collection of all the tables queries forms data access pages reports macros and modules that compose a complete system 12 CHAPTER 1 Access as a Development Tool Getting to Know the Database Objects As mentioned previously tables queries forms reports macros and modules combine to comprise an Access database Each of these objects has a special function An Access appli cation also includes several miscellaneous objects including relationships database prop erties and import export specifications With these objects you can create a powerful user friendly integrated application Figure 1 1 shows the Access application window Notice the categories of objects listed in the Navigation Pane The following sections take you on a tour of the objects that make up an Access database x re Chap24Ex Database Access 2007 Microsoft Access X Home Create External Data Database Tools a fa 24 le Eo a a Bzg A a Paste by Records Filter Size to Switch Find J A E 2 X Fit Form Windows Views Clipboard amp Font Rich Text Sort amp Filter Window i x Security Warning Certain content in the database has been disabled Options All Access Objects v Tables Queries x Forms x Reports
56. er objects in the database you must define relationships among the tables in your database You accomplish this by using the Relationships window To view the Relationships window click to select the Database Tools tab Then select the Relationships button in the Show Hide group The Relationships window appears as shown in Figure 1 6 In this window you can view and maintain the relationships in the database If you or a fellow developer has set up some relationships but you don t see any in the Relationships window select the All Relationships button in the Relationships group on the Design tab to unhide any hidden tables and relationships Getting to Know the Database Objects 15 r C Bao s Table Tools Northwind 2007 Database Access 2007 Micros X j s eee Home Create Extemal Data DatabaseTools Design Fa 7 A A Zra Insert Rows i gt Delete Rows View Primary Builder Test Validation Property Indexes Key Rules a Lookup Column Sheet Tools Show Hide FieldName Data Type Description AutoNumber Company Text Last Name Text First Name Text E mail Address Text tel miat Torst Field Properties General tookup FieldSize Longinteger New Values Increment SSmartTags Afield name can be up to 64 characters long Text Align including spaces Press Fl for help on field nam
57. erform the following tasks Create new databases Open existing databases Save changes to the current object Use the Save As menu to save to other Access file formats as well as to a web server or to a PDF or XPS file Print or print preview Manage databases by compacting and repairing them backing them up and working with Database properties Email your databases to other people Close the current database Ribbon Tips and Tricks You can use the same keyboard shortcuts with Microsoft Office Access 2007 that you could with previous versions of Access This means that you can perform many of the commonly used features such as Save using the keyboard shortcuts that you are familiar with When you hover your mouse pointer over the ribbon on a button that is associated with a keyboard shortcut the shortcut appears as a ToolTip see Figure 1 29 What s New in Access 2007 37 gt Close Database E E Access Options X Exit Access amp EAS i Table Tools Chapter1 Database Access 2007 Microsoft Acc x f m Recent Documents DO New Ta EY switchboard Manager A DATA Clients sams ChapterLaccdb Lat Encrypt with Password E gt open 2 DATA Clients Northwind 2007 accdb anager E Add ins Rone 3 Normalization accdb Database Tools J Save A DATA Clients sams Chap24Ex accdb 5 DATA Clients sams Chap4 accdb N NENS S DATA Clients s
58. es Design view F6 Switch panes F1 Help FIGURE 1 5 The design of the Customers table is the blueprint or template for the table r E W9 e Relationship Tools Northwind 2007 Database Access 2007 M X Home Create External Data Database Tools Design X Clear Layout b Hide Table EA Relationship Report sa Direct Relationships Edit ow Close Relationships Table B All Relationships Tools_____ Relationships r a Customers Orders Employees B VID a OrderID Vw Company p Employee ID Company Last Name Customer ID Last Name First Name Order Date First Name E mail Address Shipped Date E mail Address Job Title Shipper ID Job Title Business Phone si Ship Name z Business Phone ome Dhonn Shin Rte ee Homa Nhann Employee Privileges rivileges Employee ID Privilege ID Pine ON Privilege ID Privilege Name Y Purchase Order Supplier ID Created By Submitted Date I Creation Date Status ID Inventory Transactions Purchase Order Details PEA ene Transaction ID 7mp a OvaeY DEGNE Transaction Type 7 Purchase Order Product ID sD 3 aie OrderID m vi Kim gt Ready ll Num Lock FIGURE 1 6 The Relationships window is the place where you view and maintain the relation ships in the database 16 CHAPTER 1 Access as a Development Tool Notice that many of the relationships in Figure 1 6 hav
59. es gt tb1ClientPhones This table contains all phone numbers for each client it s related to tb1Clients and tblPhoneTypes gt tb1lPhoneTypes This table is a lookup table It contains all valid phone types for a client it s related to tb1ClientPhones gt tb1Terms This table is a lookup table It contains all valid payment terms for a client it s related to tb1Clients gt tb1lContactType This table is a lookup table It contains all valid contact types for a client it s related to tb1Clients gt tb1lProjects This table holds all the pertinent information about each project it s related to several other tables tb1Clients tblPayments tblEmployees tb1TimeCardHours and tb1TimeCardExpenses gt tb1TimeCardHours This table is used to track the hours associated with each project and employee it s related to tb1Projects tbl1TimeCards and tb1WorkCodes gt tblPayments This table is used to track all payments associated with a particular project it s related to tb1Projects and tblPaymentMethods gt tb1TimeCardExpenses This table is used to track the expenses associated with each project and employee it s related to tb1Projects tb1TimeCards and tblExpenseCodes gt tblEmployees This table is used to track employee information it s related to tblTimeCards and tbl1Projects Practical Examples 73 gt tb1TimeCards This table is used to track each employee s hours it s actual
60. ess by using Access Options Follow these steps to view only one object at a time 1 Click the Microsoft Office button 2 Select Access Options see Figure 1 40 The Access Options dialog box appears 3 Click Current Database Your screen should appear as in Figure 1 41 4 In the Application Options section click Display Document Tabs to deselect it 5 Click OK to close the dialog box You will receive a message indicating that you must close and reopen the current database for the specified option to take effect What s New in Access 2007 45 Yau Contacts database Database Access 2007 Beta Microsoft Access 2X X Home Create External Data Database Tools i E m an Recent Documents pas EY Switchboard Manager mer New m G7 Encrypt with Password 1 Contacts database accdb Linked Table Make Manager Add Ins ACCDE Open Database Tools j x zen disabl Enable Content Trust Center Save N Save As gt Manage mail Add From Outlook E mail List Print List F ex E mail Address z Business Phone Z Email ep Print gt 0 an Close Database 3 Access Options X Exit Access Record 4 4l1of1 gt mb Ik No Filter Search 4 ait gt Form View Num tock ELEI FIGURE 1 40 Access Options enables you to modify the behavior of Access and specific databases Access Options 21x
61. g for a large corporation as a mainframe programmer I was required to carefully follow a task analysis checklist I had to find out what each user of the system did to complete her daily tasks document each procedure determine the flow of each task to the next relate each task of each user to her other tasks as well as to the tasks of every other user of the system and tie each task to corporate objectives In this day and age of rapid application development and changing technology task analysis in the devel opment process seems to have gone out the window I maintain that if you don t take the required care to complete this process at least at some level you will have to rewrite large parts of the application Data Analysis and Design After you have analyzed and documented all the tasks involved in the system you re ready to work on the data analysis and design phase of your application In this phase you must identify each piece of information needed to complete each task You must assign these data elements to subjects and each subject will become a separate table in your database For example a subject might be a client every data element relating to that client the name address phone credit limit and any other pertinent information would become fields within the client table You should determine the following for each data element gt Appropriate data type gt Required size gt Validation rules You should also
62. h context sensitive tab as appropriate within this shortcut Ca HUG MyFirstAccess2007Database Databa Table Tools 7x 5 Home Create External Data Database Tools Datasheet bZ Fy sA A g Insert Data Type AutoNumber 7 Unique ag ea gg dis OO YF Delete Format E ts Required Ss View New Add Existing Lookup TR Relationships Object Field Fields Column Rename 9 s0 06 Dependencies Views Fields and Columns Data Type and Formatting Relationships All Tables ZJ Tablet Table1 ID Add New Field E Tablet Table New Recora M 4 1of1 1K No Filt Search Datasheet View Num Lock TEE FIGURE 1 24 The Datasheet tab is a contextual tab available while you are working in Datasheet view Utilizing the Gallery The gallery is a control that displays a choice visually so that you can see the results you will get The idea is to allow you to browse and see what Microsoft Office Access 2007 can do Figure 1 25 provides an example As you can see when you click the arrow on the right side of the Gridlines button a gallery appears showing you how each result will appear This feature makes it easy for you to confidently make your selection from the options available Working with the Quick Access Toolbar The Quick Access toolbar is a single standard toolbar that appears at the top of the ribbon and provides single click access to commands such as
63. hem separately 8 Access prompts you to save your import steps If you want to do so click the Save Import Steps check box enter the required information see Figure 1 53 and then click Save Import Get External Data Access Database 21x Save Import Steps All objects were imported successfully Do you want to save these import steps This will allow you to quickly repeat the operation without using the wizard IV Saye import steps Save as Import Chap2 Description Create an Outlook Task If you regularly repeat this saved operation you can create an Outlook task that reminds you when it is time to repeat this operation The Outlook task will include a Run Import button that runs the import operation in Access J Create Outlook Task Hint To create a recurring task open the task in Outlook and click the Recurrence button on the Task tab Save Import Cancel FIGURE 1 53 Select Save Import if you plan to perform the import process again at a later time 9 Open the replicated database in Access 2007 10 Make sure that the s_GUID s_Lineage and s_Generation fields are visible To do this right click the top of the Navigation Pane and select Navigation Options The Navigation Options dialog box appears see Figure 1 54 Navigation Options 2h x r Grouping Options Click on a Category to change the Category display order or to add groups Categories Groups for Tables and Rel
64. ill be wondering when it is appropriate to use multi valued fields The follow ing are some guidelines gt When you want to link to a SharePoint list gt When you plan to export an Access table to a SharePoint site gt When you plan to move an Access database to a SharePoint site gt When you want to store a multi valued selection from a small list of choices CAUTION Do not use multi valued fields if you plan to upsize your data to Microsoft SQL Server because SQL Server does not support multi valued fields Therefore when you upsize an Access database to SQL Server the upsizing process will convert the multi valued field to an ntext memo field containing a delimited list of values Now that you know when you will want to create a multi valued field take a look at how you create one 1 Open the table that will contain the multi valued field in Datasheet view 2 Click the Datasheet tab 3 Select Lookup Column from the Fields amp Columns group The Lookup Wizard appears see Figure 1 69 4 Click to designate whether you want the lookup column to look up the values in a table or a query or whether you will type the values that you want For this example opt to look up the values in a table or query and click Next Additional Tips and Tricks 67 5 Select the table that you will use to populate the list see Figure 1 70 Click Next 6 Select the field s that you want to include in your lookup see Figure 1 71
65. les you to easily access formatting features without having to use menus or display a toolbar Here s how 1 Select the text you want to change The text must be in a memo field using the rich text feature The mini toolbar appears above the selected text see Figure 1 47 50 CHAPTER 1 Access as a Development Tool a A Contacts database Database Acces Table Tools 2x JA Home Create ExternalData Database Tools patasheet a cation Ju lay Wla Ae qe E Da2 Vie Past D aaa Record Filt z Find jew aste cok s er ni F ao ee ofa ies bx Y e ma Clipboard 5 Rich Text Sort amp Filter x Security Alert Due to your security settings certain content in the database has been disabl Enable Content Trust Center Calibri lu ppm u pH n i E All Access Objects v Contacts ANG z Tables a P state Provin ZIP Postal Ce Country Reg sad contacts cA 93012 USA www infotech This is some femethat wal a Queries f Contacts Extended Forms E contact Details E contact List Reports contact Address Book i contact Phone List Record 4 4 1lof1_ Num Lock Jnr No Filter Search __ Datasheet View FIGURE 1 47 After you select text the mini toolbar appears above the selected text 2 Click to select the appropriate formatting options for example bold
66. lete Format Formatting E ts Required E x View New Add Existing Lookup Relationships Object Field Fields Column SU Rename Si 9 e Dependencies Views Fields and Columns Data Type and Formatting Relationships All Tables x Table1 F ID Add New Field E Tablet Table New ABC Company Record 4 4 1of1 gt m XK NoFilter Search Datasheet View Num Lock lelki FIGURE 1 26 The Quick Access toolbar enables you to easily access commonly used commands 36 CHAPTER 1 Access as a Development Tool Home Create External Data Database Tools Datasheet MyFirstAccess2007Database Database Access 2007 Table Tools ox A I D Calibri 5 1 r a J p ae Al tay ar vi Past P Sear ecords ilter ni I oe SF Aee E E x alll Views Clipboard amp Font a Rich Text Sort amp Filter ag All Tables ETA Table1 E Tablet Table Datasheet View Num Lock ES ID Add New Field New ABC Cbmpany Record I4 lofi Wk No Filter Search Ea FIGURE 1 27 You can place the Quick Access toolbar under the ribbon Working with the Microsoft Office Access Button The Microsoft Office Access button appears in the upper left corner of the application window When you click the Microsoft Office Access button a menu appears see Figure 1 28 gt gt gt gt v Using the menu you can p
67. lication gt What s New in Access 2007 gt Other New Features Found in Access 2007 gt Additional Tips and Tricks gt Practical Examples The Application Design for a Computer Consulting Firm 8 CHAPTER 1 Access as a Development Tool Access as a Development Platform for Personal Applications At its most basic level you can use Access to develop simple personal database management systems I caution people against this idea though People who buy Access hoping to automate everything from their wine collections to their home finances are often disappointed The problem is that Access is deceptively easy to use Its wonderful built in wizards make Access look like a product that anyone can use After answering a series of questions you have finished application switchboards data entry screens reports and the underlying tables that support them In fact when Microsoft first released Access many people asked whether I was concerned that my business as a computer programmer and trainer would diminish because Access seemed to let absolutely anyone write a database application Although it s true that you can produce the simplest of Access applications without any thought of design and without writing a single line of code most applications require at least some designing and custom code As long as you re satisfied with a wizard generated personal application with only minor modifications no problems should occur It s whe
68. ly a bridge between the many to many relationship between Employees and Time Card Expenses as well as between Employees and Time Card Hours It s also related to tblEmployees tb1TimeCardHours and tb1TimeCardExpenses gt tblExpenseCodes This table is a lookup table for valid expense codes it s related to tb1TimeCardExpenses gt tb1lWorkCodes This table is a lookup table for valid work codes it s related to tb1TimeCardHours gt tb1lPaymentMethods This table is a lookup table for valid payment methods it s related to tblPayments gt tblCorrespondence This table is used to track the correspondence related to a project it s related to tblProjects and tblCorrespondenceTypes gt tblCorrespondenceTypes This table is a lookup table for valid correspondence types it s related to tb1Correspondence gt tb1lCompanyInfo This table is a system table It is used to store information about the company You can find this information on forms and reports throughout the system gt tblErrorLog This table is a system table You use it to store runtime errors that occur gt tblErrors tThis table is a system table You use it to store valid error codes and descriptions The relationships among the tables are covered in more detail in Chapter 3 but they re also shown in Figure 1 80 Microsoft Access Relationship Tools On bed Doxa Cts Home Create External Data Database Tools Design
69. m Norn CurrentProject IsTrusted OpenForm Login Dialog Form Normal u c z 2 2 z A Action Arguments z Form Name Startup Screen View Form Filter Name Where Condition Data Mode Opens a form in Form view Design view Print Preview or Datasheet view Press Fl for help on Window Mode Normal this action F6 Switch panes Fl Help Num Lock FIGURE 1 13 The design of the AutoExec macro contains conditions actions arguments and comments 22 CHAPTER 1 Access as a Development Tool Modules The Foundation to the Application Development Process Modules the foundation of any application let you create libraries of functions that you can use throughout your application You usually include subroutines and functions in the modules that you build Functions always return a value subroutines do not By using code modules you can do the following Perform error handling Declare and use variables Loop through and manipulate recordsets Call Windows API and other library functions Create and modify system objects such as tables and queries Perform transaction processing Perform many functions not available with macros Test and debug complex processes v v vy vy vy VY WwW YW Create library databases These are just a few of the tasks you can accomplish with modules To view the design of an existing module right click the module you want to modify in the Navigation Pane a
70. mended requirements What Hardware Does Microsoft Office Access 2007 Require According to Microsoft documentation these are the official minimum requirements to run Microsoft Access 2007 gt 500 megahertz MHz processor or higher gt Windows XP with Service Pack 2 Windows 2003 with Service Pack 1 or a later operating system such as Windows Vista gt 256 megabytes MB RAM or higher gt 1 5 gigabytes GB of hard disk space some will be freed after the original download package is removed from the hard drive gt 1024x768 or higher resolution gt CD ROM or DVD drive gt A pointing device The bottom line for hardware is the more the better You just can t have enough memory or hard drive capacity The more you have the happier you will be using Access 24 CHAPTER 1 Access as a Development Tool How Do I Get Started Developing an Access Application Many developers believe that because Access is such a rapid application development environment there s absolutely no need for system analysis or design when creating an application I couldn t disagree more As mentioned earlier in this chapter Access applica tions are deceptively easy to create but without proper planning they can become a disaster Task Analysis The first step in the development process is task analysis or considering each and every process that occurs during the user s workday a cumbersome but necessary task When I started workin
71. n For now remember that you should establish relationships both conceptually and literally as early in the design process as possible They are integral to successfully design ing and implementing your application Queries Stored Questions or Actions You Apply to Your Data Queries in Access are powerful and multifaceted Select queries enable you to view summarize and perform calculations on the data in your tables Action queries let you add to update and delete table data To run a query select Queries from the Navigation drop down and then double click the query you want to run or right click to select the query you want to run and then click Open When you run a select query a datasheet appears containing all the fields specified in the query and all the records meeting the query s criteria see Figure 1 8 When you run an action query Access runs the specified action such as making a new table or appending data to an existing table In general you can update the data in a query result because the result of a query is actually a dynamic set of records called a dynaset based on your tables data Getting to Know the Database Objects 17 Da H2 c gt Northwind 2007 Database Access 2007 Microsoft Access xX Home Create External Data Database Tools ay m AJ Ver g ab IBZ ita comes EENEN aal T wee Past sofe ea ab Record RA Filt a Find we lew as
72. n a custom group If you want to delete an item from a custom group simply right click the object and select Delete This action does not remove the object from the database it simply removes the shortcut from the custom group The object will appear in the list of Unassigned Objects You can then add that object to another group First you must display the Unassigned Objects group Then click and drag the object to the appropriate group Finally if you want to rename an object simply right click it and select Rename Shortcut Type the new name for the short cut and press Enter Creating Multi valued Fields Another new feature available in Microsoft Office Access 2007 is the new multi valued field As its name implies a multi valued field is a field that holds multiple values You can use this to represent a relationship between two tables For example an order table can have a multi valued field for the employee associated with the order if that order can be associated with multiple employees When you use the drop down list in the order to select an employee the list appears with check boxes You can select multiple items in the list and then click OK to close the list see Figure 1 68 AS Customers Database Access 2007 Table Tools 2X Home Create External Data Database Tools Datasheet uua Insert Data Type Number z Uni et Maa
73. n you want to substantially customize a personal application that problems can happen Access as a Development Platform for Small Business Applications Access is an excellent platform for developing an application that can run a small busi ness Its wizards let developers quickly and easily build the application s foundation The ability to build code modules enables developers to create code libraries of reusable func tions and the ability to add code behind forms and reports enables them to create power ful custom forms and reports The main limitation of using Access for developing a custom small business application is the time and money involved in the development process Many people use Access wizards to begin the development process but find they need to customize their applica tion in ways they can t accomplish on their own Small business owners often experience this problem on an even greater scale The demands of a small business application are usually much higher than those of a personal application Many doctors attorneys and other professionals have called me in after they reached a dead end in the development process They re always dismayed at how much money it will cost to make their applica tion usable Access as a Development Platform for Departmental Applications Access is perfect for developing applications for departments in large corporations It s relatively easy to upgrade departmental users to the approp
74. nd click Design View to open the Module Design window see Figure 1 14 The global code module in Figure 1 14 contains a General Declarations section and five functions The function that is visible is called CreateInvoice Chapter 8 VBA An Introduction and Chapter 13 Advanced VBA Techniques discuss modules and VBA respectively I also cover modules and VBA extensively throughout this book Microsoft Visual Basic Northwind 2007 CustomerOrders Code i File Edit View Insert Debug Run Tools Add Ins Window Help il 4 id 4 Ga aa gt u a ke BF Hl O nici Project Northwind 2007 General ectarations oag z bption Compare Database E E88 Northwind 2007 Northwind 2007 f Ee Microsoft Office Access Class Objec Option Explicit 0 O B 63 Modules CustomerOrders Public Enum CustomerOrderstatusEnum A DomainFunctionWrappers AB Inventory New_Customerorder 0 Af Privileges Invoiced CustomerOrder 1 Purchaseorders z Shipped Customerorder 2 4 as Closed CustomerOrder 3 End Enum Properties CustomerOrders CustomerOrders Module z Alphabetic Categorized Function CreateInvoice OrderID As Long Amt A Dim rsw As New RecordsetWrapper If rsw OpenRecordset Invoices Then With rsw Recordset If Not rsw AddNew Then Exit Funct CustomerOrders a of mm E ami A FIGURE 1 14 The global code module in Design view shows th
75. nder Display Options click Show Hidden Objects 3 Click OK to close the dialog box and return to the Navigation Pane The Navigation Pane shows all hidden objects as dimmed see Figure 1 67 Customers Database Access 2007 Beta Microsoft Access x E Home Create External Data Database Tools 2 Ga Bf Access P Text File ia a PA Gaword afp A G synchronize f _ ES E pa Excel EJ XML File Sil PEA Fret rite E3 BaReconnect All Saved Saved Excel SharePoint PDF m reate Manage Move to 3 Imports ip SharePoint List J3 More 7 list or XPS SYMore Email Replies SharePoint L23 Discard Changes Exports Import Export _ Collect Data SharePoint Lists Northwind Objects Northwind Forms ga Customer Orders Subform e Employee Details a Employee List geal Employee Orders Subform geal Order List Northwind Reports Northwind Tables Northwind Special geal Customer List Unassigned Objects Num Lock FIGURE 1 67 The Navigation Pane shows all hidden objects as dimmed Additional Tips and Tricks 65 4 If you hid the object from its parent group and category right click the object and select Unhide If you used the Hidden property to hide the object from all categories and groups right click the object select View Properties and then clear the Hidden check box You can easily add remove or rename an object i
76. nme Create ExterpalData Database Tools Datasheet aa 2 Be SF Property Sheet E E TET p HE Switchboard Manager i G ip Object Dependencies 4 u G7 Encrypt with Password Visual Relationships SQL Access linked Table a Make Basic 7 Message Bar Server Database Manager amp Add ins ACCDE Macro Show Hide Analyze Move Data Database Tools All Tables yi Table1 2 ID Add New Field E Tablet Table New Record 14 lofi IK No Filter Search Datasheet View Num Lock IEE FIGURE 1 30 If you press the Alt key on your keyboard the Alt shortcuts appear as small indicators Sometimes you are going to want extra screen real estate and will want to collapse the ribbon so that only the active command tab appears Microsoft Office Access 2007 makes this quite easy To collapse the ribbon double click the active command tab Your applica tion window appears as in Figure 1 31 To open it again simply click the tab you want to activate Customizing the Quick Access Toolbar As mentioned in the section Working with the Quick Access Toolbar you can customize the Quick Access toolbar To do so right click the toolbar a context sensitive menu appears see Figure 1 32 Select Customize Quick Access Toolbar The Access Options dialog box appears with the Customization page selected see Figure 1 33 The following steps show you how to customize the Quick Access to
77. ns Ej Table Names NEW Bun H NZ aE IN Ma Return All ie Sha Parameters Results Query Type Query Setup Show Hide Purchase Price Totals Purchase ID Price Total Purchase Orders a 9 Purchase Order IC Supplier ID Created By Submitted Date Creation Date Status ID Exmactnd Date Purchase Order Status 9 status ID Status Navigation Pane 4 w gt l Field Creation Date z Status Supplier ID Shipping Fee Taxes Order Total S Sub Total Price Tot Table Purchase Orders Purchase Order Status Purchase Orders Purchase Orders Purchase Orde Purchase Price Tota Sort Show vy yi vy yi My Num Lock B soe i fee FIGURE 1 9 The design of this query displays data from the Purchase Orders and Purchase Order Status tables and the Purchase Price Totals query To view any form select Forms from the Navigation Pane Then double click the form you want to view or right click the form you want to view and click Open Figure 1 10 illustrates a form in Form view This form is actually four forms in one one main form and three subforms The main form displays information from the Orders table and the subforms display information from the Order Details table and the Orders table As the user moves from order to order the form displays
78. olbar 1 Use the Choose Commands From drop down list to select the category of commands from which you want to choose For example in Figure 1 34 the Create commands are selected What s New in Access 2007 39 Home g aa MyFirstAccess2007Database Databa Table Tools _o x A Create External Data Database Tools Datasheet E Tablet Table New ABC Company Record 4 4 1of1 gt mM XK No Filter Search Datasheet View Num Lock FIGURE 1 31 Double click the ribbon to collapse it Hog able Tools Chapter1 Database Access 2007 Microsoft Acc 7 X Remove from Quick Access Toolbar j Home Datasheet Customize Quick Access Toolbar f a 2s i Show Quick Access Toolbar Below the Ribbon ga re KA Switchboard Manager vi E G7 Encrypt with Password Visual a R Minimize the Ribbon Access Linked Table Make Basic J O message war j Sener Database Manager a Add ins ACCDE Macro Show Hide laa Move Data Database Tools Tablel E tablet Table iD Add New Field a New Record 4 4 1of1 gt m KNoFiter Search Datasheet View Num Lock El FIGURE 1 32 When you right click the Quick Access toolbar a context sensitive menu appears 40 CHAPTER 1 Access as a Development Tool Access Options 2 xi Popular B Customiz
79. on Pane menu you will see your new category in the list see Figure 1 58 k T Customers Database Access 2007 Beta Microsoft Access Home Create External Data Database Tools ox Saved Saved Excel SharePoint PDF _ Import Export E Bf Access Text File ney A EA gt gaWord ton 3 synchronize L Ef Excel fxm File 4 s o s Fyre File Reconnect All Create Manage Moveto 1 Imports if SharePoint List F More Exports List orxps 0 More E mail Replies SharePoint Discard Changes _Collect Data SharePoint Lists All Tables l Mri Navigate To Category Custom Northwind Objects Object Type v Tables and Related Views Created Date Modified Date Filter By Group Employees Orders Status Products Suppliers Unrelated Objects I altables Ready Num Lock FIGURE 1 58 After you create a custom category you will see it in the list of available categories Additional Tips and Tricks 59 Adding Custom Groups to the Category After you have created a custom category you will want to add custom groups to it Here are the steps involved 1 Right click the menu at the top of the Navigation Pane and select Navigation Options The Navigation Options dialog box appears 2 Click to select the category to which you want to add groups In Figure 1 59 Northwind Objects is selected axl r Grouping Options Click on a Ca
80. onflicts often result in difficult to diagnose errors and idiosyncrasies in an Access application Furthermore Access is not the best solution for disconnected users who must access an application and its data over the Internet Finally Access 2007 is hardware hungry The hardware requirements for an Access application are covered later in this chapter The bottom line is that before you decide to deploy a wide scale Access application you need to know the hardware and software configurations of all your system s users You must also decide whether the desktop support required for the typical Access application is feasible given the number of people who will use the system that you are building What Exactly Is a Database 11 Access as a Development Platform for Intranet Internet Applications Using data access pages you can publish your database objects as static or dynamic HTML pages Static pages are standard HTML you can view in any browser Access 2000 intro duced the capability to create XML data and schema documents from Jet or SQL Server structures and data You can also import data and data structures into Access from XML documents You can accomplish this either using code or via the user interface NOTE This book provides coverage of Internet related features such as working with HTML and XML files Access as a Scalable Product One of Access s biggest strong points is its scalability You can scale an application th
81. open the database you can see each object as an over lapping window Exploring the New Status Bar The status bar in Microsoft Office Access 2007 is similar to that of earlier versions of Access but sports some new features In addition to showing status messages property hints progress indicators and other features familiar to earlier versions of Access the new status bar enables you to modify the current view and to zoom It also provides rich right click functionality You can quickly and easily modify the view you are working with by simply clicking the appropriate tool in the lower right corner of the status bar see Figure 1 44 For example when a form is open you can switch among Form view Datasheet view Layout view and Design view When a table is open you can switch among Datasheet view PivotTable view PivotChart view and Design view Another feature of the new status bar is the capability to adjust the zoom level to zoom in or out You do this by using the slider on the status bar Finally the new status bar provides a host of commands that are available when you right click it Notice in Figure 1 45 that you can perform commands such as changing the Caps Lock setting the Num Lock setting and whether the data is filtered You simply click to select or deselect the appropriate setting 48 CHAPTER 1 Access as a Development Tool Dia dM Northwind 2007 Database Access 2007 Microsoft Access NS
82. points to remember gt You can hide an object either via the Navigation Pane or via a property of the object itself gt You can completely hide objects or groups or you can simply disable them Additional Tips and Tricks 63 Customers Database Access 2007 Beta Microsoft Access Gs Home Create External Data Database Tools B BP Access Text File iso A A Ga Word a G3 Synchronize 2 TI E tog CPP excel EF XML File e so 8S etre P Reconnect All Savei Saved Excel SharePoint PDF Create Manage Moveto a al Imports i SharePoint List More Exports List or XPS More E mail Replies SharePoint wy Discard Changes E Import __ U __Export_ Northwind Objects Northwind Forms ges Customer Orders Subform P Employee Details Pe Employee List p Employee Orders Subform ees Order List Northwind Reports Northwind Tables a PE Customer List J Coiled Data SharePoint Lists _ D Ready Caps Lock Num Lock FIGURE 1 65 The new group appears in the Navigation Pane Completing the Process Now that you know the details of showing or hiding groups and objects in a category here s how you finish the process To hide a group in a category simply right click the title bar of the group that you want to hide and then select Hide from the context sensitive menu To restore a hidden group to a categor
83. pplication usually helps Recruit several people to test your application and make sure they range from the most savvy of users to the least computer adept person you can find These different types of users will probably find completely different sets of problems Most importantly make sure you re not the only tester of your application because you re the least likely person to find errors in your own programs 28 CHAPTER 1 Access as a Development Tool Implementation Your application is finally ready to go out into the world or at least you hope so Distribute your application to a subset of your users and make sure they know they re performing the test case Make them feel honored to participate as the first users of the system but warn them that problems might occur and it s their responsibility to make you aware of them If you distribute your application on a wide scale basis and it doesn t operate exactly as it should regaining the confidence of your users will be difficult That s why it is so important to roll out your application slowly Maintenance Because Access is such a rapid application development environment the maintenance period tends to be much more extended than the one for a mainframe or DOS based application Users are much more demanding the more you give them the more they want For a consultant this is great Just don t get into a fixed bid situation Because of the scope of the application changing
84. riate hardware for example it s much easier to buy additional RAM for 15 users than it is for 4 000 Furthermore Access s performance is adequate for most departmental applications without the need for client server technology Finally most departments in large corporations have the devel opment budgets to produce well designed applications Fortunately most departments usually have a PC guru who is more than happy to help design forms and reports This gives the department a sense of ownership because they What Types of Applications Can You Develop in Access 9 have contributed to the development of their application It also makes my life as a devel oper much easier I can focus on the hard core development issues leaving some of the form and report design tasks to the local talent Access as a Development Platform for Corporationwide Applications Although Access might be best suited for departmental applications you can also use it to produce applications that you distribute throughout the organization How successful this endeavor is depends on the corporation There s a limit to the number of users that can concurrently share an Access application while maintaining acceptable performance and there s also a limit to the number of records that each table can contain without a signifi cant performance drop These numbers vary depending on factors such as the following gt How much traffic already exists on the network g
85. rs Employee Privileges a 3 a3 amp Employees EE Inventory Transaction Types Welcome to the Northwind Traders sample amp Inventory Transactions EH cues database In order to use this customize status sar E Order Details Options on the Message Bit ail Vv Kana Mode E oneee stis this content Alternatively Of v numtox On E orders f V Scroll Lock Off a oi rom a Trusted Location E E orders Tax Status V Filtered amp Privileges v Move Mode E Products The example companies organizations products dalV Extended Selection people places and events depicted herein are fictit Y view Shortcuts FIGURE 1 45 When you right click on the status bar you can perform many commands What s New in Access 2007 49 Showing or Hiding the Status Bar Microsoft Office Access 2007 gives you the option of hiding or showing the status bar The following are the steps you must take to change the visibility of the status bar 1 Click the Microsoft Office button 2 Select Access Options The Access Options dialog box appears 3 Click Current Database 4 Click within the Application Options section to deselect Display Status Bar 5 Click OK to close the dialog box 6 Close and reopen the database The status bar should no longer be visible see Figure 1 46 Ca WY F Northwind 2007 Database
86. rts gt Interface with other Access databases as well as with Excel spreadsheets SharePoint lists text files XML files and other databases such as Open Database Connectivity ODBC databases gt Create and manage email The fourth tab is called the Database Tools tab see Figure 1 23 It enables you to do the following Launch the Visual Basic editor Work with macros Work with relationships and object dependencies Perform analysis tasks Interface with SQL Server Work with linked tables Manage switchboards Encrypt databases Work with add ins v vyv vy vy vy VY Vv WwW y Compile your database What s New in Access 2007 33 3 a eC Table Tools Chapter1 Database Access 2007 Microsoft Acc ox Home Create External Data Database Tools l Datasheet a of Access Eja Sa EA o is Nini L Discard Changes yy ox Excel gia Cache List Data Save e Saved 77 SharePoint List Create Manage Work Synchronize Move to imports gt I SharePoint List 23 exports LbSherePoint list my FIAT Replies Online 2 Relink Lists SharePoint Import Export Collect Data SharePoint Lists All Tables SJ Table Tablel ra a ee Add New Field E Tablet Table New Record iofi_ gt M K NoFiter Search Datasheet View Num tock E FIGURE 1 22 The External Data tab enables you to interface between Microsoft Office
87. s to sign off on every screen and report Even a change such as moving a field on a screen meant a change order and approval for additional hours After the user signed off on the screen and report specifications the programmers would go off for days and work arduously to develop each screen and report They would return to the user after many months only to hear that everything was wrong This meant the developer had to go back to the drawing board and spend many additional hours before the user could once again review the application The process is quite different now As soon as you have outlined the tasks and the data analysis is complete the developer can design the tables and establish relationships among them The form and report prototype process can then begin Rather than the developer going off for weeks or months before having further interaction with the user the developer needs only a few days using the Access wizards to quickly develop form prototypes Testing As far as testing goes you just can t do enough I recommend that if your application is going to be run in Windows 2000 Windows 2003 Windows XP and Windows Vista you test in all environments I also suggest you test your application extensively on the lowest common denominator piece of hardware the application might run great on your machine but show unacceptable performance on your users machines Testing your application both in pieces and as an integrated a
88. sing Microsoft Office Access 2007 to interface with data stored in Microsoft Windows SharePoint 2007 and that list contains a field that uses one of the multi valued field types available in Windows SharePoint Services Another situation is when you want to purposely simplify the database design Although this seems counter to basic database design principles it helps to understand that the Microsoft Office 2007 database engine does not actually store the multiple values in a single field It uses system tables to build the relationship and then visually brings the data back together for the user If you think about it you will realize that the relationship between the tables is actually a many to many relationship In this example an order can be associated with multiple employees and each employee can be associated with multiple orders Multi valued fields allow Microsoft Office Access 2007 and SharePoint 2007 to be tightly integrated because using multi valued fields in Access supports the equivalent field type in SharePoint Services This means that when you link to a SharePoint list containing a multi valued data type Access creates a multi valued data type locally When you export an Access table to SharePoint multi valued fields seamlessly port to SharePoint In fact when you move an entire Access database to SharePoint all the tables containing multi valued fields become field types available in Windows SharePoint Services You might st
89. t How much RAM and how many processors does the server have gt How is the server already being used For example are applications such as Microsoft Office being loaded from the server or from local workstations gt What types of tasks are the users of the application performing Are they querying entering data running reports and so on gt Where are Access and your Access application run from the server or the workstation gt What network operating system is in place My general rule of thumb for an Access application that s not client server based is that poor performance generally results with more than 10 15 concurrent users and more than 100 000 records Remember these numbers vary immensely depending on the factors mentioned as well as on the definition of acceptable performance by you and your users The basics of when to move to a client server database are covered in Chapter 22 Developing Multiuser and Enterprise Applications I cover additional details about this topic in a separate book Alison Balter s Mastering Access 2002 Client Server Development also published by Sams Developers often misunderstand what Access is and what it isn t when it comes to being a client server database platform People often ask me Isn t Access a client server data base The answer is that Access is an unusual product because it s a file server application out of the box but it can act as a front end to a
90. te i D ecords ilter ni SSE SHS Pay PO 9 7 Views Clipboard Rich Text Sort amp Filter Find Queries v x Ca Customers Extended a Product Quantity On Order tH Employees Extended Be orthwind Traders Chai HI 41 Northwind Traders Syrup 50 in Inventory __ Northwind Traders Cajun Seasoning 40 F Inventory on Hold Northwind Traders Boysenberry Spread 10 E Inventory on Order _ __ Northwind Traders Chocolate Biscuits Mix 20 CS Inventory Purchased 1 Northwind Traders Marmalade 40 tH inventory sold Northwind Traders Crab Meat 120 Northwind Traders Coffee 300 n Invoice Data Northwind Traders Dried Apples 40 OF Order Details Extended Northwind Traders Mozzarella 40 CS order Price Totals Northwind Traders Brownie Mix 10 Cl order Subtotals iat Order Summary iat Product Category Sales by in Product Orders a Product Purchases EP Product Sales by Category y Record 4 siori gt mi K No Filter Search Datasheet View Num Lock jE ci Gh sou Se joes FIGURE 1 8 When you run the Inventory on Order query a datasheet appears containing all the fields specified in the query and all the records meeting the query s criteria When you store a query only its definition layout or formatting properties and datasheet are actually stored in the database Access offers an intuitive user friendly tool for you to design your queries Figure 1 9 shows the Query Design window To open this window select Queries
91. tegory to change the Category display order or to add groups Categories Groups for Northwind Objects Tables and Related Views M Unassigned Objects AddItem Delete Item Rename Item Add Group Delete Group Rename Group r Display Options Open Objects with I Show Hidden Objects I Show System Objects C Singe dick Double click I Show Search Bar Oox ca FIGURE 1 59 Select the category to which you want to add groups 3 Click the Add Group command button A new group appears 4 Type the name of the new group 5 Continue adding new groups to the category When you are finished the Navigation Options dialog box should appear as in Figure 1 60 ax Grouping Options Click on a Category to change the Category display order or to add groups Categories Groups for Northwind Objects Tables and Related Views M Northwind Forms Object Type M Northwind Reports Custom V Northwind Tables e Northwind Objects 2 Unassigned Objects Add Item Delete Item Rename Item Add Group Delete Group Rename Group r Display Options Open Objects with I Show Hidden Objects I Show System Objects C Single ciick Double click I Show Search Bar OK Cancel FIGURE 1 60 After you add groups they appear in the dialog box 60 CHAPTER 1 Access as a Development Tool 6 Click OK to close the dialog box The groups now appear within the category se
92. th the correct customer We refer to this unique identifier as a primary key 3 The primary key is a field or fields that uniquely identify the record Sometimes you can assign a natural primary key For example the Social Security number in an employee table should serve to uniquely identify that employee to the system At other times you might need to create a primary key Because two customers could have the same name for example the customer name might not uniquely identify the customer to the system You might need to create a field that would contain a unique identifier for the customer such as a customer ID 4 A primary key should be short stable and simple Short means it should be small not a 50 character field A Long Integer is perfect as a primary key Stable means the primary key should be a field whose value rarely if ever changes For example although a customer ID would rarely change a company name is much more likely to change Simple means it should be easy for a user to work with 5 Every field in a table should supply additional information about the record that the primary key serves to identify For example every field in the customer table describes the customer with a particular customer ID 6 Information in the table shouldn t appear in more than one place For example a particular customer name shouldn t appear in more than one record Take a look at an example The datasheet shown in Figure 1 15 is
93. ti 123 5 S jm 3 Company Mortensen Sven Purchasing Manager 123 5 E a 10 Company J Wacker Roland Purchasing Manager 123 5 E 11 Company K Krschne Peter Purchasing Manager 123 5 _ 12 Company L Edwards John Purchasing Manager 123 5 13 CompanyM Ludick Andre Purchasing Representati 123 5 fe 14 Company N Grilo Carlos Purchasing Representati 123 5 15 CompanyO Kupkova Helena Purchasing Manager 123 5 16 Company P Goldschmidt Daniel Purchasing Representati 123 5 BE 17 CompanyQ Bagel Jean Philippe Owner 123 5 ki A A E AA M tot 29 ani A No Filter Scannell 4 m lawrence A Datasheet View Num Lock ED Sd je mage m 50 gt lt Left 0 gt lt Top xI FIGURE 1 4 The Datasheet view of the Customers table in the Northwind database includes all the table s fields and records As a developer you most often want to view the table s design which is the blueprint or template for the table To view a table s design click the View icon on the home page of the ribbon while the table is open see Figure 1 5 In Design view you can view or modify all the field names data types and field and table properties Access gives you the power and flexibility you need to customize the design of your tables Chapter 2 What Every Developer Needs to Know About Databases and Tables covers these topics Relationships Tying the Tables Together To properly maintain your data s integrity and ease the process of working with oth
94. to under stand what Access is and how it fits into the application development world Access is an extremely powerful product with a variety of uses you can find Access applications on everything from home PCs to the desks of many corporate PC users going against enter prisewide client server databases After you understand what Access is and what it does you re ready to learn about its many objects Access applications are made up of tables queries forms reports macros modules ribbons relationships and other objects When designed properly an Access application effectively combines these objects to give the user a powerful robust and useful application
95. types of functions gt Switch between views datasheet and design gt Cut copy and paste gt Format text add bold or underline change the font and so on gt Work with rich text bulleted lists and numbered lists gt Work with records save total spell check and so on gt Sort and filter data gt Locate data meeting specific criteria The second tab is the Create tab see Figure 1 21 It enables you to perform the following types of functions gt Create tables table templates and SharePoint lists gt Create various types of forms gt Create various types of reports gt Create queries and macros 32 CHAPTER 1 Access as a Development Tool CAS MyFirstaccess2007Database Databa Table Tools 7 X Home Create ExternalData Database Tools Datasheet Ej Tabie Ei Form 3 J 3 Labels wal E E Table Templates EsplitForm O _ Blank Report GN y Il SharePoint Lists pasgn FE Multiple tems Design Report Fy Report Wizard Design ee ee Tables _Forms Reports Other All Tables Table1 ID Add New Field Tablet Table New Record M 4 1of1 IK No Filter Search Datasheet View Num Lock IER FIGURE 1 21 The Create tab enables you to create database objects The third tab is the External Data tab see Figure 1 22 This tab enables you to perform the following types of tasks gt Process saved imports and expo
96. upplierID SupplierAddress City State X Ready Num Lock FIGURE 1 16 The data has been normalized into four separate tables How Do Get Started Developing an Access Application 27 I have uniquely identified each record by a contrived CustID field which I use to relate the orders table tolOrders to tblCustomers The tblOrders table contains only informa tion that applies to the entire order rather than to a particular item that the customer ordered This table contains the CustID of the customer who placed the order and the date of the order and I ve related it to the tblOrderDetails table based on the OrderID The tbl0rderDetails table holds information about each item ordered for a particular Order ID There s no limit to the potential number of items that the user can place on an order The user can add as many items to the order as needed simply by adding more records to the tbl0rderDetails table Finally I placed the supplier information in a separate table tblSuppliers so that if any of the supplier information changes the user has to change it in only one place Prototyping Although the task analysis and data analysis phases of application development haven t changed much since the days of mainframes the prototyping phase has changed In working with mainframes or DOS based languages it was important to develop detailed specifications for each screen and report I remember requiring user
97. we ll look at each tab available in Microsoft Office Access 2007 Getting to Know the Ribbon The ribbon is the area at the top of the program window it replaces menus and toolbars Using the ribbon you can choose the category of commands with which you want to work The ribbon contains command tabs and contextual command tabs The following sections cover both types of tabs Exploring the Command Tabs When you launch Microsoft Office Access 2007 you are presented with a tabbed inter face The tabs displayed include Home Create External Data Database Tools and Datasheet This section explores the details of each tab What s New in Access 2007 31 Cx UGS iyrirstaccess2007Database Databa Table Tools 2X s Home l Create External Data Database Tools Datasheet Sa rele y Gl 14 a amp Calibri JE EE a at r AEA Sarp ea v Record l Fil Fina View Paste IVre gt eae ecords 4 ilter n ESS SR Ao 2x Views Clipboard Font ta Rich Text Sort amp Filter All Tables Table1 ID Add New Field Tablet Table New Record 14 4 1of1 gt gt IK No Filter Search Datasheet View Num Lock Bit FIGURE 1 20 The Home tab enables you to perform basic formatting and record oriented tasks The first tab is the Home tab see Figure 1 20 It enables you to perform the following
98. xperience will be very differ ent from that of Access 2003 or any of the previous versions of Microsoft Access When you launch Access 2007 the screen appears as shown in Figure 1 17 Here you can opt to create a new blank database open a recently used database open other existing databases or create a new database from a template If you select Blank Database Access prompts you for the name and location of the database as shown in Figure 1 18 When you click Create the screen appears as shown in Figure 1 19 What s New in Access 2007 29 Getting Started with Microsoft Office Access Mew Blank Database EE a g ce More on Otice Onana Ba ffceonine Taig tempat oertne What s new in Access 20077 RARE SIE re ne Access 2057 cantar more powetl on p Get the est contet whe working ne 2057 To hep you quay ac report ana nare Moesen Omer sten infomation in a manapeabie emvsnmant Leam o Gige to Access 2007 Une Interface mare about the new teshnes aad laprovenents Organise a your chjects uning the new aty aceit Nangaben Pane Microsoft Access Template Categories Ra Getting Started with Microsoft Office Access ri _New Blank Database j Microsoft Office Online Blank Database Faculty database Create a Microsoft Office Access database that does not contain any existing data or objects File Name Petsbesetac OOOO Ol E28 6ffice Online D Documents and Settings Administrator My
99. y follow these steps 1 Right click the menu bar at the top of the Navigation Pane and select Navigation Options 2 Click to select the category containing the hidden object 3 In the Groups for Category list click to select the check box next to the hidden group 4 Click OK The group should now appear in the Navigation Pane Hiding an Object in Its Parent Group At times you will want to hide an object in its parent group All you need to do is right click the specific object that you want to hide and then select Hide If you want to hide an object from all categories and groups follow these steps 1 Right click the object that you want to hide and select View Properties The Properties dialog box appears see Figure 1 66 64 CHAPTER 1 Access as a Development Tool Customer List Properties xi General a Shortcut to Form Customer List Type Shortcut Description Created 1 8 2007 4 21 46 PM Modified 1 8 2007 4 21 46 PM Owner Admin Attributes Hidden T Disable Design View shortcuts Cancel A FIGURE 1 66 You use the Properties dialog box to hide an object 2 Click the Hidden check box 3 Click OK You will no longer see the object in the Navigation Pane Restoring a Hidden Object You are probably wondering how to restore an object after it is hidden Here s how 1 Right click the menu at the top of the Navigation Pane and select Navigation Options from the shortcut menu 2 U
100. you could very well end up on the losing end of that deal There are three categories of maintenance activities bug fixes specification changes and frills You need to handle bug fixes as quickly as possible The implications of specifica tion changes need to be clearly explained to the user including the time and cost involved in making the requested changes As far as frills go try to involve the users as much as possible in adding frills by teaching them how to enhance forms and reports and by making the application as flexible and user defined as possible Of course the final objective of any application is a happy group of productive users What s New in Access 2007 Access 2007 sports a plethora of new features all worth taking a look at Although Microsoft targeted many of the new features to the end user there are many other useful enhancements in the product The following sections provide an overview of the new features I cover each feature in more detail in the appropriate chapter of this book What s New in the User Interface The user interface in Microsoft Office Access 2007 has been redesigned from the ground up Microsoft made this design change to help you find the commands that you need when you need them Many features that previously were buried deep within Access s menu structure are now easily accessible From the moment you launch Microsoft Office Access 2007 to the time you exit the application your user e

- Pearsoncmg

Contents

Download Pdf Manuals

Related Search

Related Contents