Introduction to Microsoft Access Libraries

1. Start Microsoft Access
2. From the resources that accompany these lessons, open the Exercise1 application

Microsoft Access Object Library is a library automatically available when you start a new database or if you open an existing database. To check its existence, on the main menu of Microsoft Visual Basic, you can click Tools -> References... In the Available References list of the References dialog box, you can see that the Microsoft Access Object Library is checked:

The Microsoft Access Object Library relies on classes to identify anything. The most fundamental class in this library is called Object (remember that Microsoft Access is not case-sensitive). Therefore, when using variables that would use this library, you can declare a variable of this class and then initialize it.

One of the most fundamental objects in the Microsoft Access Object Library is named DBEngine. Everything that exists in your database comes directly or indirectly from this object. Because this object is static (it does not change) and is automatically available when you start a new database (it is one of the default objects of the application), you do not need to declare a variable for it. If you want to use it, simply type its name and it is available.

When you start a database in Microsoft Access, you are said to have started a session. If one database is not enough for what you are currently doing, you can open another existing database or you can create a new one. In the same way, you can open as many databases as you want. The group of databases opened by a user is referred to as a session:

In Microsoft Access, a session is programmatically considered, or called, a workspace and it is represented by the Workspace object. When you start a new database, you get a default workspace. If necessary, you can create one or more additional workspaces. The group of workspaces you are using is stored in a collection called Workspaces. To identify the workspaces, the DBEngine class is equipped with an indexed property named Workspaces. Therefore, to identify a workspace inside a DBEngine object, access the Workspaces() property and pass either the index or the name of the workspace. The first workspace in the DBEngine object can be identified with DBEngine.Workspace(0). The second workspace can be identified as DBEngine.Workspace(1) and so on.

Microsoft Office 2007 ships with a library that can be used to perform all types of operations on a database and used throughout all applications of the Microsoft Office family. This library is called Microsoft Office Access Database Engine Object. Like every library, it has a version. In Microsoft Office 2007, it is the Microsoft Office 12.0 Access Database Engine Object Library.

The Microsoft Office 12.0 Access Database Engine Library is loaded by default when you start a new database in Microsoft Access. To check it, on the main menu of Microsoft Visual Basic, click Tools -> References...:

If you don't want to use the library, you can remove it from your database. To do that, simple remove the check mark on its check box. Once you have made the decision, click OK (or Cancel).

Like the other database libraries, the Microsoft Office 12.0 Access Database Engine Library relies on many objects and collections. The most top object of the Microsoft Office 12.0 Access Database Engine Library is called Access. One of the properties of the Access object is called Application. To reference the Microsoft Office 12.0 Access Database Engine Library in your application, declare a variable of type Access.Application. Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim ROSH As Access.Application
End Sub

After declaring the variable, you must initialize it using the Set operator. To do this, call the CreateObject() function. Its syntax is:

CreateObject(class,[servername])

This method takes two arguments. The first argument is the name of a class. For Microsoft Access, the class is Microsoft.Access. This class can be followed by the version number. For example, Microsoft Office Access 2007's version is 12. In this case, the class would be specified as Access.Application.12. Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim ROSH As Access.Application
    
    Set ROSH = CreateObject("Access.Application.12")
End Sub

In reality, you can pass the argument as Access.Application without the version. If you do this, the database engine would find out about the latest installation of Microsoft Access in your computer using the drivers installed.

When calling the CreateObject() object, only the first argument is required. If you are trying to use a database that resides on a server, pass the name of that server as the second argument.

As introduced in the first lesson, Microsoft Data Access Objects is a library published by Microsoft and used to create and manage databases. In previous versions of Microsoft Access, this library was automatically available. Starting with Microsoft Access 2000, this library was not loaded by default anymore. If you want to use it, you must remember to "load" it. 

Before using DAO, you can first check whether it is available for your database. If it is not and you want to use it, you must add it. To check it, from the Microsoft Visual Basic main menu, click Tools -> References... In the References dialog box, if the check box of the Microsoft DAO item is not checked, this would indicate that the library is not available for your database. If you want to use it, click the Microsoft DAO X.X Object Library check box:

