Lesson 13 – Advanced Database Features

1. Lesson 13 – Advanced Database Features

2. Lesson Objectives • Convert databases. • Manage import specifications. • Mange export specifications. • Use advanced navigation techniques. • Use security tools.

3. Convert Databases Exercises • Open a Previous Version of Access • Convert a Previous Version of Access • Convert a Database as a Previous Version

4. Open a Previous Version of Access • Access 2000 and above will open in 2007 • Will not be able to use new 2007 features • Multi-valued Data types • Attached Data types • Append only Memo fields • Compressed image storage • Link tables to files in ACCDB format • Encrypted database with password

5. Open a Previous Version of Access • Must restart Access to see property changes

6. Convert a Previous Version of Access • Conversion tools allow database to be upgraded to 2007 • Version number appears in the Access title bar

7. Convert Database as a Previous Version • Conversion tools allow database to be formatted to earlier version of Access • Access 2000 file format • Access 2002-2003 file format • New files lose the 2007 advanced features

8. Managing Import Specifications Exercises • Saving Import Specifications • Run a Saved Import Task • Import a XML File

9. Saving Import Specifications • Import Specifications • Allows for easy repeating of tasks • Details of saved task can be changed

10. Saving Import Specifications • Text Files • Contain only unformatted textual characters • Delimited files • Separated by special character • Fixed-width files • Data forms columns • Disadvantage • Can’t tell types of data

11. Saving Import Specifications • Advanced Import Specifications • Save for later use

12. Run a Saved Import Task • Manage Data Task dialog box

13. Import a XML File • Extensible Markup Language (XML) • Stores • Table names • Data • Field names • Field properties • Test only stores data • Uses XSL and XSD files

14. Managing Export Specifications Exercises • Export Data as an XML file • Run a Saved Export Task • Export a Text File • Export a HTML Document • Create a Mail/Merge Letter

15. Export Data as an XML file • XML • Condensed form of Standard Generalized Markup Language (SGML) • Readable outside of the Microsoft environment • XSD • Infrastructure design • Approved W3C standard • XSL • Stylesheet formatting

16. Export Data as an XML file • Options control how and what files will be created during the export

17. Run a Saved Export Task • Exported data will not append to existing files • Can replace existing files

18. Export a Text File • Users can export any recordset • Set options to view file after export for verification • Notepad can open text files

19. Export a Text File • Set options on how the text file will be stored

20. Export an HTML Document • Hypertext Markup Language (HTML) • Standard for Web documents • Viewable through a web browser • Optional to save formatting and layout

21. Create a Mail Merge Letter • Word processing feature • Generates a copy of a document for each record in a recordset • Used for mass mailings

22. Using Advanced Navigation Techniques Exercises • Custom Navigation Pane • Set Application Option • Create a Switchboard • Navigate with a Switchboard

23. Custom Navigation Pane • Can organize major database objects along common tasks • Can limit which object a user can see • All changes are saved in the current database

24. Set Application Options • A few of the options include • Application icon • Application title • Hides file format information in the Title bar • Must restart database to apply changes

25. Create a Switchboard • Switchboard • Default title is name of the database • A form • Two parts • Pages • switchboards • Options • commands

26. Navigate with a Switchboard • Can assign Switchboard to open when the database opens

27. Navigate with a Switchboard • Switchboard data is stored in a Table • Changes can be made to the Table

28. Using Security Tools Exercises • Using the Trust Center • Use the Database Splitter • Encrypt and Decrypt a database

29. Using the Trust Center • Trust Center • Create Trusted locations • No security warnings when opening a database • Affects how new and existing databases behave

30. Use the Database Splitter • The Database Splitter will send: • Tables to a Back-end database • Other objects to a Front-end database • Back-end databases can be stored in secure location to protect data • Multiple Front-end databases can use one Back-end database

31. Use the Database Splitter

32. Encrypt and Decrypt a Database • Encrypted databases • Decode each object as it is opened • Slow the database down • Add security • Password protection • To add or remove password, database must be opened in Exclusive mode • Backup your database before assigning a password

33. Encrypt and Decrypt a Database

34. Lesson 13 Summary • Access databases are upwardly, but not downwardly compatible. • When using Office Access 2007, you do not need to convert Access 2000 or 2002–2003 databases. • Access 97 or earlier format databases are converted into Access 2002–2003 format by default and then can be converted to Access 2007. • When you convert a 2007 database to a previous version file format, the database loses the ability to use the advanced features available only in Access 2007. • Whenever you routinely import data from similarly structured files, you can save the import specifications.

35. Lesson 13 Summary • Each time you launch an import or export operation, you can save additional specifications for future use. • An advantage of using an XML file format over a text file is the ease in transforming the data to different applications such as Word, Excel, or non-Access databases. • An HTML file format provides a means for describing the structure of text-based information in a document. • Mail merge is a word processing feature that combines a main document template and a recordset to create a customized document for each record in the recordset. • To assign a password, you must open the database for exclusive use.

36. Lesson 13 Summary • Two techniques to assist users to navigate database objects are a custom navigation pane and a switchboard. • The default title given to a switchboard is the name of the database. • Access 2007 provides several tools to help make your data and workstation more secure, such as trusting a database, adding a password, and encrypting a database. • The Trust Center allows a user to set security options or change Trusted locations. • Splitting a database into a front-end and a back-end database provides a level of security.