Introduction to DAO, ADO, ADOX, and SQL
Microsoft Access Object Library and VBA
Microsoft Access ships with a language named Visual Basic For Applications, or VBA. This allows you to complement MS Access with code that can perform complex operations. This language is used throughout the Microsoft Visual Office family of applications, including Microsoft Excel, Work, PowerPoint, Visio, etc. This language is also used by applications published by companies other than Microsoft. An example is Autodesk that publishes AutoCAD. To customize the VBA language for our database environment, Microsoft Access includes a library called Microsoft Access Object Library.
The Microsoft Access Object Library is 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:
You should not attempt to remove this library.
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 is not loaded by default. 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, then 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 3.6 Object Library check box and click OK.
Because the DBEngine object is already recognized in the current database, you can omit it when calling the CreateDatabase() method.
The first argument can be the name of the database you want to create. If you provide only the name of the database, it would be created in the same folder where the action was initiated. For example, if you are working in a database already and create a new database by providing a name, the new database would be created in the same folder where the current database resides. Here is an example:
Private Sub cmdCreate_Click() CreateDatabase("Exercise.mdb", ...) End Sub
If you want, you can store the new database in a drive and folder of your choice. To do this, provide the complete path and the name of the database as the first argument. This would be done as follows:
Private Sub cmdCreate_Click() CreateDatabase("C:\Microsoft Access Database Development\Exercise.mdb", ...) End Sub
The second argument to this method is required and it specifies the language used for the database. This is also referred to as the collation. This argument is passed as a constant named value and can be one of the following:
Here is an example:
Private Sub cmdCreate_Click() CreateDatabase("Exercise.mdb", dbLangGreek) End Sub
The third argument of the DBEngine.CreateDatabase() method is used to pass some options to use when creating the database. This optional argument can be a constant specified as dbEncrypt, if you want the database to be encrypted. If you don't pass this constant, the database would not be encrypted.
If you want to specify the version of Microsoft Jet used in the new database, the options argument can be one of the following values: dbVersion10, dbVersion11, dbVersion20, or dbVersion30. If you don't specify one of these values, the dbVersion30 would be used.
If you want the database to be encrypted and you want to specify the version of Microsoft Jet used in the new database, combine the dbEncrypt constant with one of the version values.
When the CreateDatabase() method has finished, it returns a reference to the database that was created. In fact, you must obtain this reference. The database is recognized as the Database object of DAO. To get it, first declare a variable of type Database. Here is an example:
Private Sub cmdGetReferenceToDAO_Click() Dim db As DAO.Database End Sub
Because the DAO object is implied, you can omit it in your declaration and simply use the Database object as type. To get a reference to the new database, use the SET operator and assign the returned value of the method to your Database variable. Here is an example that creates a new database named Exercise.mdb in the current folder:
Private Sub cmdCreate_Click() Dim db As DAO.Database Set db = CreateDatabase("Exercise.mdb", dbLangGeneral) 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.
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 this ebook 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 this ebook, we will apply the SQL as it is implemented in Microsoft Access.
When using SQL, you write short to long sections of code and view its result. Code based on SQL is referred to as a SQL statement. In Microsoft Access, there are four main ways you can create or use a SQL statement:
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:
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 Select Query window, its results are displayed in a spreadsheet. To be able to recognize a value, the top section of the a column of the spreadsheet displays a label, called a caption. If you create a simple SELECT Value; expression, Microsoft Access assigns a default caption to the value. 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:
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.
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:
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:
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.
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.
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.
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.
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. Starting with Microsoft Access 2000, the ADO library is included, or "loaded" by default when you start a database. To check this, you can open the References dialog box
Notice that a check mark is already placed in the Microsoft ActiveX Data Objects Library check box. You should check the Microsoft web site regularly to find out if the company has released a new version of the library. If it has, you should download it to update yours.
Before using ADO, you should first create a database using an application such as Microsoft Access or by other related means. Among the limitations of ADO in the beginning, there were security and even the ability to create a database without necessarily using a product from Microsoft. To address these issues, Microsoft created an additional library called Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security, abbreviated ADOX. Before using ADOX, you must reference it in Microsoft Visual Basic. To do this, you can open the References dialog box from the Tools menu and select your latest version of Microsoft ADOX.
Like Microsoft Access' own library and like DAO, ADO relies on objects for its functionality. The most fundamental object used in ADO databases is ADOX. To use this object, you must create a reference to it. To do this, first declare a variable of type ADOX.
To support the creation of a database, the ADOX library provides the Catalog object. To access this object, the ADOX object is equipped with a property called Catalog. This property defines most of the objects you will need to use in your databases. To use the Catalog property, specify it when declaring the ADOX variable. Here is an example:
Private Sub cmdAction_Click() Dim objCatalog As ADOX.Catalog End Sub
Before using the Catalog property of the ADOX object, you must specify that you need a reference to it, this can be done by initializing your variable with ADOX.Catalog using the New operator. Here is an example:
Private Sub cmdAction_Click() Dim objCatalog As ADOX.Catalog Set objCatalog = New ADOX.Catalog End Sub
You can also refer to this property when declaring the variable. Here is an example:
Private Sub cmdAction_Click() Dim objCatalog As New ADOX.Catalog End Sub
Whenever you create an ADO or ADOX object and while it is being used, it consumes resources. Therefore, after using the object, you should/must remove it from memory (if you don't, you may start seeing abnormal behavior on your computer after a while, as resources become scarce for other objects or applications are trying to use the same resources). To remove a resource from memory, after using it, assign it the Nothing constant.
Once you have gotten a reference to the object you want to use, you can perform a related operation. The primary operation you can perform consists of creating a database. This is done using the Catalog property of the ADOX object. To create a database, you can call the Create() method of the Catalog. This would be started as follows:
Private Sub cmdAction_Click() Dim objCatalog As ADOX.Catalog Set objCatalog = New ADOX.Catalog objCatalog.Create End Sub
This method takes as argument a string, also called a connection string, that defines the assignment to perform. The string itself is internally made of different sections separated by a semi-colon.
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 cmdAction_Click() Dim objCatalog As ADOX.Catalog Set objCatalog = New ADOX.Catalog objCatalog.Create "provider=" 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 SQL Server, you can specify the provider as follows:
Private Sub cmdAction_Click() Dim objCatalog As ADOX.Catalog Set objCatalog = New ADOX.Catalog objCatalog.Create "provider=SQLOEDB" End Sub
The most common provider we will use is the Microsoft JET database engine. It is represented as Microsoft.JET.OLEDB.4.0. You can use it to specify the provider as follows:
Private Sub cmdAction_Click() Dim objCatalog As ADOX.Catalog Set objCatalog = New ADOX.Catalog objCatalog.Create "provider=Microsoft.Jet.OLEDB.4.0" 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 cmdAction_Click() Dim objCatalog As ADOX.Catalog Set objCatalog = New ADOX.Catalog objCatalog.Create "provider='Microsoft.Jet.OLEDB.4.0'" End Sub
If you are creating a SQL Server type of database, there are various other details you must provide to the connection string. For the rest of our lessons, we will use Microsoft JET. When using Microsoft JET, the second part of the connection is referred to as the data source. If you are creating a database, you can specify the path and the name of the database to the Data Source factor. The path must consist of the drive and the folder(s). After the last folder, the name of the database must have the .mdb extension. For example, to create a database called Exercise that would reside in a folder called Exercises of the C: drive, you can specify the connection string as follows:
Private Sub cmdAction_Click() Dim objCatalog As ADOX.Catalog Set objCatalog = New ADOX.Catalog objCatalog.Create "provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Exercises\Exercise.mdb" End Sub
To be safer, you can also include the value of the data source in single-quotes:
Private Sub cmdAction_Click() Dim objCatalog As ADOX.Catalog Set objCatalog = New ADOX.Catalog objCatalog.Create "provider=Microsoft.Jet.OLEDB.4.0;'Data Source=C:\Exercises\Exercises.mdb'" End Sub
Instead of directly passing a string to the Create() 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 cmdAction_Click() Dim objCatalog As ADOX.Catalog Dim strCreator As String Set objCatalog = New ADOX.Catalog strCreator = "provider=Microsoft.Jet.OLEDB.4.0;'" strCreator = strCreator & "Data Source=C:\Exercises\Exercises.mdb'" objCatalog.Create strCreator End Sub
Although a database is primarily considered as a project that holds one or more lists of items, there can be other issues involved such as how the data would be made available to the users, what computer(s) would access the data, what types of users (physical or else) would access the database. A database is started as a computer file before being presented to the user(s). The database could reside in one computer and used by one person. A database can also be stored in one computer but accessed by different computers in a network. Another database can be created and stored in a server to be accessed through the Internet. These and other related scenarios should be dealt with to create and distribute an effective database.
You may plan to create a database that would be used by one person using one computer. As your job becomes more effective, you could be asked to create another database that would be accessed by different people. Regardless of why and how, after creating a database, you should have a way of making it available to those who would use. To do this, you must create a data source. With Microsoft Access on Microsoft Windows, you have two main options.
In Microsoft Access, a database is created as a computer file and it has a path, that is, where the database file is located. The path to a file is also known as its location. The path to a database, including its name, is also called the data source. In some of your database operations, you might be asked to provide a data source for your database. In this case, provide the complete path followed by the name of the database.
Using the path to a database is probably the easiest way to access it but this is more convenient if you are working in Microsoft Access. If you plan to access your database from another programming environment, then you should create an ODBC data source. To do this, in the Control Panel or the Administrative Tools, double-click Data Source (ODBC) to open the ODBC Data Source Administrator:
To proceed, click the Add button. This would launch a wizard. In the first page of the Create New Data Source wizard, click Microsoft Access Driver (*.mdb):
Click Finish. In the following screen, you would be asked to enter a name for the data source. You can enter the name in one or more words. The name would be used by the applications that need to access the database. This means that you should pay attention to the name you give. In the Description text box, you can enter a short sentence anyway you like. To specify the database that would be used, click Select and select an mdb database:
After selecting the necessary database, if you need to be authenticated in order to use the database (if the database is protected), click the Advanced button:
By default, a database is meant to allow anybody to use. In this case, you can leave the Login Name and the Password empty. Otherwise, type the necessary credentials. After using the Set Advanced Options dialog box, click OK (or Cancel to keep it the way it previously was).
After entering the necessary information and selecting the desired database, you can click OK twice.
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 an object called 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 declaring and initializing the Connection object, you can then open the connection. To support this, the Connection object is equipped with the Open() method. 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.
The 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:
One of the expressions you can specify in the connection string is the name of the provider. To do this, type Provider= followed by the provider you are using. For most databases we will create or use here, the provider will be Microsoft.JET.OLEDB.4.0. This means that our connection string would start with:
You can also include the name of the provider in single-quotes to delimit it. If you were working on an MSDE or a Microsoft SQL Server database, the provider would be SQLOLEDB.
The second part of the connection string specifies the data source. To provide this information, you can assign the path and name of the database to the Data Source attribute. Here is an example:
The provider and the data source are the most commonly necessary pieces of information. It is important to note that the contents of the connection string differs from one provider to another. If you were working on a Microsoft SQL Server database, your connection string would different from the above done for a Microsoft Access database.
For the Connection.Open() method, the connection string is the only required argument. If you are working in a secure environment and the database requires authentication, then you should (must) provide some credentials. To do this, you must provide a user name as the UserID argument and possibly a password as the Password argument. The last argument allows you to indicate if you want the connection to return a value upon establishing the connection.
Here is an example of opening a connection
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:\Programs\Example1.mdb';" End Sub
Instead of directly passing a connection string to the Open() method, you can first declare a String variable, initialize it with the connection string, and then pass it to the Open() method. Here is an example:
Private Sub cmdConnector_Click() Dim conConnector As ADODB.Connection Dim strConnection As String Set conConnector = New ADODB.Connection strConnection = "Provider='Microsoft.JET.OLEDB.4.0';" strConnection = strConnection & "Data Source='C:\Programs\Exercise1.mdb';" conConnector.Open strConnection End Sub
If you (or someone else) had created an ODBC data source and want to use it 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=;" 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 need to have your connection point 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
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 object is equipped with the Execute() method. 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 in future lessons. 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.mdb';" conConnector.Open strConnection conConnector.Execute strStatement End Sub
In this case, the strStatement string would be passed to the Execute() method of the Connection 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.
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 close a connection, the Connection object is equipped with the Close() method. After closing a connection, to free the resources it was using, assign Nothing to it. This would be done as follows:
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
|Previous||Copyright © Yevol, 2007||Next|