Access Level 5: SQL Usage in Access & VBA

Level 5Full Day1 DayVersion 365Official CPE Provider

Go straight to the source with SQL inside Access. Write SELECT and action queries by hand, drive forms and reports with SQL, and run it from VBA to build powerful, data-driven applications.

Course Objective

Students will write SQL directly in Access, including SELECT and action queries, apply SQL in forms and reports, run SQL from VBA using recordsets, and build aggregate and calculated fields.

Prerequisites

To ensure your success, we recommend that you have experience with Access queries, forms, reports, and some exposure to VBA. Students can obtain this level of skill through our Access Level 1 through 4 courses. This course is technical since it involves SQL and VBA.

Course Outline

7 lessons · 23 topics

1Understanding SQL statements

Create a query in design view
Examine a few main clauses from a query
Write a SELECT query using SQL Language
Add the ORDER BY clause to the SELECT query
Add a WHERE clause to the SELECT query
Use the WHERE clause as a join

2Understanding SQL action queries

Create a query using design view
Write a CREATE TABLE query using SQL Language
Write an INSERT query using SQL Language
Write an UPDATE query using SQL Language
Write a DELETE query using SQL Language

3Create SQL only queries in Access

Create a UNION & a UNION ALL query

4SQL and its uses in forms & reports

Create a form/report and update its record source
Change the filter property of a form or report
Create a combo box or a list box

5Using SQL commands in VBA

Use the DoCmd.RunSQL method in VBA
Create a filter for a form using SQL in VBA
Create a filter for a report using SQL in VBA

6Creating and using Recordsets in VBA code

Create and initialize a Recordset object
Use the Find and FindNext method of the object
Loop through a Recordset object

7Working with calculations in queries

Create aggregate functions in queries
Create calculated fields in queries

Excel Solutions Team
Kirkwood Tower, 11757 Katy Fwy, Suite 1300, Houston, TX 77079
(713) 589-2655 · contactinfo@excelsolutionsteam.com · excelsolutionsteam.com

Official CPE Provider (TX State Board of Public Accountancy)
BBB Accredited since 2006 · 20+ years training Houston professionals