Introduction to Microsoft Access and VBA

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.

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.

Microsoft ADOX: Microsoft ActiveX Data Object Extensions for Data Definition Language and Security, also called ADOX, is an addition to ADO. Besides many of the ADO operations it can perform, you can use it for additional assignments such as creating a database.

ADO.NET: ADO.NET is a 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 a database. 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 does not own anything (because it is not a library; it is only a concept of dealing with databases).

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 other companies regularly publish other libraries you can use to perform some tasks in your Microsoft Access databases. Additionally, you can create your own library, or ask someone else to create libraries for you, using languages such as C, C++, Pascal, etc.

In our lessons, we will be using Microsoft Office Access 2007 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 Office -> Microsoft Office Access 2007. You can also create a shortcut to your desktop or above the Start menu.

When Microsoft Access starts, it displays a window with a button in the top-left corner, followed by some buttons on the right side and the Microsoft Access name.

• Start Microsoft Access

When Microsoft Access starts, it displays the title bar that starts with the Office Button . The Office button can display a tool tip when the mouse is positioned on it:

If you click the Office Button, a menu appears:

This menu can be used to create a new database or to open an existing database, etc.

The Office Button displays the Quick Access Toolbar in the right section. You can hide the Quick Access toolbar if you right-click it and click Remove Quick Access Toolbar.

The Customize button appears after the Quick Access toolbar. When it gets clicked, a menu appears:

You can use it add or remove buttons on the Quick Access toolbar. To add a button, you can click the Customize button and click an item from the menu. To use an item that is not represented on the menu:

• Click the More Commands button
• Right-click the Quick Access toolbar and click Customize Quick Access Toolbar

This would open the Access Options dialog box:

If you want to add an item, you can click it in the middle list, and click the Add button. After making your selections, click OK 

When Microsoft Access freshly opens, its screen is divided in three sections. The left column displays a few labels where the top item is Online Templates. The middle section is made of two sections. The top part is used to quickly create a database:

The other section shows some information. The right side displays a list of recently opened databases. The bottom section of the Microsoft Access interface displays a status bar.

There are various types of databases you can use in Microsoft Access. You can create a database from scratch. You can use some objects that ship with Microsoft Access 2007 to create a database. You can open either a database you previously created or one made by someone else.

There are various ways you can create a database. To start a database from scratch, after launching Microsoft Access, you can click Blank Database. In the right section, accept or change the name of the database. If you want to create a Microsoft Access 2007 database, either omit or add the .accdb extension

If you want to create a database that is compatible with previous versions of Microsoft Access, you must add the extension .mdb. 

The name of the database follows the rules of files of Microsoft Windows. For example, the name can be made of letters, digits, spaces, and other allowed characters, up to 255 of them. After specifying the name, you should pay attention to the path. The path is the location, in your computer or your network, where the database as a file would be found. By default, the File Name text box selects the My Documents folder for a new database. In most cases, this would be enough. Otherwise, to specify a folder of your choice, under File Name and on the right side of the name of the database, click the Browse button . This would open the File New Database dialog box. You can click the arrow of the Save In combo box to select a drive such as (A:), (C:), etc:

After selecting the drive, you can either select an existing folder or create a new folder by clicking the Create New Folder button on the right side of the Save In combo box:

You can also use a directory on the network as the repository of the new database. To specify a network folder, if your computer is part of a network, you can click the arrow of the Save In combo box, and select Network Neighborhood or My Network Places:

From Network Neighborhood or My Network Places, select an existing directory or navigate the network until you reach the desired folder. In some cases, you might need to contact your IT department for the right directory to use.

After specifying a drive and a folder, you can click Create.

A database project is a database that is used to connect to a Microsoft SQL Server database. For such a project, the actual database resides on a server. The objects in Microsoft Access are used to let the user interact with the database because they are visual and user friendly while the objects in Microsoft SQL Server are mostly made of non-user-friendly lists only.

To start a project, create a blank database. When specifying the name of the database, add the .adp extension:

