Data Access with ActiveX Data Objects - Advanced Programming Techniques - Excel VBA 24-Hour Trainer (2015)

Excel VBA 24-Hour Trainer (2015)

Part IV
Advanced Programming Techniques

Lesson 27
Data Access with ActiveX Data Objects

The topic of data access has become one of the most intensive forces in driving the recent development of commercial software applications. Data storage and search engine companies have become the face of the worldwide voracious demand for accessing information.

Excel is without peer in its powerful features for calculating and analyzing data, and in its ability to produce customized reports in an instant with VBA. For users who deal with extremely large volumes of source data, Excel can still fall short as a data storage application. Microsoft has built Excel with some robust methods for importing external data into your workbooks, making Excel a terrific front-end application that analyzes data it does not need to store.

Introducing ADO

ADO is an acronym for ActiveX Data Objects, which is the technology Microsoft recommends for accessing data in external databases. Excel's spreadsheets, being tabular row and column objects, share common features with database tables, providing a natural environment for data to be transferred between Excel and relational databases.

From Excel, using ADO you can do the following:

· Connect to most any external database in the Windows operating system, as long as that database has, as many do, an ODBC (Open Database Connectivity) or OLE DB (Object Linking and Embedding Database) driver.

· Add, delete, and edit records from a database to your workbook, or from your workbook to a database.

· Query data to return a recordset, enabling you to import some or all records from a database table directly to your worksheet, for whatever analysis you want to perform, just as if the data was already in Excel.

DEFINITIONS OF DATABASE TERMS

Because this lesson introduces concepts for external data access, it contains more descriptive theory about databases than actual code examples. In Lesson 32, you see several working examples of how Excel utilizes ADO and SQL in conjunction with Access databases. If you are unfamiliar with database terminology, the following definitions for common database terms might help you throughout this lesson.

A database is an organized collection of related information.

DAO (Data Access Objects) is a library of objects and their associated methods and properties that can be used to represent objects in databases, enabling Excel to interact directly with databases through VBA.

DBMS is an abbreviation for database management system. Popular examples of database management systems include dBASE, Paradox, and Microsoft Access.

A field is a column in a list such as in an Excel worksheet or Access database that describes a characteristic about records, such as first name or city.

ODBC (Open Database Connectivity) is a database standard that allows a program to connect to and manipulate a data source, enabling a single user to access many different databases.

A primary key is one or more fields that determine the uniqueness of each record in a database.

A query is a series of statements written in Structured Query Language to specify the tables and fields you want to work with that add, modify, remove, or return data from a database.

A record is a row of data in a table.

A recordset is one or more records (rows) of data derived from a table.

A relational database is a collection of data items organized as a set of formally described tables from which data can be accessed or reassembled in many ways.

NOTE Prior to ADO, Microsoft's primary recommended tool for accessing external data was an interface called DAO, or Data Access Objects. The DAO interface has become all but obsolete due to its limitations as compared to ADO, though DAO is still supported by ADO. The two technologies share many of the same code syntaxes but they are not the same in terms of flexibility and performance. You still do have a choice between the two, but you'll be much better served by ADO, which is why it is covered in this book.

With entire books devoted to database integration with ADO, there is much more complexity to the topic than this lesson is meant to cover. The best way to start becoming familiar with ADO is to examine the three primary tools in its object model: the Connection object, the Recordset object, and the Command object.

The Connection Object

The Connection object establishes a path that connects Excel and the database. With ADO from Excel, you normally issue commands that pass information back and forth through the Connection object. Among the key methods belonging to the Connection object are Open, which establishes the database connection, and Close, which closes the connection. The Connection object's ConnectionString property defines how to connect to the database.

You connect to the database with the Provider keyword. The following line of code is a common syntax for Excel versions 2007 through 2013:

Provider = "Microsoft.ACE.OLEDB.12.0;Data Source= _

C:\YourFilePath\Database1.accdb";Persist Security Info=False;"

In versions of Excel prior to 2007, the Provider would have been specified as the Microsoft Jet database engine of Access:

