Transcript Exploring Microsoft Office 2013 Access Comprehensive

by Mary Anne Poatsy, Keith
Mulbery, Eric Cameron, Jason
Davidson, Rebecca Lawson, Linda
Lau, Jerri Williams
Chapter 10
Using Macros and SQL
in Access
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
1
•
•
•
•
•
Understand the purpose of a macro
Create a stand-alone macro
Use the Macro Designer
Attach an embedded macro to an event
Identify when to use a data macro
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
2
•
•
•
•
•
Create an event-driven data macro
Create a named data macro
Understand the fundamentals of SQL
Interpret an SQL SELECT statement
Use an SQL SELECT statement as a record
source
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
3
• Macro – series of actions that can be
programmed to automate tasks
• Two categories of macros:
– Stand-alone macro – database object that you
create and use independently of other controls or
objects
– Embedded macro – executes when an event
attached to a control or object occurs
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
4
• Event – occurs when a user enters, edits or
deletes data or opens, uses, and closes forms and
reports
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
5
• Macro Designer enables you to create and edit
macros
• Four methods for adding actions to a macro
with the Macro Designer:
– Click Add New Action arrow and choose action
– Type the action name in directly
– Locate and double-click action in the Action Catalog
– Drag the action from the Action Catalog
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
6
• Argument – a variable, constant, or expression
that is needed to produce the output for an
action
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
7
• Two methods of creating embedded macros:
– Wizard – let Access create the macro for you
– Manually – click the ellipsis in the event box of the
control or object you are manipulating
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
8
• Data macros:
– Execute a series of actions when a table event
occurs or whenever a named data macro is executed
– Attach programming logic to tables
– Enable organizations to apply business logic to a
database
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
9
• Data macros:
– Can be used to validate and ensure the accuracy of
data in a table
– Can only be used with table events
– Cannot be used with other objects
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
10
• Two main types of data macros:
– Event-driven – triggered by table events
– Named – can be run from anywhere in the database
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
11
• Structured Query Language (SQL)
– Industry-standard language for defining,
manipulating, and retrieving data in a database
– Developed at IBM in the early 1970s
– Microsoft developed its own version of SQL for
Access
– All Access queries use an SQL statement to extract
data from tables
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
12
• SQL SELECT statement is used to retrieve
data from table in a database
• SQL keyword defines the purpose and
structure of an SQL statement
– Shown in UPPERCASE
• Four basic keywords:
– SELECT, FROM, WHERE, ORDER BY
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
13
• SELECT keyword – instructs Access to return
the specific field from one or more tables or
queries
– Example: SELECT fieldname
• FROM keyword – specifies the table(s) that
will be searched
– Example: FROM tablename
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
14
• WHERE keyword – specifies the criteria that
records must match to be included in the results
– Example: WHERE (Lastname=“Smith”)
• ORDER BY keyword – sorts the records by a
certain field
– Example: ORDER BY fieldname
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
15
• Forms and reports must have a record source
• Record source can be table, query, or SQL
• Basic structure of an SQL statement:
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
16
• Add an SQL Record Source to a Report
– Replace existing record source with SQL statement
• Copy an SQL Statement from SQL View
– SQL statements are in text format in SQL view and
can be copied and pasted into the record source of a
report
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
17
• In this chapter you learned to:
– Understand the purpose of a macro
– Create a stand-alone macro
– Use the Macro Designer
– Attach an embedded macro to an event
– Identify when to use a data macro
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
18
– Create an event-driven data macro
– Create a named data macro
– Understand the fundamentals of SQL
– Interpret an SQL SELECT statement
– Use an SQL SELECT statement as a record source
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
19
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
20
All rights reserved. No part of this publication may be reproduced, stored in a retrieval
system, or transmitted, in any form or by any means, electronic, mechanical, photocopying,
recording, or otherwise, without the prior written permission of the publisher. Printed in the
United States of America.
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall.
21