After specifying the file name (and the path), click Create. This would display a message box asking you whether you want to connect to a Microsoft SQL Server database:

If you already have the database you want to connect to, you would click Yes. This would open the Data Link Properties dialog box:

In the Data Link Properties, you can select the server, the type of authentication, and the database. Once you are ready, you can click OK. Microsoft Access would then attempt to connect to the database. If there is a problem, you would receive an error. If there is no problem, a connection would be established and you can continue working on, or finalizing the project.

If you did not have a Microsoft SQL Server yet but are working on a computer on which Microsoft SQL Server is installed, when the message box appears, you can click No. A Microsoft SQL Server Database Wizard would start:

This would allow you to create a database.

Microsoft Access ships with a few sample databases you can use and customize. To create a database from a template, after launching Microsoft Access, in the middle section, locate one under Featured Online Templates:

Click one of the buttons. Access or change the suggested name of the database, and click Download.

Alternatively, on the left side, click Local Templates, then, in the main section, select one of the samples under Local Templates:

1. On the left, click Local Templates
2. In the middle section under Local Templates, click Issues
3. In the right section of the screen, set the File Name to Technical Support1
4. Click Create

There are various ways you can open a database. If you click the Office Button and some databases were previously used, they would appear in the list. Here is an example:

If you see a database you want to use, you can click it. In a Windows utility such as Windows Explorer or My Documents, if you see a database you want to use, you can double-click it to open. If you receive a database by mail, you can also open it.

If you have a database you do not need anymore, you can delete it. To delete a database, in My Documents, in Windows Explorer or another file management application:

• You can click it to select it and press Delete
• You can right-click it and click Delete

A warning message would be presented to you to confirm what you want to do.

After you have deleted a database, it doesn't disappear from the MRU lists of Microsoft Access. This means that, after a database has been deleted, you may still see it in the right column menu of the Office Button or in the list under the Open Recent Database column. If you try opening such a database, you would receive an error. Here is an example from trying to open a database named Things To Do after it had been deleted (although it still appears under Open Recent Database):

If a database has been deleted and you want to remove it from the MRU lists, open the Registry (Start -> Run: regedit, Enter). Open the following key:

HKEY_CURRENT_USER
    - Software
      - Microsoft
        - Office
          - 12
            - Access 
              - Settings

Locate the deleted database and delete its key. 

Microsoft Office Access 2007 is equipped with an alternative to a main menu, the Ribbon:

If the Ribbon is taking too much space on your screen, you can reduce its size. To do this, click the arrow of the Quick Access button and click Minimize Ribbon:

The ribbon is a type of property sheet made of various property pages. Each page is represented with a tab. To access a tab:

• You can click its label or button, such as Home or Create
• You can press Alt or F10. This would display the access key of each tab:
   
  
  
  To access a tab, you can press its corresponding letter on the keyboard. For example, when the access keys display, if you press Home, the Home tab would display
• If your mouse has a wheel, you can position the mouse anywhere on the ribbon, and role the wheel. If you role the wheel down, the next tab on the right side would be selected. If you role the wheel up, the previous tab on the left would be selected. You can keep rolling the wheel until the desired tab is selected

To identify each tab of the ribbon, we will refer to them by their names.

Inside of a tab of the ribbon, the property page is divided in various sections, each delimited by visible borders of vertical lines on the left and right. Each section displays a title in its bottom side. In our lessons, we will refer to each section by that title. For example, if the title displays Font, we will call that section, "The Font Section".

Some sections of the Ribbon display a button . If you see such a button, you can click it. This would open a dialog box or a window.

When Microsoft Access is occupying a big area or the whole area of the monitor, most buttons of the Ribbon appear with text. Sometimes you may need to use only part of the screen. That is, you may need to narrow the Microsoft Access interface. If you do, some of the buttons may display part of their appearance and some would display only an icon. Consider the difference in the following three screenshots:

In this case, when you need to access an object, you can still click it or click its arrow. If the item is supposed to have many objects, a new window may appear and display those objects:

From this:

To this:

