Introduction to Microsoft Access and VBA

A database is a list of items stored somewhere to make their values easy to access or retrieve. This means that a database can exist anywhere, including human or non-human memory. A computer database is a list or a group of lists created as a project. There are various ways and various types of applications used to create such a list. To make it more useful, special computer applications are formally developed to help create and manage computer databases.

Microsoft Access is an application used to create computer databases that can be used on a Microsoft Windows operating system, on a web site, or on portable medium.

Because there are many requirements and many options for computer databases nowadays, there are also various techniques of creating a database. Still, by its basic definition, a database is primarily one or more lists. How the list(s) is (are) created can depend on various circumstances. To make it possible to create databases, various libraries have been developed and you use one of these libraries to do a better job:

Microsoft Access Object Library: Microsoft Access provides its own mechanism for creating and managing a database. It provides most of the tools you need to start and complete a database project. Microsoft Access is also equipped with a library, the Microsoft Access Object Library that you can use to programmatically create and manage databases. This library is already available to you so you don't have to "load" it.

Microsoft Data Access Object: Microsoft Data Access Object, or DAO, is a library that ships with Microsoft Access and allows you to create, maintain, and manage databases. It also provides various means of performing the necessary operations on a database. Starting with Microsoft Access 2000, the DAO library is always available but it is not automatically loaded (made available to a new database). If you want to use, you must first add it to your project. 

Microsoft ActiveX Data Objects: Microsoft ActiveX Data Objects, also called ADO, is a library that was developed to allow programmers with other environments to create and manage Microsoft Access databases. To support this, it provides a driver that allows these other programming environments to "attach" their applications to a Microsoft Access database. Like Microsoft Access' own library, you can use ADO inside of Microsoft Access to fully create and manage a database. Unlike DAO, starting with Microsoft Access 2000, ADO is automatically loaded when you start a new database so you don't have to add it to your project.

Microsoft ADOX: Microsoft ActiveX Data Object Extensions for Data Definition Language and Security, also called ADOX, is an addition to ADO. For example, since you cannot inherently create a Microsoft Access database using ADO (that is, its objects), you would instead use ADOX.

ADO.NET: ADO.NET is the latest technique developed by Microsoft and that is part of the .NET Framework. This technology allows you to use one or more libraries of the .NET Framework and one or more of the languages of the .NET Framework to create and manage your project. Although its name includes ADO, ADO.NET is neither ADO nor a real library, it is a technique of creating and managing databases. For example, while ADO contains objects and collections, ADO.NET doesn't own anything.

Win32 API: A library is practically never complete. To complement those cited above, you can use others. One the external libraries you can use is called Win32. It belongs to Microsoft Windows and is already installed with the operating system. Because most of its functions are written in C, they cannot be directly used in a Microsoft Access database: you must import them.

Other Libraries: Besides the above libraries, Microsoft and companies other than Microsoft regularly published other libraries you can use to perform some tasks in your Microsoft Access databases. Additionally, you can also create your own library, or ask someone else to create libraries for you, using languages such as C, C++, Delphi, etc.

In our lessons, we will be using Microsoft Access to create computer-based databases. There are different techniques you can use to launch it. Probably the easiest technique consists of clicking Start -> (All) Programs -> Microsoft Access. You can also create a shortcut to your desktop or above the Start menu. When Microsoft Access starts, it immediately displays a dialog box that allows you to specify the primary operation.

Like a regular multiple application interface (MDI), Microsoft Access displays a menu in its top section. This main menu is made of the most regular categories such as File, Edit, View, Window, and Help:

To support databases, the main menu of MS Access also includes items such as Insert or Tools, etc. When you click an item from the main menu, it displays its sub-items. At times, the menu will depend on other windows that display on the screen.

Under the main menu is a toolbar. The most regularly displayed toolbar is called Database as you can see its check box if you right-click a toolbar or the main menu:

If you are working on a database, Microsoft Access may display the Database window:

In the lower section of the Microsoft Access interface, it displays a status bar with various sections.

Practical Learning: Starting Microsoft Access

• Start Microsoft Access

Most of the time, you can create a good performing database using only what Microsoft Access natively has to offer. In some other cases, to create a more complex database, you would have to write code. To support this, Microsoft Access ships with, and installs a programming environment named Microsoft Visual Basic. This is a flavor of the popular Microsoft Visual Basic with everything you need to write any type of code to complement your application.

There are various ways you can open Microsoft Visual Basic. The easiest way consists of clicking Tools -> Macro -> Visual Basic Editor. If you had no object selected, Microsoft Visual Basic would appear empty:

If you had an open database when you accessed Microsoft Visual Basic, it would appear with non-empty windows that would display contents related to the database in Microsoft Access:

Microsoft Visual Basic appears like a class application with a menu, a toolbar, and additional window. To know what a particular button is used for, position your mouse on top of one and view a tool tip.

• On the main menu of Microsoft Access, click Tools -> Macro -> Visual Basic Editor

Almost any section of Visual Basic is dockable, which means it can be moved on the screen to another location.

The Project Explorer displays the coding objects available for your database. If the Project Explorer is not displaying, on the main menu, click View -> Project Explorer. This window object is usually on the left of the window. To move it, you can click its title bar under the Standard toolbar, hold your mouse down and drag to the desired location. To position it back to its previous location, you can double-click its title bar. To expand or collapse the folders tree,  click the Toggle Folders button.

Every object and item of your database has characteristics, called properties, associated with it. You control those properties when you design the object. For example, when you are designing a form in Microsoft Access, you define what its caption would be. You can as well have access to these properties in Microsoft Visual Basic. These properties are displayed in the Properties Window when the object is selected. Since the Project Explorer and the Properties Window usually share the same section of the window (it is better that way), you can shrink one and heighten the other. To do that, position the mouse on the gray bar between both windows. When the mouse pointer turns into a short line with double arrows, click and drag.

The Immediate window is used to test code when necessary. To display it, on the main menu of Microsoft Visual Basic, you can click View -> Immediate Window

1. To display the immediate window, on the main menu of Microsoft Visual Basic, click View -> Immediate Window
2. To use it, in the Immediate window, type ?NOW and press Enter
    
   

The version of Microsoft Visual Basic we are using here is "For Applications". Indeed, you can create a fairly functional application with this version, but it is related to Microsoft Access (or Microsoft Office). When you are in the editor window of Microsoft Visual Basic, you can get back to Microsoft Access either from the View Microsoft Access button on the Standard toolbar, or by clicking a Microsoft Access object on the Taskbar. The shortcut to get back to Microsoft Access is Alt + F11. You can close Microsoft Visual Basic any time and keep Microsoft Access running. To do this, on the Standard toolbar of Microsoft Visual Basic, you can click the View Microsoft Access button to get back to the database. On the other hand, if you close Microsoft Access, Microsoft Visual Basic will be closed also.