Using MS Access with VBA

Microsoft 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:

• Objects: In VBA almost everything is considered as an object. Forms, reports, fields, queries and also applications are considered as objects. You can create an entire object that contains both data and the operations that the object can perform. It is possible to create relationships between these objects. You can also reference other library objects, for example, you can perform a Word mail-merge from within Access using the data from an Access database.
• Properties: This is the way an object looks to the user and behaves. We can change properties through programming, say change how a form looks on the screen or the color of a form.
• Methods: A method is an action that can be performed by or on an object. These are the actions we can call on the object to make it do something. For example we can move a form on the screen, by calling its Move method. A report object can be previewed or printed. A form object can be re-queried if the underlying data has changed.
• Event: An event is something that happens to an object. An event happens when the user does something and an action of the form follows that event. Say when we click on the ‘Close’ button, the particular screen or window will close. Most programs we write in VBA will be in response to an event happening. Events are used to connect programming logic to an application. Microsoft Access is based on the event-driven programming model. There is no need to write a main program that controls the flow of all the sub programs. This part is already built into MS Access, whether you use it or not. For example, when you open an Access form, the OnOpen event is raised. You can use this event to write a piece of code to initialize the form, or display certain parameters on the form based on certain filter conditions.

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.