If you decide to use the DAO library, you must remove the Microsoft Office 12.0 Access Database Engine Object Library because both libraries cannot coexist in the same database:

After selecting the library, click OK.

Microsoft created DAO in the beginning to provide a programming library for Microsoft Access. To allow other environments, including those that were not from Microsoft, to create and manage databases that could communicate with Microsoft Access, the company created a library called ActiveX Data Objects or ADO. ADO was meant to create and manage databases.

Although treated as one entity, Microsoft ActiveX Data Object is really many libraries grouped under one name. To use ADO in your database, open the References dialog box, locate the latest version of Microsoft ActiveX Data Objects X.X library and click its check box:

To bring it up to the list of loaded libraries, you can click the up pointing button above Priority. Once you are ready, click OK.

To use an object of the ADO library, you will declare a variable for it.

To use or access a database, a user typically launches it and opens the necessary object(s) from it. You too will need to access a database but with code. To programmatically access a database using the ADO library, you must first establish a connection. To support this, the ADO library provides a class named Connection. Based on this, before establishing a connection, declare a variable of type ADODB.Connection and initialize it using the New operator. This would be done as follows:

Private Sub cmdConnector_Click()
    Dim conConnector As ADODB.Connection
    
    Set conConnector = New ADODB.Connection
End Sub

After using the Connection variable, to release the resources it using, assign Nothing to it. This would be done as follows:

Private Sub cmdConnector_Click()
    Dim conConnector As ADODB.Connection
    
    Set conConnector = New ADODB.Connection

    Set conConnector = Nothing
End Sub

After declaring and initializing the Connection object, you can then open the connection. To support this, the Connection class is equipped with a method named Open. The syntax of the Connection.Open() method is:

Connection.Open ConnectionString, UserID, Password, Options

The first argument to this method is called a connection string.

A connection string is text made of various sections separated by semi-colons. Each section is made of a Key=Value expression. Based on this, a connection string uses the following formula:

Key1=Value1;Key2=Value2;Key_n=Value_n;

The first part of the connection string is called the provider. It specifies the platform used to handle the database. To specify this factor, assign the desired name to the Provider word. This would be done as:

Private Sub cmdConnector_Click()
    Dim conConnector As ADODB.Connection
    
    Set conConnector = New ADODB.Connection
    conConnector.Open "Provider=

    Set conConnector = Nothing
End Sub

Although there are various providers in the industry, there are two primary providers used in ADO. One of them is Microsoft SQL Server and it is represented by SQLOLEDB. Therefore, if your database will reside on a Microsoft SQL Server, you can specify the provider as follows:

Private Sub cmdConnector_Click()
    Dim conConnector As ADODB.Connection
    
    Set conConnector = New ADODB.Connection
    conConnector.Open "Provider=SQLOEDB"

    Set conConnector = Nothing
End Sub

The most common provider we will use is the Microsoft JET database engine. It is represented as Microsoft.JET.OLEDB.4.0 (case insensitive). You can use it to specify the provider as follows:

Private Sub cmdConnector_Click()
    Dim conConnector As ADODB.Connection
    
    Set conConnector = New ADODB.Connection
    conConnector.Open "Provider=Microsoft.Jet.OLEDB.4.0"

    Set conConnector = Nothing
End Sub

You can also include the name of the provider as its own string. To do that, you can include it in single-quotes:

Private Sub cmdConnector_Click()
    Dim conConnector As ADODB.Connection
    
    Set conConnector = New ADODB.Connection
    conConnector.Open "Provider='Microsoft.Jet.OLEDB.4.0'"

    Set conConnector = Nothing
End Sub

The second part of the connection string is referred to as the data source. It is represented by a factor named Data Source. To specify this factor, you can assign the name of the database with its extension to the Data Source factor. Here is an example:

Private Sub cmdConnector_Click()
    Dim conConnector As ADODB.Connection
    
    Set conConnector = New ADODB.Connection
    conConnector.Open "Provider='Microsoft.Jet.OLEDB.4.0';Data Source=Example.accdb"

    Set conConnector = Nothing
