Access VBA

Microsoft Access VBA Training (2 days)

 

This Microsoft Access VBA training course will teach you how to manipulate your Access database using VBA (Visual Basic for Applications) code, automating the database.  Access VBA extends the features of the database, providing you with the full power of Visual Basic programming to manipulate Access objects.

This course is instructor led, involving the use of examples and exercises in a workshop environment in order to practice the techniques and methods that have been taught.

We offer four weeks free consultation on completion of this course.

 

OBJECTIVES

This course will ensure that you go back to your office as a confident, relaxed Access VBA programmer. It will lead you through programming and controlling Access using VBA using multiple examples to reinforce the concepts that you are being taught.

PREREQUISITES

The Access VBA course requires a working knowledge of Access – tables, forms, queries and reports.  No prior knowledge of Access macros or Visual Basic for Applications (VBA) is required.

 

COURSE CONTENT

 

THE VBA CODE EDITOR

This session concentrates on the VBA code editor that is contained within MS Access. Once you are comfortable with accessing the code editor it moves on to show you how to create the modules that you will use to hold your code. It then discusses the project explorer which you will work in when writing your VBA code, and also to look at sub-routines and commenting your VBA code.

The key concepts trained are:

  • Two ways to create modules
  • Using Project Explorer
  • Basic subroutines and comments

MESSAGES AND VARIABLES

This session focuses on using message boxes to communicate with and get input from users and also on the basics of variables.

The key concepts trained in this session are:

  • The all-powerful DoCmd object
  • Displaying messages
  • Getting input from users
  • Using variables

FUNCTIONS

Functions are one of the key elements of a VBA program. Functions take input data, carry out an operation on that data and then return a value when they complete. A simple example of a function would be a calculator to work out the area of a rectangle. The function would require as inputs the two different lengths of the sides. It would multiply them together and return the area of the rectangle.

The input data into a VBA function are known as arguments which are passed through a function.

The key concepts trained in this session are:

  • Passing arguments
  • Returning values

VISUAL BASIC EXPLAINED

Now that you have been introduced to the nuts and bolts of VBA code this session steps back a little bit. It explains at a high level how VBA programs are organised and manipulated.

This session introduces delegates to the object model in Access and also to collections of objects. It also introduces methods and property as well as other parts of the VBA framework.

The key concepts trained in this session are:

  • Objects and collections
  • Methods and properties
  • The Access object model
  • Getting help in VBA

ATTACHING CODE TO FORMS

Once you are comfortable writing VBA code the next step is to understand how to connect it to forms. This is the most user friendly way to access the power of VBA on a day-to-day basis. It allows non-programmers to access your code and also for you to run your code without opening the code editor.

The key concepts trained in this session are:

  • Attaching code to buttons
  • Attaching code to events
  • Making code not form-specific
  • Manipulating Form/Control Properties

LOOPING

Often within VBA code you want as certain task to be repeated many times. This session covers looping in detail including its application to forms and controls.

The key concepts trained in this session are:

  • Different types of loops
  • Looping over all open forms
  • Looping over controls
  • Looping over Access Objects

MODIFYING QUERIES

SQL (Structured Query Language) is the core database querying language. This session introduces SQL and then shows delegates how to amend the SQL of an Access query. This gives you complete control over queries in Access.

The key concepts trained in this session are:

  • Basic SQL statements
  • Running queries from VBA

RECORDSETS

Recordsets, along with SQL, are the most common way for VBA developers to interact with a database. A recordset is simply just a collection of records. They may either have come from a table, a number of tables or more often be the result of a query.

This session leads you through using recordsets to interact with your database. It starts by running through the differences between the major ways of using recordsets, ActiveX Data Objects (ADO) and Data Access Objects (DAO) before looking at the practicalities of adding, deleting and editing data.

The key concepts trained are:

  • Choosing ADO or DAO
  • Adding data
  • Editing data
  • Deleting data

DEBUGGING

  • Stepping through macros
  • Setting breakpoints

ERROR HANDLING 

  • Language errors
  • Runtime errors
  • Logical errors

LINKING TO EXCEL AND WORD

The beauty of VBA is it lies behind most Microsoft Office  products. This session covers how you can use Access to access other applications and also how you can manipulate Excel from Access using your VBA code.

  • Referencing other applications (Word, Outlook, SQL Server)
  • Manipulating Excel from Access

CASE STUDIES

  • Creating a course booking application
  • Simple Calculator
  • ATM