Introduction to DAO, ADO, ADOX, and SQL

Microsoft Access Object Library and VBA

Introduction

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.

In previous lessons, we demonstrated that Microsoft Access relied on objects and collections. Based on this, the objects used in DAO are stored in various collections. The main object of the DAO library is called DAO. One of the objects of the DAO library is called DBEngine. Its main purpose is to give access to the other objects of DAO. For example, to support the creation of a database, the DBEngne object is equipped with the CreateDatabase() method. Its syntax is: DBEngine.CreateDatabase(name, locale, options)

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

1. Start Microsoft Access and, from the resources that accompany this ebook, open the Exercise1 database
2. On the main menu of Microsoft Access, click Tools -> Macro -> Visual Basic Editor
3. On the main menu of Microsoft Visual Basic, click Tools -> References...
4. In the References dialog box, locate your latest Microsoft DAO Object Library version and click the check box to its left
    
   
5. Click OK

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 this ebook, 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 this ebook, 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 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.

A SQL Statement

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:

• To write a SQL statement:
  • In the Database window, you can click the Queries button and double-click Create Query in Design View
  • On the main menu of Microsoft Access, you can click Insert -> Query and double-click Design View in the New Query dialog box
  • On the Database toolbar, you can click the arrow of the New Object button, click Query, and double-click Design View in the New Query dialog box
  Any of these actions 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, you can right-click the top section of the Select Query window and click SQL View. This would display a window with a default line of code. You can either edit it or delete the default and replace it with your own code.
  After writing your SQL expression, you can execute it to see the result. To do this, on the Query Design toolbar, you can click the Run button . After viewing the result, to return to the Select Query window, on the main menu, you can click View -> SQL Code. Alternatively, on the Query Design toolbar, you can click the arrow of the View button and select SQL Code.
• You can use a wizard to assist you with formulating a SQL statement without manually writing code. Still, you will be able to modify the code produced by the wizard.
• You can write a SQL statement as a string and, to apply it, you can submit 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 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:

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.

Practical Learning: Introducing SELECT

1. On the Database window, click the Queries button
2. Right-click Payroll1 and click Design View
3. To use SELECT, change the statement as follows:
    

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

4. To view the result, on the Query Design toolbar, click the Run button
    
   
5. To return to the code window, on the Query Design toolbar, 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, on the main menu of Microsoft Access, click View -> Datasheet View
    
   
3. To return to the code window, on the main menu, click View -> SQL View
4.  

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.

Practical Learning: Using the Addition

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 Query Design toolbar, click the Run button
    
   
3. To return to the code window, on the Query Design toolbar, 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 *

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.

Practical Learning: Using the Multiplication

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 Query Design toolbar, click the Run button
    
   
3. To return to the code window, on the Query Design toolbar, click the arrow of the View button and click SQL View
4. 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.

The Modulo

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 ActiveX Data Objects

 

Introduction

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.

Using ADO

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.

ADOX Fundamentals

 

Introduction

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.

The Catalog Object of 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.

Practical Learning: Getting a Reference to ADO

1. On the main menu of Microsoft Access, click Tools -> Macro -> Visual Basic Editor
2. On the main menu of Microsoft Visual Basic, click Tools -> References...
3. In the Available References list check box of the References dialog box, click the check box of Microsoft ADO Ext. 2.7 For DDL And Security
    
   
4. Click OK
5. To return to Microsoft Access, on the Standard toolbar, click View Microsoft Access
6. In the Database window, click the Forms button then right-click Fundamentals and click Design View
7. Right-click the Create Database button and click Build Event...
8. In the Choose Builder dialog box, double-click Code Builder
9. To get a reference to ADOX, declare the following variable and make sure you set it to Nothing before the closing of the procedure:
    

   Private Sub cmdCreateDatabase_Click() Dim objCatalog As ADOX.Catalog Set objCatalog = Nothing End Sub

10. Save all

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:

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

1. To create a new database, change the event as follows:
    

   Private Sub cmdCreateDatabase_Click() Dim objCatalog As ADOX.Catalog Set objCatalog = New ADOX.Catalog objCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source='C:\Microsoft Access Database Development\Fundamentals.mdb'" MsgBox "A new Microsoft JET database named Fundamentals.mdb has been created" Set catConnection = Nothing End Sub

2. Return to Microsoft Access and switch the form to Form View
3. To create the database, click the Create Database button
4. Open Windows Explorer and open the C:\Microsoft Access Database Development folder to check that the database has been created
5. Return to Microsoft Access
6. Save and close the form

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.

A Data Source

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.

The Connection to a Database

 

Introduction

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:

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

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:

Provider=Microsoft.JET.OLEDB.4.0

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:

Provider='Microsoft.JET.OLEDB.4.0';Data Source='C:\Programs\Example1.mdb';"

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

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.

A Connection to the Current Database

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.

Executing a SQL Statement

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.

Closing a Connection

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