End SubP

If you provide (only) the name of the database, the database engine would look for it in the same folder as the application that called it. In the above example, the database engine would be look for it in the My Documents folder. If you want to refer to a database that is outside of the folder of the application that called this method, provide a complete path  of the database. This can consist of the drive, the folder(s), and the name of the database with its extension.

 Here is an example:

Private Sub cmdConnector_Click()
    Dim conConnector As ADODB.Connection
    
    Set conConnector = New ADODB.Connection
    conConnector.Open "Provider='Microsoft.Jet.OLEDB.4.0';" & _
		                "Data Source=C:\Exercises\Example.accdb"

    Set conConnector = Nothing
End Sub

If you are referring to a Microsoft Access Office 2007 database, specify the extension as .accdb. If you are referring to a database compatible with previous versions of Microsoft Access, specify the extension as .mdb.

To be safer, you can include the value of the data source in single-quotes:

Private Sub cmdConnector_Click()
    Dim conConnector As ADODB.Connection
    
    Set conConnector = New ADODB.Connection
    conConnector.Open "Provider='Microsoft.Jet.OLEDB.4.0';'Data Source=Example.accdb'"

    Set conConnector = Nothing
End Sub

Instead of directly passing a string to the Open() method, you can first declare a String variable, initialize it with the necessary provider/data source, and then pass that string variable to the Create() method. Here is an example:

Private Sub cmdConnector_Click()
    Dim strConnection As String
    Dim conConnector As ADODB.Connection
    
    Set conConnector = New ADODB.Connection
    strConnection = "Provider='Microsoft.Jet.OLEDB.4.0';'Data Source=Example.accdb'"
    conConnector.Open strConnection

    Set conConnector = Nothing
End Sub

Of course, you can create the string using any of the appropriate techniques you want. Here is an example:

Private Sub cmdConnector_Click()
    Dim strConnection As String
    Dim conConnector As ADODB.Connection
    
    Set conConnector = New ADODB.Connection
    strConnection = "Provider='Microsoft.Jet.OLEDB.4.0';"
    strConnection = strConnection & "'Data Source=Example.accdb'"
    conConnector.Open strConnection

    Set conConnector = Nothing
End Sub

In our introduction to databases in Lesson 8, we learned how to create an ODBC data source. To use such an object to open the database, include its name (and the credentials) in the connection string. Here is an example:

Private Sub cmdDataSource_Click()
    Dim conConnector As ADODB.Connection
    
    Set conConnector = New ADODB.Connection
    
    conConnector.Open "DSN=Exercise;UID=;PWD=;"

    Set conConnector = Nothing
End Sub

Remember that, in this case, the ODBC data source must have been created.

The connection object as defined above allows you to create a connection to either the database you are working on or another database that may be currently closed. As you can see from the above introduction, you must know the path to the database you want to access. In some cases, you may simply want to connect to the current database. To support this, the CurrentProject property of the Application object is equipped with a property named Connection. This gives you a convenient access to the connection of the currently opened database. Here is an example of invoking it:

Private Sub cmdCurrentConnection_Click()
    Dim conCurrent As ADODB.Connection
    
    Set conCurrent = Application.CurrentProject.Connection
End Sub

Remember that, after using a connection, you should release the resources it was using:

Private Sub cmdCurrentConnection_Click()
    Dim conCurrent As ADODB.Connection
    
    Set conCurrent = Application.CurrentProject.Connection

    Set conCurrent = Nothing
End Sub

When this code executes, it identifies the connection to the current database and stores it in a declared variable named conCurrent.

After creating a connection to a database, the next step is usually to specify what you want to do on the database. One of the most usual operations you can perform is to submit a SQL statement to it (the connection). This is also equivalent to executing the statement.

To execute a statement, the Connection class is equipped with a method named Execute. Its syntax is:

Execute CommandText, RecordsAffected, Options

The first argument, CommandText, can be a SQL statement. It can also be something else as we will see. The second and the third arguments are optional.

