Access and Excel Automation with VBA

This comprehension course will teach students how to leverage the power of VBA to automate the daily tasks in Access and Excel. For example, checking or posting records to the master database or merging data from different workbooks. After finishing the course, students will be able query data using the SQL language, and integrate the Access database and Excel spreadsheets seamlessly without using copy or paste functions.

What you’ll learn

VBA Programming Basics

  • Getting help in VBA
  • Create a subroutine
  • Create a function
  • Declare a variable
  • Create a constant
  • Comment your code
  • Objects, Properties and Methods 
  • Collections and Objects 
  • Using Exclamation Marks, Dots & Brackets

Working with VBA code editor

  • Using Project Explorer
  • Creating modules
  • Using Immediate Window

Messages and variables

  • Input boxes and message boxes
  • Assign values to variables
  • Declaring variables

Using built-in functions and statements

  • Using Excel worksheet functions
  • Using the MsgBox function
  • Using the InputBox function
  • Create and using custom functions

Working with Access Objects and Methods

  • What is an object in VBA
  • Objects Properties
  • Objects Properties
  • AccessObject Object
  • Using an Object Variable
  • The Me Property
  • Application Objects
  • Assign Values to Variables

Working with Access Data Object Model

  • Understanding DAO and ADO 
  • ADO Object Model 
  • Working with RecordSets 
  • Creating a RecordSet 
  • Close the RecordSet 
  • Finding Specific Records
  • RecordSet Boundaries
  • Moving through a RecordSet
  • Filtering a RecordSet
  • Editing Data in a RecordSet 
  • Adding a New Record 
  • Deleting Records

SCHEDULES
 
AEV4051 - 廣東話 08 May enrol
 
AEV4061 - 廣東話 07 Jun enrol
RELATING COURSES
  Access
  Access and Advanced Access
  Access SQL
  Advanced Excel and Excel VBA
  Excel VBA X ChatGPT/GPT
  Excel VBA + Advanced Excel VBA
  SQL for Data Analysis

Working with Excel Objects

  • The Cells property
  • The Offset property
  • The entireColumn and entireRow
  • The Value property
  • The Text property
  • The Count property
  • The Column and Row properties
  • The Address property
  • The Font property
  • The Border
  • The interior property
  • The Formula and FormulaR1C1

Introduction to SQL

  • Creating and Editing SQL
  • About Statements, Batches and Scripts
  • Executing and Parsing SQL Scripts
  • Using Comments
  • SQL Syntax and The Rules of SQL
  • About Keywords, Identifiers, Operators, Whitespace and Case
  • About the Semi Colon
  • SQL Conventions and Good Practice

Retrieving Data with SQL

  • Introducing Queries: The SELECT Statement
  • The Clauses of the SELECT Statement
  • Using FROM to Specify the Source Table(s)
  • Retrieving Entire Tables
  • Retrieving Specific Columns
  • Using ORDER BY to Sort the Output

Using WHERE to Filter Results

  • Working with Comparison Operators (=, >= etc)
  • Numeric and String Based Filtering
  • Filtering Based on Calculations
  • Eliminating Duplicate Results with DISTINCT
  • Extending Filters with AND and OR
  • Range Filtering using BETWEEN and IN
  • NULL and its Implications Explained
  • Matching Patterns with LIKE

Access and Excel Integration

  • Reading Access Data from Excel spreadsheets
  • Adding and editing Access Data in Excel
  • Producing Excel reports using Access data
  • Reading Excel data in Access