Provider = "Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\YourFilePath\Database1.accdb; Extended Properties=Excel 8.0;"

Or, depending on the circumstance, more simply:

Provider = "Microsoft.Jet.OLEDB.4.0"

NOTE When working with databases, you almost always connect to them, meaning you do not open them in the way you'd open a Word document if you were working with Word from Excel. TheConnection object is like a conduit between Excel and your database.

The Recordset Object

The Recordset object is probably the most commonly used object in ADO. When you instruct ADO to retrieve a single record or the entire count of records from a database table, you use the Recordset object to do that.

Among the key members of the Recordset object are the following:

· The ActiveConnection property, which is a connection string or a Connection object that identifies the connection being used to access the database. As with this property for the Command object, where objRecordset and objConnection are object variables, the ActiveConnection syntax is

Set objRecordset.ActiveConnection = objConnection

· The Open method opens the Recordset object so you can access the data. Its syntax is

Recordset.Open Source, ActiveConnection, CursorType, LockType, Options

Note that the Source argument is often a string that names the table from which the recordset should be retrieved.

· The Close method closes an open Recordset object. With the Recordset object declared as dbRecordset, the syntax for Close would be

dbRecordset.Close

The Command Object

The Command object holds information about the kind of task being run, which is usually related to action queries in Access, or procedures in SQL, which are described in the next section. A Command object can also return a list of data records, and is most often run with a combination of parameters, of which there are more than this lesson can possibly cover.

The Command object has three important properties:

· The ActiveConnection property, which, like the ActiveConnection property for the Recordset object, is a connection string or a Connection object that identifies the connection being used to access the database. For example, this syntax assigns a Connection object to the ActiveConnection property, where objRecordset and objConnection are object variables:

Set objRecordset.ActiveConnection = objConnection

· The CommandText property, which sets the command that will be executed by the database and will usually be an SQL string.

· The CommandType property, which tells the database how to interpret and execute the CommandText's instructions.

An Introduction to Structured Query Language (SQL)

Structured Query Language (SQL) is a database language used for querying, updating, and managing relational databases. SQL is used to communicate with the vast majority of databases that are commonly in use today.

SQL is a complex language in response to the rigid nature of table design in relational database construction. This lesson covers SQL's four basic operations of SELECT, INSERT, UPDATE, and DELETE. As a reminder of what I mentioned at the beginning of this lesson, you'll find several examples of these operations in Lesson 32 that show how to work with Access from Excel.

NOTE Notice that SQL statements, such as SELECT and INSERT, are shown in uppercase. This is a standard SQL programming practice and a good habit to get into from the start. The SQL code examples in this book are relatively small, but SQL code can be very large and complex. SQL is easier to read when its statements are shown in uppercase, which distinguishes them from the clauses of code with which they are associated.

The SELECT Statement

The SELECT statement retrieves data in the form of one or more rows (records) from one or more tables. The SELECT statement is probably SQL's most commonly used operation, because it tells the data source what field(s) you want to return from what table(s).

If you want to retrieve all columns and all rows from the Vendors table, the expression in SQL is as follows:

SELECT *

FROM Vendors

Sometimes you might not want to retrieve all columns. The following example retrieves the State column from the Vendors table, if you want to know the count of your vendors per state:

SELECT State

FROM Vendors

If you want to see a list of vendors and the names of their contact people, but only for vendors in California, the following example accomplishes that. Note that the literal string criterion California is in single quotes, which is SQL's required syntax:

SELECT VendorName, ContactName

FROM Vendors

WHERE State 'California'

If you want to retrieve the previous recordset by having it already sorted by the VendorName field, you could add the ORDER BY statement and specify the field name as follows:

SELECT VendorName, ContactName

FROM Vendors

WHERE State 'California'

ORDER BY VendorName

The INSERT Statement

The INSERT statement adds a new row (record) to a table. You need to specify the name of the table where the row will be added. You can optionally omit the field names from the INSERT statement, but it is advisable that you name them anyway because it helps you to see that the values you are entering are in the same order as the field names.