Here is an example:

Private Sub cmdConnector_Click()
    Dim conConnector As ADODB.Connection
    Dim strConnection As String
    Dim strStatement As String
    
    strStatement = "Blah Blah Blah"

    Set conConnector = New ADODB.Connection
    strConnection = "Provider='Microsoft.JET.OLEDB.4.0';"
    strConnection = strConnection & "Data Source='C:\Programs\Exercise1.accdb';"    

    conConnector.Open strConnection
    conConnector.Execute strStatement 

    Set conConnector = Nothing
    
End Sub

In this case, the strStatement string would be passed to the Execute() method of the Connection object that would execute it. Because the statement is created as a string and doesn't "belong" to Microsoft Access, it will not be checked until it must be executed. This means that it is completely your responsibility to formulate a valid statement. Microsoft Access cannot check, and will not assist you with, the validity of the statement, until the statement is executed.

When using a connection, it consumes resources that other applications may need. Therefore, after using it, you should close it and free the resources it was using so they can be made available to the other parts of the computer.

To provide the ability to close a connection, the Connection class is equipped with a method named Close. Therefore, to close a connection, call its Close() method. Here is an example:

Private Sub cmdDataSource_Click()
    Dim conConnector As ADODB.Connection
    
    Set conConnector = New ADODB.Connection
    
    conConnector.Open "DSN=Exercise;UID=;PWD=;"

    conConnector.Close
    Set conConnector = Nothing
End Sub

In previous lessons, we used Microsoft Visual Basic as our primary language to perform the necessary operations using code. That language is mostly used for its ability to handle computer applications for the Microsoft Windows family of operating systems. To provide a common approach to database development, a universal language was developed to apply it to any programming environment or any operating system. The Structured Query Language, or SQL, is a language used to create and maintain databases.

SQL can be pronounced Sequel or S. Q. L. In these lessons, we will consider the Sequel pronunciation. For this reason, the abbreviation will always be considered as a word, which would result in “A SQL statement” instead of "An SQL statement". Also, in these lessons, we will regularly write, “The SQL” instead of “The SQL language, as the L already represents Language.

The SQL is used by various database environments such as Microsoft Access, MySQL, Microsoft SQL Server, Oracle, Paradox, etc. As it usually happens, each environment adds its flavor to the language to adapt it to whatever needs to be done. Based on this, the SQL we will learn in these lessons has some differences with the way it is interpreted in other programming environments.

As it is common with other computer languages, the SQL comes with its syntax, vocabulary, and rules. The SQL is equipped with keywords that tell it what to do and how to do it. In these lessons, we will apply the SQL as it is implemented in Microsoft Access.

When using SQL, you write short or long sections of code and view its result. Code based on SQL is referred to as a SQL statement. In Microsoft Access, to open a window that you can use to create or use a SQL statement, on the Ribbon, click Create. In the Other section, click Query Design. This would display the Show Table dialog box where you can click Close (in future lessons, we will learn more about that Show Table dialog box):

To open the window that allows you to write code:

• Right-click the middle of the Query window and click SQL View
• On the Ribbon, in the Results section of the Design tab of the Ribbon, click the SQL button
• On the Ribbon, in the Results section, click the arrow of the View button and click SQL View

This would display a window with a default line of code. You can either edit it or delete the default code and replace it with your own code.
After writing your SQL expression, you can execute it to see the result. To do this, in the Results section of the Ribbon, click the Run button . After viewing the result, to return to the Query window:

• Right-click the title bar of the window and click SQL Code
• In the Results section of the Home tab of the Ribbon, click the arrow of the View button and click SQL Code

Other means of using creating and using SQL code include:

• On the Ribbon, click Create. In the Other section, click Query Wizard. Use the Query Wizard to create a query. When you finish, the database engine would generate the necessary SQL code for you. You can then modify that code to suit your desire
• In Microsoft Visual Basic, you can write a SQL statement as a string and pass it to the RunSQL() method of the DoCmd object
• You can write a SQL statement and pass it to a method of one of the DAO, ADO, or ADOX objects we will review

