If you have created databases and used macros to provide automation in Access, but now need to develop a more professional end result that is easy to update and maintain, this is the course for you.

For many people this is their first experience of programming, so there are no prerequisites in that direction. However, a good working knowledge of creating databases and using macros in Access is essential.

• Visual Basic for Applications - When to use VBA instead of macros
• VBA Language - Comments, Variables, Expressions and all the basic building blocks of VBA code
• Logic - Such as If...Then and Select Case
• Loops - For...Next, Do...Loop, For...Next
• Arrays - Creating arrays and passing them as arguments
• Debugging - Types of error, stepping options, watch points and conditional breakpoints
• Error Handling - Trapping errors, useful error handlers and logging errors
• DAO and SQL - Data Access Objects and Structured Query Language to manipulate data
• Class Modules - Creating, using properties and methods

The VBA Programming Environment

• When to use VBA instead of Macros

Programming in the VBA Language

• Coding in Visual Basic for Applications
• Commenting Code
• Line Continuation Character
• Using Variables and Expressions
• Naming Conventions
• Defining Variables and Assigning Values
• Automatic or Explicit Declarations
• The Variant Data Type; Other Data Types
• Branching and Looping commands
• Indenting Code; Branching
• If…Then; Select Case
• Looping; For….Next
• Do….Loop; For Each…..Next
• Using Arrays and Collections
• Typing in Code
• Optional and Named Arguments
• Passing Arrays as Arguments
• Code Compilation
• Conditional Compilation Directives
• Finding Bugs; Types of Error
• Debugging Code; Stepping Options
• Watch Points and conditional Breakpoints
• AutoData – A Debugging Feature

Error Handling in VBA

• Using VBA to Handle Errors Generally
• Trapping Errors; Writing Useful Error Handlers
• Other Error Messages; Logging Errors

Manipulating Data; Data Access Objects & SQL

• Data Access Objects; DAO Hierarchy
• Working with Objects and Collections
• Opening Databases Programmatically
• Database and Recordset Objects
• Recordset Objects; Recordset Field Objects
• Dynasets; Snapshots
• Manipulating Data with VBA Methods
• Finding Records in a Recordset
• Editing Data in a Recordset
• Transaction processing Commands
• Using Recordsets to Manipulate Imported Data
• Creating, Using and Deleting Queries from VBA
• Changing Database Definitions through VBA
• Using SQL to Manipulate Databases
• SQL Data Modification Statements
• Methods of Running SQL from VBA Code

Class Modules and Form-Event Procedures

• Creating a Class Module
• Using Class Properties and Methods
• Using Form Events; Form Events and Properties
• Code Behind Forms; Changing Form Properties
• Solutions: Soft Validation
• Creating Validation Event Procedures
• Changing the Message at Run-Time
• Moving to a Specific Form control
• Solutions: Value Derivation
• Creating New Form Instances
• Solutions: Tying Forms/Reports Together
• Using Form Class Modules

The course runs from 9:30am through to 4:30pm on both days. Refreshments are available from 9am.

We provide:

• Full course material
• Certificate of attendance
• 90 days post course support
• Note pad and pen
• Polos!

Also includes:

• Free on-site parking
• Refreshments throughout the day
• Lunch at a local restaurant
• Free WiFi

Our Norwich on-site parking (NR3 1UB) is free and situated 5 minutes walk along the river to our training centre at St James Mill. If you arrive by train it is about 20 minutes on the Riverside walk or a short taxi ride to get to us. View full directions for Norwich and all our other venues.

You will learn in a relaxed environment with a small group of up to 8 people. A specialist tutor will guide and encourage you through all the learning necessary to develop your skills and questions are always welcome.

• SQL Server - Programming and Development
• SQL Server - Reporting Services