microsoft access 2010 tutorialMicrosoft Access is another Microsoft product, used for creating computer databases. It can be used on a Microsoft Windows operating system, on a web site, or on a portable medium. Though a good performing database can be created by using only Microsoft Office Access, you may sometimes require more complex databases and may need to perform functions such as automate certain processes and tie your database objects together. These can be achieved by programming and writing code techniques in VBA. Learn VBA for Microsoft Access will orient to the programming tools in Microsoft Office Access 2007 and will point you to some resources where you can learn more about VBA codes and scripts.

What is VBA?

Visual Basic for Applications or VBA is a subset of VB (Visual Basic) which runs inside one of the Microsoft Office applications. A VB application is launched as an application in its own right, whereas VBA is a hosted language and has to be launched by the host application, such as Word, Excel, or Access. VBA interacts very easily with the host application by pointing to the object library of the host application. An object library is a file that exposes all objects of the host application to VBA. Being able to reference these objects gives you the power to manipulate these objects by writing programs or codes.

VBA is very similar to VB, but it does not contain all the functionalities of VB since it is hosted within another environment. The hosting environment, such as MS Word or Access creates the foundation for using VBA.

Structure and Layout of VBA in Access

VBA is a modern programming language that is quite similar to other structured programming languages and is mainly meant for Microsoft Office and its associated applications. Microsoft Access comes with its own programming language called VBA or Visual Basic for Applications. Though VBA supports the look and feel of Microsoft’s Visual Basic, it is neither Visual Basic nor Visual Basic .NET. Access VBA is specifically designed for Microsoft Access and supports the Microsoft Access object model. Access VBA scripts provide dynamic aspects to a database form.

VBA is not a full object oriented programming language like C++, but it shares some of the same basic concepts. VBA consists of some core elements that form the basic framework of the VBA environment. Here we have discussed the core elements of VBA especially in relation to MS Access:

VBA code is contained in either sub or function procedures. Modules act as the containers of sub procedures and function procedures. In MS Access there are two kinds of modules:

  1. Standard Module: These are the modules displayed in the main navigation pane when the option Modules is selected in the menu of the main navigation bar. These modules are not associated with any particular object, such as form or report, and they can be called from any object.
  2. Class Modules: Class modules are associated with a particular object, such as forms or reports. In addition, developers can create their own class modules. Class modules look exactly the same as the standard module, however, they are not displayed in the main database window.

There are three different kinds of class modules:

• Form class modules

• Report class modules

• Custom class modules

Class modules are linked to their objects, and when exported, the module is exported with the object together. Forms and reports class modules contain sub and function procedures pertaining to the form or report only.

Conclusion

Macros can be used for simple tasks, such as opening or closing a form, but they cannot be used to build robust applications. Sooner or later you have to use VBA to accomplish programmatic tasks. With VBA you can create your own individual functions. This functionality is not possible with macros.  Also, when performance is an issue, VBA codes are faster than macros. So to produce a database that has a navigation interface, forms, sub forms, queries and reports, it is best to use VBA script or programming to integrate, connect, and automate all the functionalities of the database.

Excel VBA students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo