Working with Data in Office Solutions

  • Article

 

David Shank
Microsoft Corporation

January 13, 2000

Regardless of which Office application you use to build your solution, you are at some point going to have to work with data. The primary role of most Office solutions is to turn raw data, often from a broad variety of sources, into usable information. This can include data stored in Office applications, such as Microsoft Access databases, Microsoft Excel workbooks, or Microsoft Outlook folders, as well as data from other sources, such as Microsoft SQL Server databases, HTML tables, or tabular text files.

In previous versions of Office, developers used Data Access Objects (DAO) to programmatically access data. Office 2000 applications continue to provide support for DAO through the Microsoft DAO 3.6 object library, so you can run existing solutions that use DAO, as well as create new solutions that use DAO.

In Microsoft Office 2000, you will mainly use ActiveX Data Objects (ADO) to work with data programmatically. ADO supports a broader array of data sources than the DAO programming model and is the recommended data access technology. You can also use both ADO and DAO code in the same solution if you want.

This month's column is the first of a two-part discussion about using ADO to access data in an Office solution. In this column, I'll give a brief overview of ADO along with some specific examples of how to work with databases, Recordset objects, and the data contained in a recordset. Next month, I'll talk about searching, sorting, and filtering data and working with queries.

ADO Overview

Microsoft Office applications have always supported a broad range of data formats and data access technologies, and Office 2000 is no exception to this trend. However, all Microsoft products that support data access are converging on a new data access strategy called Universal Data Access. The primary technologies used to implement Universal Data Access are the low-level data access component architecture, called OLE DB, and the higher-level programming interface to OLE DB, ADO.

OLE DB is Microsoft's system-level data access interface to data. It's an open specification designed to build on the success of Open Database Connectivity (ODBC) by providing an open standard for accessing an even wider variety of data. Where ODBC was created to access only relational databases, OLE DB is designed for both relational and non-relational data sources, including mainframe and hierarchical databases; e-mail and file system stores; text, graphical, and geographical data; custom business objects; and others.

OLE DB consists of a collection of COM interfaces to various database management system services. These interfaces define the underlying architecture for the creation of software components that implement these services. As an Office solution developer, the primary thing you need to know about OLE DB is that it provides access to a particular data source by using a COM component called a data provider, which is often referred to as an OLE DB provider. You can think of an OLE DB provider as being much like an ODBC driver for a particular data source, with two exceptions: OLE DB providers can support access to a broader variety of data sources, and similar ADO code can be used to work with data exposed by any OLE DB provider.

There are some limitations to using ADO against Access databases that may require you to continue to use DAO:

  • In the Access object model, the Recordset property of a Form object can be used to request or specify a Recordset object for the data displayed in a form. If you request the Recordset object for the current form in an Access database, Access always returns a DAO Recordset object. Therefore, you must continue to use DAO code to work with the Recordset object that is returned.
  • When you use the Recordset property to set the Recordset object of a Form object to a Recordset object you created, and you set the Form object to an ADO Recordset object, the data will be read-only. If you want the data to be writable, you must set the Form object to a DAO Recordset object.
  • To read and set database properties in an Access database, and to read and set certain table properties, such as the Description and Filter properties, you must continue to use DAO code.
  • It's not possible to exchange information between ADO and DAO code. For example, if a DAO procedure returns a Recordset object, there is no way to translate or pass that DAO Recordset object to ADO code, and vice versa; an ADO Recordset object can't be read by or translated to DAO. However, this doesn't mean that ADO can't work with saved database objects, such as tables and queries that were created with DAO, and vice versa. But it does mean that although ADO and DAO can coexist in the same project, you can't use ADO code to work with objects returned by preexisting DAO code. You must either continue to use DAO code, or rewrite those procedures by using ADO code.

If you are upgrading from a previous version of Office, your existing DAO code will continue to work. If you want to also use ADO code, you must set a reference to the Microsoft ActiveX Data Objects 2.1 Library. When you create a new Access 2000 database, you get a reference to ADO by default.

Getting Connected

You use the ADO Connection object to open a database using ADO. When working with an Access database you must set the Provider property to the Microsoft Jet 4.0 OLE DB provider before you attempt to open the Connection object. By default, the Microsoft Jet 4.0 OLE DB Provider opens a connection to a database in shared-access mode. The following example illustrates how to establish a connection to the Northwind sample database:

Dim strPath as String
strPath = "c:\program files\Microsoft" _
       & " office\office\samples\northwind.mdb"

Call OpenDataSource(strPath)

Sub OpenDataSource (strDBPath As String)
   Dim cnnDB As ADODB.Connection

   ' Initialize Connection object
   Set cnnDB = New ADODB.Connection
   ' Specify Microsoft Jet 4.0 Provider and then open the
   ' database specified in the strDBPath variable.<
   With cnnDB
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
      ' Code to work with database goes here.
   End With

   ' Close Connection object and destroy object variable.
   cnnDB.close
   Set cnnDB = Nothing
End Sub

You can also specify provider information, as well as other Connection object properties, using the ConnectionString property. For example, the following code opens an Excel spreadsheet, Sheet1.xls, as an ADO database:

Dim cnn As New ADODB.Connection
Dim strConnString As String
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=Sheet1.xls" & _
      ";Extended Properties=Excel 8.0;"
cnn.Open strConnString
' Code to work with database goes here.
cnn.Close
Set cnn = Nothing

If you are writing code in an Access 2000 module and you want to write ADO code to work with the current Access database, you use the Access CurrentProject object's Connection property:

Dim cnnDB As ADODB.Connection
' Get connection to current database.
Set cnnDB = CurrentProject.Connection
' Code to work with database goes here.

cnnDB.Close
Set cnnDB = Nothing

Working with Data