When writing an expression, SQL is not case-sensitive. This means that Case, case, and CASE represent the same word. This applies to keywords of the SQL or words that you will add in your expressions.

The most fundamental operator used in the SQL is called SELECT. This operator is primarily used to display a value to the user. In this simple case, it uses the following formula:

SELECT Value;

The value on the right side of SELECT must be appropriate and we will see examples in the next few sections.

When you execute a SQL statement in the Query window, its results are displayed in a spreadsheet. To be able to recognize a value, the top section of a column of the spreadsheet displays a label, called a caption. Here is an example:

If you create a simple SELECT Value; expression, Microsoft Access assigns a default caption to the column. In reality, the SQL allows you to specify a caption that would be used for the value. This is done using the following basic formula:

SELECT Value As Caption;

The words SELECT and AS are required. As mentioned already, SELECT would be used to specify a value and AS in this case allows you to specify a caption of your choice.

The value to select can be a number. Here is an example:

SELECT 48;

The caption can be made of a word but the word cannot be one of the SQL's keywords. Here is an example:

SELECT 48 AS Age;

This would produce:

You can also use non-literal characters or digits in the caption. If the caption is made of a combination of words, you can concatenate them to create one word. Here is an example:

SELECT 24.85 AS HourlySalary;

If you want the caption to display different words, you can include them between an opening square bracket "[" and a closing bracket "]". Here is an example:

SELECT 25.05 AS [Hourly Salary];

This would produce:

Instead of displaying one column, you may want to display more than one. To do this, you can separate them with commas. Here is an example:

SELECT 42.50 AS [Weekly Hours], 25.05 AS [Hourly Salary];

This would produce:

Besides a number, the value of a SELECT expression can be a string. If it is, you can include it between single-quotes. Here is an example:

SELECT 'Martens, Laurent' AS [Employee Name];

In the same way, you can mix number-based and string-based columns.

1. In  the Navigation Pane, right-click Payroll1 and click Design View
2. To use SELECT, change the statement as follows:
    

   SELECT 'Larens' AS LastName,38.50 AS [Weekly Hours],22.12 AS [Hourly Salary];

3. To view the result, on the Ribbon, click the Run button
    
   
4. To return to the code window, on the Ribbon, click the arrow of the View button and click SQL View

Algebra uses a type of ruler to classify numbers. This ruler has a middle position of zero. The numbers on the left side of the 0 are referred to as negative while the numbers on the right side of the rulers are considered positive:

A value on the right side of 0 is considered positive. To express that a number is positive, you can write a + sign on its left. Examples are +4, +228, +90335. In this case the + symbol is called a unary operator because it acts on only one operand.

The positive unary operator, when used, must be positioned on the left side of its operand, never on the right side.

As a mathematical convention, when a value is positive, you do not need to express it with the + operator. Just writing the number without any symbol signifies that the number is positive. Therefore, the numbers +4, +228, and +90335 can be, and are better, expressed as 4, 228, 90335. Because the value does not display a sign, it is referred as unsigned as we learned in the previous lesson.

To express a variable as positive or unsigned, you can just type it. here is an example:

SELECT +1250

As you can see on the above ruler, in order to express any number on the left side of 0, it must be appended with a sign, namely the - symbol. Examples are -12, -448, -32706. A value accompanied by - is referred to as negative.

The - sign must be typed on the left side of the number it is used to negate.

Remember that if a number does not have a sign, it is considered positive. Therefore, whenever a number is negative, it MUST have a - sign. In the same way, if you want to change a value from positive to negative, you can just add a - sign to its left.

Here is an example that uses two variables. One has a positive value while the other has a negative value:

SELECT -1250

We have seen that the SELECT keyword could be used to create a list of values. These values are separate of each other. You can also combine values to create a string or a value that is in fact an expression. For example, you can combine a first name and a last name to create a full name. An expression that combines columns can be performed on text-based columns. such as a first name being added to a last name to get a full name.

String concatenation consists of adding one string to another to get a new string. This is done using the & operator. The formula of the expression is:

