vbaccess2010 lesson01

8/12/2019 vbaccess2010 Lesson01

1/8

http://functionx.com/vbaccess2010/Lesson01.htm

Introduction to Microsoft Access and VBA

Databases and Libraries

Introduction

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 computerdatabase 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 areformally 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 a portable medium.

arious Libraries for a Database

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 oneor 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 abetter 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 toprogrammatically 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 variousmeans of performing the necessary operations on a database.

Microsoft ActiveX Data Objects: Microsoft ActiveX Data Objects, also called ADO, is a library that wasdeveloped to allow programmers with other environments to create and manage Microsoft Accessdatabases. 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 canuse 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. Thistechnology allows you to use one or more libraries of the .NET Framework and one or more of thelanguages 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. Forexample, while ADO contains objects and collections, ADO.NET does not own anything (because it is nota 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 useothers. One the external libraries you can use is called Win32. It belongs to Microsoft Windows and is


2/8

already installed with the operating system. Because most of its functions are written in C, they cannotbe directly used in a Microsoft Access database: you must import them.

Other Libraries: Besides the above libraries, Microsoft and other companies regularly publish otherlibraries 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++, Pascal, etc.

Microsoft Access

Introduction

In our lessons, we will use Microsoft Office Access 2010 to create computer-based databases. Probably

the easiest technique to launch Microsoft Access consists of clicking Start -> (All) Programs -> Microsoft

Office -> Microsoft Office Access 2010.

Practical Learning: Starting Microsoft Access Start Microsoft Access

Microsoft Access Databases

Introduction

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 2010 to create a database. You canopen either a database you previously created or one made by someone else.

isually Creating a Microsoft Access DatabaseThere are various ways you can create a database. To visually 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 2010 database, either omit or addthe .accdb extension. If you want to create a database that is compatible with previous versions ofMicrosoft Access, you must add the extension .mdb.

After specifying the name, 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. Afterselecting 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.

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

Creating a Database Using a Wizard

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


3/8

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

Deleting a Database

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. Thismeans that, after a database has been deleted, you may still see it in the left list of the Microsoft Access

interface. If a database has been deleted and you want to remove it from the MRU lists, open theRegistry (Start -> Run: regedit, Enter) and search (F3) for its name. It may be located in:

HKEY_CURRENT_USER

- Software- Microsoft

- Office

- 12- Access


4/8

- Settings

Locate the deleted database and delete its key.

Microsoft Visual Basic Fundamentals

Introduction

Most of the time, you can create a good performing database using only Microsoft Office Access 2010. Insome 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. Thisis a variant 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 Macro & Code section, you can click either theModule or the Visual Basic button.

Practical Learning: Opening Microsoft Visual Basic

1. On the Ribbon, click Database Tools


5/8

2. Click the Visual Basic button

BA and Macros

isual Basic Child Windows

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

The Project window displays the coding objects available for yourdatabase. 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. Toexpand 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 aredesigning 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 theProperties Window when the object is selected.

Since the Project window and the Properties Window usuallyshare the same section of the window (it is better that way), youcan 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:


6/8

On top, there are two combo boxes. To know the name of a combo box, you can position the mouse onit 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 theCode Editor can display.

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

main menu:


7/8

There are two small buttons on the left side of the horizontal scroll bar. The Full Module View button isused 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 ofMicrosoft Visual Basic, you can click View ->

Immediate Window

Practical Learning: Using Microsoft Visual Basic Windows

1. To display the immediate window, on the main menu of Microsoft Visual Basic, click View ->

Immediate Window


8/8

2. To use it, in the Immediate window, type ?nowand press Enter

3. To return to Microsoft Access, on the Standard toolbar, click View Microsoft Access

Closing Microsoft Access and Visual Basic

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 ViewMicrosoft 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 theStandard 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

Practical Learning: Ending the Lesson

To close Microsoft Access, click File -> Exit