The ADO object model supports a rich set of features for organizing, sorting, searching, updating, adding, and deleting data from diverse data sources. The methods and properties of the ADO Recordset object give you a great deal of control over records in a database. In addition to these methods and properties, you can use the methods and properties of the Recordset object's Fields collection and the Field object to work with data at the field level.

Opening a Recordset Object

To open an ADO Recordset object, you need to have a Connection object. You can get an explicit Connection object as illustrated in the previous examples. You can also pass the information needed to create the Connection object as an argument to a Recordset object's Open method. The following example illustrates the second technique where the Connection object is created implicitly as a result of information provided in the ActiveConnection argument of the Open method:

' Create a Recordset object without explicitly 
' creating a Connection object 
Dim rst As ADODB.Recordset
Dim strConnect As String
Dim strSQL As String

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=c:\program files\office\" _
       & "Microsoft office\samples\" _
       & "northwind.mdb"
strSQL = "Select * FROM Customers WHERE Region = 'WA'"

Set rst = New ADODB.Recordset
rst.Open Source:=strSQL, ActiveConnection:=strConnect

' Code to work with records goes here.

Set rst = Nothing

You can also specify the name of a Table of Query in the Source argument of the Open method. For example, the following code creates a Recordset object containing all the data from the Employees table:

Set rst = New ADODB.Recordset
rst.Open Source:="Employees", ActiveConnection:=strConnect

' Code to work with employee records goes here.

Set rst = Nothing

A Recordset object can refer to only one record within the set as the current record. The software functionality that lets you work programmatically with a set of records is referred to as a cursor. You can think of a cursor as a device that you use to scroll through a set of records in a database to read, add, delete, or update records. There are four types of cursors for Recordset objects in ADO: Dynamic, Keyset, Static, and Forward-only. The combined CursorType, LockType, and Options arguments of the ADO Open method determine the type of ADO Recordset object that is returned.

Working with Records in a Recordset

A Recordset object has a current position, which may be before the first record (BOF), after the last record (EOF), or on a specific record within the Recordset. When retrieving information with the Field object, the information always pertains to the record at the current position.

ADO contains several methods for moving from one record to another. These methods are Move, MoveFirst, MoveLast, MoveNext, and MovePrevious. You may notice that the methods for iterating through an ADO Recordset are identical to the methods available in DAO.

The following example shows how to use the MoveNext method to iterate through all of the records in a Recordset:

Sub ADOMoveNext()

   Dim cnn As New ADODB.Connection
   Dim rst As New ADODB.Recordset
   Dim fld As ADODB.Field

   ' Open the connection
   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
      & "Data Source=c:\program files\" _
      & "Microsoft office\office\samples\NorthWind.mdb;"
   ' Open a forward-only,read-only recordset
   rst.Open _
      "SELECT * FROM Customers WHERE Region = 'WA'", _
      cnn, adOpenForwardOnly, adLockReadOnly

   ' Print the values for the fields in
   ' the first record in the Immediate window
   Do Until rst.EOF
      For Each fld In rst.Fields
         Debug.Print fld.Value & ";";
      Next
      Debug.Print
      rst.MoveNext
   Loop

   rst.Close
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing

End Sub

The ADO code you use to update data in a Recordset object is also very similar to what you are used to in DAO. The major difference is that DAO required you to put the Recordset object into an editable state by using the Edit method, whereas ADO does not require you to explicitly indicate that you want to be in edit mode. In both DAO and ADO, you can verify the edit status of the current record by checking the EditMode property.

One important difference between DAO and ADO is the behavior when you update a record and then move to another record without calling the Update method. In DAO, any changes made to the current record are lost when you move to another record without first calling the Update method. In ADO, the changes to the current record are automatically saved when you move to a new record. As with DAO, you can explicitly discard changes to the current record in ADO by using the CancelUpdate method.

The following code sample illustrates how to retrieve a specific record from a database and then update data in that record:

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strPath As String
Dim strSQL As String
Dim strFieldName As String
Dim strNewValue As String

strPath = "c:\Program Files\" _
       & "Microsoft Office\Office\Samples\Northwind.mdb"

strSQL = "SELECT * FROM Customers WHERE CustomerId = 'AROUT'"

strFieldName = "ContactName"

strNewValue = "Jane Doe"


Set cnn = New ADODB.Connection
' Open the connection.
With cnn
   .Provider = "Microsoft.Jet.OLEDB.4.0"
   .Open strPath
End With

Set rst = New ADODB.Recordset
With rst
   ' Open the Recordset object.
   .Open Source:= strSQL, _
      ActiveConnection:= cnn, _
      CursorType:= adOpenKeyset, _
      LockType:= adLockOptimistic
    ' Update the specified field for the current record.
   .Fields(strFieldName).Value = strNewValue
    ' Save the changes you made to the current
    ' record in the Recordset object.
   .Update

   ' Close the Recordset object.
   .Close
End With

' Close connection and destroy object variables.
cnn.Close
Set rst = Nothing
Set cnn = Nothing

Notice that the WHERE clause in the SQL statement uses single-quote marks (') around the value specified in the criterion (CustomerId = 'AROUT'). When you used DAO, a WHERE clause could have double-quote marks (") around a value, but in ADO you must use single-quote marks when specifying criteria. In addition, if your criteria contain field names that have the same name as SQL keywords, you must surround the field name with square brackets ([ ]).

Where to Get More Info

Next month, I will continue the discussion of using ADO in Office solutions. If you can't wait that long, here are some great resources you can use to explore ADO in Office in more depth:

David Shank is a Programmer/Writer on the Office team specializing in developer documentation. Rumor has it he lives high in the mountains to the east of Redmond and is one of the few native northwesterners still living in the northwest.