Transcript Slide 1

Lesson 13 – Advanced Database Features
THE
M
PROFESSIONAL
APPROACH
SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
I
C
R
O
S
O
F
T
ACCESS
2007
®
2
Lesson Objectives
•
•
•
•
•
Convert databases.
Manage import specifications.
Mange export specifications.
Use advanced navigation techniques.
Use security tools.
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
3
Convert Databases
Exercises
– Open a Previous Version of Access
– Convert a Previous Version of Access
– Convert a Database as a Previous
Version
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
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
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
5
Open a Previous Version of Access
• Must restart Access to see property changes
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
6
Convert a Previous Version of Access
• Conversion tools allow database to be
upgraded to 2007
• Version number appears in the Access title
bar
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
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
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
8
Managing Import Specifications
Exercises
– Saving Import Specifications
– Run a Saved Import Task
– Import a XML File
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
9
Saving Import Specifications
• Import Specifications
– Allows for easy repeating of tasks
– Details of saved task can be changed
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
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
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
11
Saving Import Specifications
• Advanced Import Specifications
– Save for later use
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
12
Run a Saved Import Task
• Manage Data Task dialog box
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
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
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
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
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
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
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
16
Export Data as an XML file
• Options control how and what files will be
created during the export
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
17
Run a Saved Export Task
• Exported data will not append to existing
files
• Can replace existing files
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
18
Export a Text File
• Users can export any recordset
• Set options to view file after export for
verification
• Notepad can open text files
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
19
Export a Text File
• Set options on how the text file will be
stored
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
20
Export an HTML Document
• Hypertext Markup Language (HTML)
– Standard for Web documents
– Viewable through a web browser
– Optional to save formatting and layout
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
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
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
22
Using Advanced Navigation Techniques
Exercises
– Custom Navigation Pane
– Set Application Option
– Create a Switchboard
– Navigate with a Switchboard
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
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
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
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
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
25
Create a Switchboard
• Switchboard
– Default title is name of the database
– A form
– Two parts
• Pages
– switchboards
• Options
– commands
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
26
Navigate with a Switchboard
• Can assign Switchboard to open when the
database opens
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
27
Navigate with a Switchboard
• Switchboard data is stored in a Table
– Changes can be made to the Table
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
28
Using Security Tools
Exercises
– Using the Trust Center
– Use the Database Splitter
– Encrypt and Decrypt a database
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
29
Using the Trust Center
• Trust Center
– Create Trusted
locations
• No security
warnings when
opening a
database
– Affects how new
and existing
databases
behave
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
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
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
31
Use the Database Splitter
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
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
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
33
Encrypt and Decrypt a Database
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
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.
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
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.
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.
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.
THE PROFESSIONAL APPROACH SERIES
© 2008 The McGraw-Hill Companies, Inc. All rights reserved.