1 / 40

Tutorial 12 Managing and Securing a Database

Tutorial 12 Managing and Securing a Database. Objectives. Filter data in a table and a form Save a filter as a query and apply the saved query as a filter Create a subquery Create a multivalued field Create an Attachment field Use an AutoNumber field

dillian
Download Presentation

Tutorial 12 Managing and Securing a Database

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

E N D

Presentation Transcript

  1. Tutorial 12Managing and Securing a Database

  2. Objectives • Filter data in a table and a form • Save a filter as a query and apply the saved query as a filter • Create a subquery • Create a multivalued field • Create an Attachment field • Use an AutoNumber field • Save a database as a previous version New Perspectives on Microsoft Access 2010

  3. Objectives • Analyze a database’s performance • Link a database to a table in another database • Use the Linked Table Manager • Split a database • Encrypt a database with a password • Set database properties and startup options • Create an ACCDE file New Perspectives on Microsoft Access 2010

  4. Multivalued Fields New Perspectives on Microsoft Access 2010

  5. Selecting Records Using Filter By Form • Open the table or query datasheet, or the form in Form view • In the Sort & Filter group on the Home tab, click the Advanced button, and then click Filter By Form • Enter a simple selection criterion or an And condition in the first datasheet or form, using the text boxes for the appropriate fields New Perspectives on Microsoft Access 2010

  6. Selecting Records Using Filter By Form • If there is an Or condition, click the Or tab and enter the Or condition in the second datasheet or form. Continue to enter Or conditions on separate datasheets or forms by using the Or tab • In the Sort & Filter group on the Home tab, click the Toggle Filter button (with the ScreenTip “Apply Filter”) New Perspectives on Microsoft Access 2010

  7. Selecting Records Using Filter By Form New Perspectives on Microsoft Access 2010

  8. Saving a Filter as a Query • Create a filter using Filter By Selection, Filter By Form, or Advanced Filter/Sort • If you applied the filter using Filter By Form, click the Advanced button, and then click Filter By Form • In the Sort & Filter group on the Home tab, click the Advanced button, and then click Save As Query • Type the name for the query, and then press the Enter key New Perspectives on Microsoft Access 2010

  9. Saving a Filter as a Query New Perspectives on Microsoft Access 2010

  10. Applying a Filter Saved as a Query • Open the table to which you want to apply the filter in Datasheet view • In the Sort & Filter group on the Home tab, click the Advanced button, and then click Filter By Form • In the Sort & Filter group, click the Advanced button, and then click Load from Query • In the Applicable Filter dialog box, click the query you want to apply as a filter, and then click the OK button • In the Sort & Filter group on the Home tab, click the Toggle Filter button (with the ScreenTip “Apply Filter”) to apply the filter New Perspectives on Microsoft Access 2010

  11. Applying a Filter Saved as a Query New Perspectives on Microsoft Access 2010

  12. Creating a Subquery • SQL Sub queries • A subquery is a query within a query • A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select • Example: let’s consider the tblCustomer . If you know the name of the government customers (Goldberg, Rodriguez, and Ward), you can get their customer id's by using this query: • SELECT CustomerID, LastName,CustomerType • FROM tblCustomer • WHERE lastName IN ("Goldberg","Rodriguez","Ward"); • But, if we don’t’ know their last names, then we could get their customer id's by using this query: • SELECT CustomerID, LastName,CustomerType • FROM tblCustomer • WHERE lastName IN (SELECT LastName from tblCustomer WHERE CustomerType = "government”); New Perspectives on Microsoft Access 2010

  13. Creating a Subquery New Perspectives on Microsoft Access 2010

  14. Using Multivalued Fields • A multivalued field is a lookup field that allows you to store more than one value • Disadvantages: • Access does not actually store values in the field in that table – it stores them in a hidden, system table in a many-to-many relationship. • Multivalued fields do not convert properly to DBMS’s such as SQL server or Oracle • Best to create a 1:M out of a M:M relationship. For example, instead of using the multivalued field in the next slide, one could create a third table assuming with had a table for Degrees: • Table 1: tblEmployee (EmployeeID, FirstName,LastName, …) • Table 2: tblDegree (DegreeId, DegreeName,..) • Table 3: tblEmployeeDegrees( ID, EmployeeId, DegreeId) New Perspectives on Microsoft Access 2010

  15. Using Multivalued Fields New Perspectives on Microsoft Access 2010

  16. Database Properties New Perspectives on Microsoft Access 2010

  17. Creating an Attachment Field • You use the Attachment data type to attach one or more attachments to a table record (Excel and Word documents, images) • Access stores attachments in compressed form to minimize file size and maximize disk space usage New Perspectives on Microsoft Access 2010

  18. Using an AutoNumber Field • When you create a table in Datasheet view, Access assigns the AutoNumber data type to the default ID primary key field because the AutoNumber data type automatically inserts a unique number in this field for every record in the table New Perspectives on Microsoft Access 2010

  19. Using an AutoNumber Field New Perspectives on Microsoft Access 2010

  20. Saving an Access Database as a Previous Version • Make sure that the database you want to save is open and all database objects are closed, and that the database does not contain any features included only in Access 2007 file-formatted databases • Click the File tab on the Ribbon to display Backstage view, click Save & Publish in the navigation bar, click Save Database As, and then click Access 2002-2003 Database or click Access 2000 Database, depending on which file format you want to use New Perspectives on Microsoft Access 2010

  21. Saving an Access Database as a Previous Version • Click the Save As button. In the Save As dialog box, navigate to the folder where you want to save the file, enter a name for the database in the File name box, and then click the Save button New Perspectives on Microsoft Access 2010

  22. Using the Performance Analyzer • Start Access and open the database you want to analyze • In the Analyze group on the Database Tools tab on the Ribbon, click the Analyze Performance button • Select the object(s) you want to analyze, and then click the OK button New Perspectives on Microsoft Access 2010

  23. Using the Performance Analyzer • Select the analysis result(s) you want the Performance Analyzer to complete for you, and then click the Optimize button • Note the idea optimizations and perform those optimizations, as appropriate • Click the Close button New Perspectives on Microsoft Access 2010

  24. Using the Performance Analyzer New Perspectives on Microsoft Access 2010

  25. Linking to a Table in Another Access Database • A linked table is stored in a file outside the open database • Can be updated from the open database • Add, Delete, Edit records • Can’t change structure • Used to access data maintained in another Access database or by other programs New Perspectives on Microsoft Access 2010

  26. Linking to a Table in Another Access Database • Click the External Data tab on the Ribbon • In the Import & Link group on the External Data tab, click the Access button (with the ScreenTip “Import Access database”) • Click the Link to the data source by creating a linked table option button • Click the Browse button, select the folder and file containing the linked data, and then click the Open button • Click the OK button, select the table(s) in the Link Tables dialog box, and then click the OK button New Perspectives on Microsoft Access 2010

  27. Using the Linked Table Manager • In the Navigation Pane, right-click the linked table name, and then click Linked Table Manager on the shortcut menu or • In the Import & Link group on the External Data tab, click the Linked Table Manager button New Perspectives on Microsoft Access 2010

  28. Using the Linked Table Manager • Click the check box(es) for the linked table(s) you want to refresh, and then click the OK button • Navigate to the linked table location, click the filename, and then click the Open button. • Click the OK button, and then close the Linked Table Manager dialog box New Perspectives on Microsoft Access 2010

  29. Using the Linked Table Manager New Perspectives on Microsoft Access 2010

  30. Using the Database Splitter • The Database Splitter is an Access tool that splits an Access database into two files: • one file contains the tables • other file contains the queries, forms, reports, and other database objects • Each file created by the Database Splitter is an Access database • Back-end database contains the tables • Front-end database contains the other objects, including the user interface New Perspectives on Microsoft Access 2010

  31. Using the Database Splitter When users open a front-end database, the object they open use data in table in the back-end database New Perspectives on Microsoft Access 2010

  32. Using the Database Splitter • Make a backup copy of the database that you want to split • Start Access and open the database you want to split • Click the Database Tools tab on the Ribbon, and then in the Move Data group, click the Access Database button • Click the Split Database button, select the drive and folder for the back-end database, type a name for the database in the File name box, and then click the Split button • Click the OK button New Perspectives on Microsoft Access 2010

  33. Encrypting a Database and Setting a Password • Start Access, click the File tab on the Ribbon, and then click Open in the navigation bar • Select the drive and folder that contains the database, and then click the database name • Click the Open button arrow, and then click Open Exclusive • Click the File tab, and then click the Encrypt with Password button • Type the password in the Password box, type the same password in the Verify box, press the Enter key, and then click the OK button in the message box New Perspectives on Microsoft Access 2010

  34. Encrypting a Database and Setting a Password New Perspectives on Microsoft Access 2010

  35. Unsetting a Database Password • Start Access, click the File tab on the Ribbon, and then click Open in the navigation bar • Select the drive and folder that contains the database, and then click the database name • Click the Open button arrow, and then click Open Exclusive • Click the File tab, and then click the Decrypt Database button • Type the password in the Password box, and then press the Enter key New Perspectives on Microsoft Access 2010

  36. Unsetting a Database Password New Perspectives on Microsoft Access 2010

  37. Setting the Database Properties and Startup Options • Open the database, click the File tab, and then click Options in the navigation bar • In the Access Options dialog box, click Current Database in the left section • Set the database properties and startup options, and then click the OK button. Most options will take effect the next time the database is opened New Perspectives on Microsoft Access 2010

  38. Setting Database Documentation Properties • Open the database, click the File tab, and then click the View and edit database properties link in the right section of the window • Click the Custom tab • To set an existing property, scroll the Name list, click the property in the Name list, type the property setting in the Value box, and then click the Add button • To create a new property, type the property name in the Name box, select the data type in the Type box, type the property value in the Value box, and then click the Add button • Click the OK button New Perspectives on Microsoft Access 2010

  39. Setting Database Documentation Properties New Perspectives on Microsoft Access 2010

  40. Saving a Database as an ACCDE File • Open the database you want to save as an ACCDE file • Click the File tab on the Ribbon, click Save & Publish in the navigation bar, and then click Make ACCDE • Click the Save As button. In the Save As dialog box, type the name for the file in the File name box, navigate to the location where you want to store the file, and then click the Save button New Perspectives on Microsoft Access 2010

More Related