After creating or opening a database, unless the product is setup otherwise, the left section is occupied by a rectangular object called the Navigation Pane. The Navigation Pane is the central point of a database. It allows you to review the objects that are part of a database. You also use it to change the way the objects display, whether the objects should appear in categories, and what categories.

By default, the Navigation Pane appears as a rectangular box with a title on top, a yellow down-pointing button and a Shutter Bar Open/Close Button . If you want to minimize the Navigation button, you can click the Shutter Bar Open/Close Button . If you click it, the Shutter Bar Open/Close Button changes and the Navigation Pane becomes a vertical bar:

To expand the Navigation Pane again, you can click the Shutter Bar Open/Close Button or you can click the bar itself.

The top section of the Navigation Pane displays an All Tables label:

When you create a table, a section gets created for that table. The top section of the table holds its name. The name of a table is followed by a colon and Table:

In the same way, as you create more tables, each gets its own section in the Navigation Pane:

In Lesson 3, we will learn that you can create two types of forms. A form can be associated with a table or a form can be independent of any table. When a form is associated to a table, the name of the form appears under the name of the table. When a form is independent of any table, it appears in a section labeled Unrelated Objects. Here are examples:

In the same way, you can create many forms associated with a table and you can create many unrelated forms. 

When you create the tables, they are added to the Navigation Pane in the order you create them. When you create forms associated with a table, by default, the objects inside a category are arranged in alphabetical order. If you want, you can make them display in reverse alphabetical order. To decide on the order you want, you can right-click the name of a section, position the mouse on Sort By, and decide:

By default, when you create the objects that are associated with a table, the objects of the category display. This means the category is expanded. If you want to minimize a section, which is referred to as collapsing, you can click its bar.

If you want to hide a category, right-click its category header and click Hide. To hide all categories except a particular one, right-click the section header of that category and click the Show Only option. To select what categories to hide or what categories to reveal, right-click an empty area in the Navigation Pane and click Category Options. Then use the check boxes to decide.

We saw that you could expand or collapse a category in the Navigation Pane. If you want to expand all categories at once, you can right-click any section header and click Expand All. In the same way, if you want to collapse all categories, right-click any section header and click Collapse All.

The Navigation Pane gives you ample flexibility on how the objects appear in it and how the categories are organized. For example, you can show the tables only, the forms only, or all objects. One way you can decide is to right-click an empty area of the Navigation Pane and click Navigation Options. This would open the Navigation Options dialog box:

The Navigation Options dialog box allows you to do many things. For example, to decide what categories to show or hide, put or remove check marks next to their name in the right list. The dialog box also allows you to create new categories.

In Microsoft Office Access 2007, you can display the objects of the Navigation Pane forms using tabs or bordered windows. To specify this, after opening a database, click the Office button and click Access Options... In the Access Options dialog box, click Current Database in the left frame. In the right frame, click

If you click the Overlapping Windows option, the object would appear each with visible borders. Here are examples:

If you select the Tabbed Documents option, each object would be represented by a tab. Here is an example:

After making your selection, click OK. If you had changed from the setting the database was using, you must close and re-open it for the change to take effect (something to do with the Registry).

Help consists of requesting or getting assistance while working on a database. There are various types of assistance you can get in Microsoft Access. One of the ways you can get help is by clicking the Help button on the right side of the Ribbon and close to the right border of the Microsoft Access interface:

 

Tool Tips

One of the types of help you can get is through small boxes called tool tips. These small rectangles display when you position the mouse on a certain item, such as a button on the Ribbon, for a few seconds. In the following example, the mouse is positioned on the Portrait button and a short description appears: 

When you move the mouse away, the small box disappears (or closes itself)

Context-sensitive help refers to help provided on a specific dialog box on the screen. Such help is provided for objects that are part of Microsoft Access. Context-sensitive help is also referred to as “What’s This?”. To get context-sensitive help, press Shift + F1. This would call a help window that can describe or explain the object that is displaying. Another type of context sensitive help is provided in various dialog boxes. They display a button with a question mark on the left of the system Close button. To use this type of help, click the question mark button or .