An example of using INSERT is this fictional pair of statements that respectively place the values 5432, Doe, John, Male into a table named Employees, for fields named EmployeeID, LastName, FirstName, and Gender:

INSERT INTO Employees (EmployeeID, LastName, FirstName, Gender)

VALUES ('5432', 'Doe', 'John', 'Male')

NOTE It's standard SQL programming practice to enter the statements in uppercase. It is mandatory SQL programming practice to place the string literal VALUES within single quotes, just as you see it here.

If you had opted to enter the preceding SQL code without naming each field, the syntax example for that same procedure would have been as follows:

INSERT INTO Employees

VALUES ('5432', 'Doe', 'John', 'Male')

The UPDATE Statement

The UPDATE statement enables you to change the values in one or more columns (fields) in a table. UPDATE is most commonly used to modify the value of a specific record that you identify with the WHERE clause. You also need to specify each column you want to change, and what each column's new value should be.

The following example shows how you could update the contact name of one of your company's vendors in the ContactName column of the Vendors table. You need to be careful to specify the WHERE clause so that only one record is changed, and that it is the correct record.

In the Vendors table, you have a field named VendorID that lists unique vendor identification numbers. The vendor name itself is Widgets, Inc., but that is not as important as its vendor identification number. Suppose that the vendor identification number for Widgets, Inc. is 1234. The new contact name is John Doe, executed with these three statements in SQL:

UPDATE Vendors

SET ContactName = 'John Doe'

WHERE VendorID = '1234'

If the ContactName field had many empty (referred to as Null) values, and you wanted to fill those empty spaces with the word Unknown, the following example would accomplish that:

UPDATE Vendors

SET ContactName = 'Unknown'

WHERE ContactName IS NULL

The DELETE Statement

The DELETE statement deletes one or more rows from a table. If you want to delete the vendor named Widgets, Inc., you would use the WHERE statement to specify which value in which column should identify the record for Widgets, Inc. The VendorID column is the perfect column for this task because a large company might have two vendors with the same name.

The following SQL statements would delete the record from the Vendors table that has the value 1234 in the VendorID column:

DELETE FROM Vendors

WHERE VendorID = '1234'

NOTE Make absolutely certain you specify the WHERE clause, because if you do not, every row from the Vendors table would be deleted. If an empty table is what you want, this fictional sequence would accomplish that:

DELETE FROM Vendors

Odds are, you don't want an empty table with all rows deleted from it. The kicker is that after the rows are deleted, you cannot undo that action as you can in Excel. Unless you are good friends with an experienced database programmer who might (or might not) be able to recover your unintentionally deleted rows, take heed and always specify the WHERE clause in your SQL DELETE actions.

Try It

This lesson introduced the fundamentals of ADO and SQL. You see several examples in Lesson 32 of VBA macros that show how to program ADO with SQL to interact with Access databases from Excel.

Here is a way to get a head start on the instruction in Lesson 32 to become familiar with database tables. Open Access and create a new database. Create a new table and enter some fictional data such as a mailing list with fields for FirstName, LastName, StreetAddress, City, State, Country, and Postal Code. Make a dozen or so entries and get a feel for navigating and editing a database table. For example, Figure 27.1 shows a table in Access being populated with hypothetical employee information, such as you might see in a company's personnel database.

image

Figure 27.1

You'll notice an important distinction between an Access table and an Excel worksheet. Database tables do not have row headers as numbers, or columns designated by letters. Columns (called fields in a database environment) rely on being identified by their field headers such as FirstName, LastName, and so on. Rows (called records) rely on being identified by one or more key fields, or certain properties of other fields such as being empty (Null) or having date entries between a start date and an end date.

You might also want to surf the Web for sites that list SQL objects and their associated properties and methods. Keep in mind that SQL's capacity for database interaction goes far beyond what you'll need it to do for your Excel projects, so stick with the basics for now when perusing SQL instructional material.

REFERENCE There is no video or code download to accompany this lesson.