String1 & String2

String1 and String2 must be recognizable strings. When this statement executes, String2 would added to the end of String1, resulting in String1String2. In the same way, you can add as many strings as you want by separating them with the & operator.

1. To concatenate strings, change the statement as follows:
    
   SELECT "Larens" & ", " & "Ernestine" AS [Employee Name], 38.50 AS [Weekly Hours], 22.12 AS [Hourly Salary];
2. To execute the statement, right-click the title bar of the window and click Datasheet View
    
   
3. To return to the code window, on the Ribbon, click View -> SQL View

The addition can be used to add one value to another. This is done using the + operator. Here is an example:

SELECT 412.48 + 66.84 AS Total;

This would produce:

The order you use to add two or more values doesn't matter. This means Value1 + Value2 is the same as Value2 + Value1. In the same way a + b + c is the same as a + c + b the same as b + a + c and the same as c + b + a. This means that the addition is associative.

1. To use the addition, change the statement as follows:
    

   SELECT "Larens, Ernestine" AS [Employee Name], 8.50 AS Monday, 9.50 AS Tuesday, 8.00 AS Wednesday, 8.00 AS Thursday, 8.50 AS Friday, Monday+Tuesday+Wednesday+Thursday+Friday AS [Weekly Hours], 22.12 AS [Hourly Salary];

2. To view the result, on the Ribbon, click the Run button

1. To return to the code window, on the Ribbon, click the arrow of the View button and click SQL View

The subtraction is used to take out or subtract one value from another value. It is essentially the opposite of the addition. The subtraction is performed with the - sign. Here is an example:

SELECT 1240 - 608

Unlike the addition, the subtraction operation is not associative. This means that a - b - c is not necessarily equal to c - b - a.

The multiplication can be used to multiply one value by another. This is done using the * operator. For example, to get the weekly salary of an employee, you can multiply the weekly hours by the hourly salary and get the result. As mentioned for the addition, the order of the operands is not important.

1. To use the multiplication, change the statement as follows:
    

   SELECT "'Larens, Ernestine" AS [Employee Name], 8.50 AS Mon, 9.50 AS Tue, 8.00 AS Wed, 8.00 AS Thu, 8.50 AS Fri, Mon+Tue+Wed+Thu+Fri AS [Weekly Hours], 22.12 AS [Hourly Salary], [Hourly Salary] * [Weekly Hours] AS [Weekly Salary];

2. To view the result, on the Ribbon, click the Run button

1. To return to the code window, on the Ribbon, click the arrow of the View button and click SQL View
2. Save and close the query window

The division is similar to cutting an item in pieces or fractions of a set value. Therefore, the division is used to get the fraction of one number in terms of another. The division is performed with the forward slash /. Here is an example:

SELECT 128 / 42

This would produce 3

When performing the division, be aware of its many rules. Never divide by zero (0). Make sure that you know the relationship(s) between the numbers involved in the operation.

In the above division, 128/42, the result is 3. When you multiply 42 by 3, as in 42*3, you get 126. In some cases, you may be interested in knowing the amount that was left out after the operation. The modulo operation is used to get the remainder of a division as a natural number. The remainder operation is performed with the MOD operator. Here is an example:

SELECT 128 Mod 42 AS [128 mod 42];

This would produce:

Like most computer languages, SQL uses parentheses to isolate a group of items that must be considered as belonging to one entity. For example, parentheses allow a procedure to delimit the list of its arguments. Parentheses can also be used to isolate an operation or an expression with regards to another operation or expression. For example, when studying the algebraic operations, we saw that the subtraction was not associative and could lead to unpredictable results. In the same way, if your operation involves various operators such as a mix of addition(s) and subtraction(s), you can use parentheses to specify how to proceed with the operations, that is, what operation should (must) be performed first. Here is an example:

SELECT (154 - 12) + 8 AS First, 154 - (12 + 8) AS Second;

This would produce:

As you can see, using the parentheses controls how the whole operation would proceed. This difference can be even more accentuated if your operation includes 3 or more operators and 4 or more operands.