Probably no matter how careful and meticulous you are, sooner or later, you will get errors on something you are working on. These errors display a square box with an exclamation mark:

If you position the mouse on it, a down pointing arrow is added to the right side of the button and if you click the button or its down pointing arrow, a menu would appear. One of the options on the menu is labeled Help on This Error:

You can then click Help on This Error to get some information on how to fix the error.

In various sections of our lessons, we will use an object called the Properties window, which allows you to change the characteristics of a control:

Some options of the Properties window could be difficult to figure out, especially if they are not explicit and if you are not familiar with them. Fortunately, to get help for any item of the Properties window, click it and press F1. The Help window would come up and would display one or more options on the topic you had clicked. If you see a link with the same name of the item you clicked, you can click that link and its explanation would display.

Online help is a program that provides help on Microsoft Access. There are two main types of online help:

• Microsoft Access ships with a help system. To use it, simply press F1
• If you have access to a Microsoft Developer Network (MSDN) CD-ROM or DVD, which is the help system provided to programmers who use Microsoft technologies (such as Microsoft Visual Studio), it includes a section on Microsoft Office

Although help on the Internet tends to be disparate, it is still the widest form of help available. This is provided in web sites, web pages, newsgroups, support groups, etc. As the publisher of the database environment, it is only natural to refer to Microsoft corporate web site first for help. The Microsoft web site is divided in categories. A web site is dedicated to Microsoft Access at http://www.microsoft.com/access. You can get help at http://support.microsoft.com. Probably the most visited site of Microsoft for developers of all Microsoft products is http://msdn.microsoft.com. This last site provides a tree list that presents items in categories (like the MSDN CD-ROM or a DVD).

Most of the time, you can create a good performing database using only Microsoft Office Access 2007. 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 code to complete your application.

In order to access Microsoft Visual Basic, you must first create or open a database in Microsoft Access. Then, on the ribbon, you can click the Database Tools tab. In the Database Tools section, you can click the Visual Basic button . This would open Microsoft Visual Basic:

As an alternative, in the Create tab of the ribbon, in the Other section, you can click the arrow of the Macro button and click either Module or Class Module.

1. On the Ribbon, click Database Tools
2. Click the Visual Basic button .

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, you can click View -> Project Explorer. The Project window 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 Code Editor is the area where you will mostly be working, this is the largest section of the Visual Basic Editor. It is mainly made of three sections:

On top, there are two combo boxes. To know the name of a combo box, you can position the mouse on it and a tool tip would come up:

The Object combo box allows you to select a particular object and access its events, actions that the object can launch. The Procedure combo box allows you to select an action, related to the object in the Object combo box, that you want to control.

The big and wide area is where you will be writing code. There are one vertical and one horizontal scroll bars that allow you to move left, right, up, and down in case your code is using more space than  the Code Editor can display.

The Code Editor uses default colors to show the code. To customize these colors, you can use the Editor Format property page of the Options dialog box that you can access from the Tools -> Options... on the main menu:

There are two small buttons on the left side of the horizontal scroll bar. The Full Module View button is used to display the whole associated with an object. The Procedure View button will display only the public procedures associated with the database.

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
    
   
    
   You may receive a message box informing you that the macros are disabled

1. If you receive it, click OK and return to Microsoft Access. In Microsoft Access, under the Ribbon, click the Options button. In the Microsoft Office Security Options, click the Enabled radio button:
    
   
    
   Click OK. Then return to Microsoft Visual Basic

For the rest of these lessons, every time you create a new database or open an existing database, open the Microsoft Office Security Options and Enable This Content.

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. When you are in the Code Editor 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.

Since Microsoft Access shares the same functionality you are probably familiar with from using other applications, you can close it easily.

• To close Microsoft Access, you can click the Office Button and then click Exit Access
• To close Microsoft Access from its title bar, you can click its Close button
• To close Microsoft Access like any regular window of the Microsoft Windows applications, you can press Alt + F4
• To close Microsoft Access using mnemonics, you can press Alt, F, X.