Search the Catalog
MCSD in a Nutshell: The Visual Basic Exams

MCSD in a Nutshell: The Visual Basic Exams

By James Foxall, MCSD
October 2000
1-56592-752-4, Order Number: 7524
632 pages, $29.95

Chapter 7
Working with Data

One of the biggest changes in Visual Basic 6 is the way in which you access data (or the way in which Microsoft wants you to access data). Microsoft is making a conscious effort to create a paradigm in which data is accessed through a common interface, regardless of the data's underlying format. In the past, developers often used a number of data access techniques, including the two common data access object models, data access objects (DAOs) and remote data objects (RDOs), as well as directly using APIs such as the ODBC API. Usually, code used to access data with one method is incompatible with code that accesses data using another method, which creates a number of drawbacks. For instance, scaling a database (such as migrating from a Jet database to a SQL database) often means rewriting the majority of data access code. In theory, if the mechanism used to communicate with different data sources remained consistent, using different back-end data sources would require little or no code modifications.

The Technologies and the Exams

Although ActiveX Data Objects (ADO) is covered on both exams, the Desktop exam only covers ADO at a superficial level, focusing mostly on the ADO Data control. The Distributed exam, however, takes off the kid gloves and delves into more hard-core subject matter. If you are taking only the Desktop exam, use Table 7-1 as a guideline to which material to focus on.

Table 7-1: Coverage of Data Access Topics on the Exams

Topic

Covered on Exam

Universal data access

Both

OLE DB

Both

ActiveX data objects (ADO)

Both

Comparison of ADO, RDO, and DAO

Both

ADO connection object

Both

ADO command object

Both

ADO recordsets

Both

Cursor locations and types

Distributed

Recordset locking

Distributed

The Execute method

Distributed

Recordset.Open

Both

Navigating records

Both

Managing transactions

Distributed

The ADO error object

Distributed

The ADO Data control

Both

The Data Environment Designer

Both

Universal Data Access

OLE DB is a low-level interface that introduces Microsoft's strategy of universal data access, or UDA. OLE DB is designed to be a high-performance mechanism for accessing data. Unlike previous technologies, OLE DB is not restricted to accessing Jet or ISAM databases, nor is it even restricted to accessing relational databases. Instead, OLE DB is capable of accessing data that resides in text files, mail servers, spreadsheets, and nonrelational data, in addition to the standard relational database formats. OLE DB makes this possible by way of OLE DB providers. An OLE DB provider is a component that exposes data through an OLE DB interface. The mechanics of how the data is retrieved, as well as the underlying format of the data, are of no consequence to OLE DB or to components accessing the data through OLE DB, as long as the provider makes the data available by way of the expected OLE DB interface.

Microsoft's UDA strategy creates an extended, layered approach to data access. At the lowest level, the OLE DB provider is designed to access some form of data and expose that data through a standard OLE DB interface. Using this interface, OLE DB itself is able to make requests and execute commands on the data. Just as OLE DB doesn't communicate directly with data (it communicates through one or more providers), client applications don't communicate directly with OLE DB. Instead, client applications use ADO to manipulate data. ADO provides a common application interface that simplifies the process of communicating with OLE DB. Figure 7-1 shows the layers of this architecture and how they communicate.

Figure 7-1. OLE DB is part of Microsoft's Universal Data Access strategy, which allows the same code to be used to access different data sources

 

TIP:  

Visual Basic is capable of creating OLE DB providers. Creating OLE DB providers is beyond the scope of the exams and therefore of this book as well.

Manipulating Data Using
ActiveX Data Objects

ADO is a set of objects that act as a client interface to OLE DB. Table 7-2 lists the two primary data access methods that ADO is designed to replace: DAO and RDO. In theory, ADO takes the best of both of these data access methods and makes them better. There are differing opinions on how successful Microsoft has been in this endeavor, but one thing is for sure: ADO will replace DAO and RDO--it's only a matter of time.

Table 7-2: Data Access Object Models

Method

Purpose

Comments

DAO

Used to access Jet databases natively and ODCB and ISAM databases via Jet. DAO is best suited for single-user applications or small, local deployments.

DAO is the oldest of the three data object models. DAO is extremely well-established, and it is the fastest way to access Jet databases.

RDO

Used to access databases via ODBC. RDO can't access Jet or ISAM databases very well, and it can only access relational databases via ODBC. RDO is used to access SQL Server, Oracle, and other large relational databases.

RDO is a set of objects that act as a wrapper around the ODCB API, alleviating the need for developers to make ODBC API calls directly.

ADO

Used to access many types of data through OLE DB. ADO is the successor to DAO and RDO.

ADO is designed to minimize network traffic and impose minimal layers between the front-end application and the data source.

Microsoft wants everyone one to believe, unequivocally, that ADO is superior to all previous data access methods. In many ways, this is true. The following lists some of the reasons you might choose ADO over DAO or RDO:

In order to use ADO in a Visual Basic project, the Microsoft ActiveX Data Objects Library must be referenced, using the References dialog box accessed by choosing References from the Tools menu. Once ADO is referenced, its object model is made available to the project.

One of the benefits of ADO over DAO and RDO is that it supports a simplified (flattened) object model. This means that ADO uses fewer objects, yet more properties, methods, and events than other data access methods. If you're new to programming databases, this may make it easier to learn. However, if you're one of the many experienced developers using other data access methods such as DAO (which has a rather large object model), using ADO necessitates some rethinking. In particular, if you're a DAO developer, it may be more difficult to transition to ADO than it would be for an RDO developer, because ADO is more similar to RDO than to DAO. Figure 7-2 shows the ADO object model, while Table 7-3 explains each component of the ADO hierarchy.

Figure 7-2. ADO has a flattened object model

 

Table 7-3: ADO Object Hierarchy

Object

Description

Connection

Used to establish and maintain a connection to an OLE DB data source. The Connection object allows you to specify information such as a username and password string.

Command

Defines a specific command to execute against the data source, such as a SQL statement. A Connection object isn't required in order to use a Command object; the Command object is capable of establishing a connection.

Parameter

Represents a single parameter for a command. The data placed into a Parameter object depends upon the command being executed. Command objects each have one Parameters collection that may contain zero or more parameter objects.

Recordset

Represents a set of records from a table, Command object, or SQL query result. Recordset objects can be used to select, navigate, add, edit, or delete data. Recordsets can be created without your explicitly creating an underlying Connection object.

Field

Holds information about a single column of data in a recordset. Information (i.e., properties) includes items such as name, size, type, and value. Recordset objects have a Fields collection.

Error

Contains details about data access errors such as error number, error description, and the object that created the error. The Error object is refreshed each time an error occurs.

Each of the ADO objects is described in the following sections.

The ADO Connection Object

In order to access data using ADO, a connection to the data source must be established. Connections can be established explicitly by creating a Connection object, or implicitly by supplying a connection to a Command object. Although an explicit connection isn't required in order to access a data source, you should create an explicit connection if you plan on referencing multiple recordsets or executing multiple commands against a single data source. Creating an explicit connection using a Connection object in cases such as this results in faster data access performance and lower overhead.

Establishing a connection using the Connection object

The first step to establishing a connection to a data source using a Connection object is to create an object variable and use it to instantiate a new connection object. The following code shows how you might do this:

Dim cnADOConnection As Connection
Set cnADOConnection = New Connection

Connections are established using the Open method of the Connection object. The Open method has the following syntax:

ConnectionObject.Open ConnectionString As String, UserID As String, _
                      Password As String, Options As Long

The parameters for the Open method of the Connection object are explained in Table 7-4. The arguments for the ADO ConnectionString are explained in Table 7-5.

Table 7-4: Parameters of the Connection Object's Open Method

Argument

Description

ConnectionString

An optional string used to specify connection information, including the name of the provider, the user and password, and the data file. Table 7-5 lists the most common arguments for ConnectionString.

UserID

An optional string containing the name of the user when connecting to the data source.

Password

An optional string containing the password for the user specified in the UserID argument.

Options

A value that determines whether the Connection object should return before the connection is established (asynchronously), or after the connection is established (synchronously). Use the enumerated value adConnectUnspecified or adAsyncConnect.

Table 7-5: ADO ConnectionString Arguments

Argument

Description

Provider=

The name of the OLE DB provider to use.

Data Source=

The name of the data source to connect to.

UID=

A valid user to use when connecting to the data source.

PWD=

A password to use when connecting to the data source.

DRIVER=

The name of the database driver to use. This isn't required if a Data Source Name (DSN) is specified.

SERVER=

The network name of the data source server.

The following example shows how you might establish a connection with the Access database named Biblio.mdb that ships with Visual Basic:

Dim cnADOConnection  As Connection
Set cnADOConnection = New Connection
 
cnADOConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=c:\temp\biblio.mdb"

If security was enabled in Biblio.mdb, you could use code such as this:

Dim cnADOConnection  As Connection
Set cnADOConnection = New Connection
 
cnADOConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=c:\temp\biblio.mdb", "James", _
                     "MyPassword"

or this:

Dim cnADOConnection  As Connection
Set cnADOConnection = New Connection
 
cnADOConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=c:\temp\biblio.mdb;UID=James;" & _
                     "PWD=MyPassword"

Disconnecting from a data source

Closing a connection is simpler than establishing one: use the Close method of the Connection object. For instance, to close the connection used in the previous example, you might use a statement such as cnADOConnection.Close. When the Close method is invoked, all pending transactions are rolled back, and any server-side resources used by the connection are released. Be sure to invoke the Close method explicitly on all connections when you are finished with them.

TIP: 

Retrieving and manipulating data using the Connection object is discussed in the section "Manipulating Data."

The ADO Command Object

The Command object represents a command to execute against a database. Command objects are often used to create recordsets from a data source, execute action queries, or manipulate the structure of a data source. For example, a Command is often a SQL statement or a stored procedure. Table 7-6 lists the commonly used properties, methods, and collections of the Command object.

Table 7-6: Commonly Used Properties and Methods of the Command
Object

Member

Description

ActiveConnection property

Specifies an open connection for the command to use.

CommandText property

Defines the executable text of the command (such as a SQL statement).

Execute method

Executes the command in CommandText and to return a Recordset object.

Prepared property

Specifies whether or not the provider saves a prepared (or compiled) version of the command prior to execution.

CommandType Property

Defines the type of command to execute; that is, it defines the type of command represented by the CommandText property.

CommandTime-out property

Sets the number of seconds a provider will wait for a command to execute.

Parameters
collection

Used to define parameterized queries or stored-procedure arguments.

The way a Command object uses a connection is defined by its ActiveConnection property. A Command object can use an existing connection, or it can establish a new connection. To use an existing connection, pass an open connection object to the ActiveConnection property, like this:

Dim cnADOConnection  As Connection
Dim comADOCommand    As Command
      
Set cnADOConnection = New Connection
Set comADOCommand = New Command
   
cnADOConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=c:\temp\biblio.mdb"
 
Set comADOCommand.ActiveConnection = cnADOConnection
 
cnADOConnection.Close

To have the Command object create a new connection, pass a connection string to the ActiveConnection property, like this:

Dim comADOCommand    As Command
      
Set comADOCommand = New Command
   
comADOCommand.ActiveConnection = _
   "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=c:\temp\biblio.mdb"

Setting the ActiveConnection property to Nothing disassociates the Command object from its connection. If you close the Connection object with which a Command object is associated, the Command object's ActiveConnection property is automatically set to Nothing.

TIP:  

Retrieving and manipulating data using the Command object is discussed in the following section.

Manipulating Data

Using ADO, you can manipulate data sources in many ways. You can create Recordset objects that contain a result set of a query or a stored procedure, and you can add, edit, and delete records in the data source. Both the Connection and the Command objects can be used to manipulate data. Rather than discuss data manipulation separately for each of these objects earlier in this chapter, the information is consolidated in the following sections.

ADO Recordsets

As with other data access methods such as DAO and RDO, records are manipulated by Recordset objects. However, unlike with previous data access methods, an active connection to a data source is not required to use a Recordset object. If an active connection doesn't exist when creating a recordset, one is created behind the scenes. Using a Recordset object, you can retrieve, edit, update, and delete records. There are three ways to open a Recordset object using ADO:

  1. Use the Connection.Execute method.
  2. Use the Command.Execute method.
  3. Open the Recordset object without a Connection object or Command object by passing a valid Connect string to the second argument of the Open method of the Recordset object.

Recordset cursors

When you create a recordset, you're given the opportunity to specify a cursor type. The type of cursor specified determines what you can and cannot do with records returned in a recordset. Table 7-7 lists the ADO cursor types, along with a description of each.

TIP:  

If you don't explicitly set the cursor type, a read-only, forward-only cursor type recordset is created. Since you can't specify a cursor type for recordsets created with the Execute method of the Connection or Command objects, such recordsets are always read-only, forward-only recordsets.

Table 7-7: ADO Cursor Types

Cursor Type

Description

Static

The recordset is a static copy of the data in the data source, similar to the Snapshot recordset type of DAO. Once a static cursor recordset is created, no changes made by other users propagate to the recordset; the recordset never changes.

Forward-only cursor

(Default) Forward-only cursors are identical to static cursors, since they create static snapshots of data. Records that use a forward-only cursor can only be scrolled from beginning to end. For instance, you can use MoveNext to move forward in the recordset, but you cannot use MovePrevious to move backward. Forward-only cursors offer optimal performance in exchange for feature limitations.

Dynamic cursor

The recordset is "live," meaning that any and all additions, changes, and deletions made by other users affect the recordset. Dynamic-cursor recordsets support all types of navigation, including bookmarks (if bookmarks are supported by the provider). Dynamic cursors offer the most features of any cursor type, but at the expense of increased overhead.

Keyset cursor

Keyset cursors are like dynamic cursors, except additions made by other users are not visible in the recordset. The recordset is affected by changes and deletions, however.

Cursor location

The cursor location determines where ADO stores the cursor (the temporary rows of a recordset). You have the option of creating server-side or client-side cursors, and there are benefits and drawbacks to each. Client-side cursors, for instance, often support more features than server-side cursors, but they can take a long time to build because the data must be pulled to the client to build the cursor. However, once a client-side cursor is built, traversing the cursor's data is often very fast. One drawback to client-side cursors is that they only support static cursors; use a server-side cursor when creating nonstatic cursors.

To specify a cursor location, you set the CursorLocation property of the Connection object. Table 7-8 lists the possible values and their meanings.

Table 7-8: Cursor Location Constants and Descriptions

Value

Description

adUseServer

(Default) Cursors are created on the server by the data provider (driver-supplied). Server-side cursors allow for additional sensitivity to changes made by others, but they often support fewer features than client-side cursors.

adUseClient

Cursors are created on the client machine. Client-side cursor engines often support more features than server-side (driver-supplied) cursors.

TIP:  

Client-side cursors must be used for batch updating; server-side cursors don't support batch updating.

Recordset locking

The type of record locking ADO uses on a recordset is determined by the LockType property of the recordset when it's created. Different applications have different locking needs, and the locking method you employ on a given recordset will need to be determined by the circumstances of the situation. Table 7-9 lists the ADO locking methods, along with the associated LockType constant.

Table 7-9: The ADO Recordset Locking Options

LockType

Locking Method

Description

adLockReadOnly

Read-only

(Default) Records can be read, but data cannot be added, changed, or deleted. This is the locking method used with static cursors and forward-only cursors.

adLockPessimistic

Pessimistic

The provider does what is necessary to ensure successful editing of records, usually by locking records at the data source immediately upon editing.

adLockOptimistic

Optimistic

The provider locks records only when you call the Update method, not when you start editing.

adLockBatchOptimistic

Optimistic Batch

Records are locked in batch update mode, as opposed to immediate update mode. This option is required for client-side cursors.

TIP:  

When you create a recordset using the Execute method of the Connection or Command object, you cannot specify a locking method; all recordsets created in this manner are forward-only, read-only recordsets. If you need to specify a different type of locking method or cursor type, use the Open method of the Recordset object to create the recordset.

Creating a recordset using the Connection.Execute method

Recordset objects can be created directly from Connection objects by using the Execute method of the Connection object. The Execute method can be used to return a Recordset object, or it can be used to execute an action query (discussed later in this chapter).

The Execute method of the Connection object has the following syntax:

ConnectionObject.Execute(CommandText As String, _
                         RecordsAffected As Long, _
                         Options As Long) As Recordset

The CommandText parameter is the text containing a data definition or data manipulation command, such as a SQL SELECT or DELETE statement, or a table name.

RecordsAffected is an optional parameter to which the provider returns the number of records affected by the Execute method.

The Options parameter determines how the provider should handle the CommandText parameter, and can be one of the constants listed in Table 7-10. (Note that it is possible to OR either of the last two constants with one of the other constants.)

Table 7-10: Possible Values for the Options Parameter of the Connection.Execute Method

Constant

Description

adCmdText

Indicates that the provider should evaluate CommandText as a textual definition of a command.

adCmdTable

Indicates that the provider should evaluate CommandText as a table name.

adCmdStoredProc

Indicates that the provider should evaluate CommandText as a stored procedure.

adCmdUnknown

Indicates that the type of command in the CommandText argument is not known and that the provider should attempt to interpret it. Typically, this results in poor performance.

adExecuteAsync

Indicates that the command should execute asynchronously

adFetchAsync

Indicates that the remaining rows after the initial quantity specified in the CacheSize property should be fetched asynchronously.

For example, to create a recordset containing all of the authors in the Biblio.mdb Jet database, you could use code such as this:

Dim cnADOConnection  As Connection
Dim rstAuthors       As ADODB.Recordset
 
Set cnADOConnection = New Connection
 
cnADOConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=c:\temp\biblio.mdb"
 
Set rstAuthors = cnADOConnection.Execute("Authors", adcmdTable)
   
cnADOConnection.Close

TIP:  

The recordset returned by the Execute method of the Connection object always uses a read-only, forward-only cursor. If you need to create a recordset that uses a different cursor type, use the Open method of the Recordset object, as discussed shortly.

Creating a recordset using the Command.Execute method

Creating recordsets using the Execute method of the Command object is similar to creating recordsets using Connection.Execute. However, instead of specifying CommandText as an argument of the Execute method, you set the CommandText property of the Command object. In addition, the Execute method of the Command object has an optional variant parameter named Parameters, that lets you pass parameter values to a SQL statement.

TIP: 

Attempting to invoke the Execute method on a Command object before setting the ActiveConnection property to an open Connection object or a valid connection string results in an error.

The Execute method of the Command object has the following syntax:

CommandObject.Execute(RecordsAffected As Long, _
                         Parameters(  ) As Variant, Options As Long) _
                         As Recordset

RecordsAffected is an optional parameter to which the provider returns the number of records affected by the Execute method.

The Parameters parameter is a variant array used to specify initial parameter values.

The Options parameter determines how the provider should handle the Command-Text property and can be one of the values listed earlier in Table 7-10.

For example, to create a recordset containing all of the authors in the Biblio.mdb Jet database using a Command object, you could use code such as this:

Dim comADOCommand    As Command
Dim rstAuthors       As ADODB.Recordset
 
Set comADOCommand = New Command 
 
comADOCommand.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                 "Data Source=c:\temp\biblio.mdb"
 
comADOCommand.CommandText = "Authors"
Set rstAuthors = comADOCommand.Execute(, , adCmdTable)   

Creating a recordset using the Recordset.Open method

In addition to using the Execute methods of the Connection and Command objects, recordsets can be established directly using the Recordset object; neither a Command object nor a Connection object is required to create a recordset. As a matter of fact, you must use a Recordset object to create anything other than a static, forward-only recordset. Records are returned to a Recordset object by invoking the Open method of the Recordset object. The Open method has the following syntax:

RecordsetObject.Open(Source, ActiveConnection, CursorType, LockType, _
                     Options)

The Source parameter is an optional variant that evaluates to a valid Command object, a SQL statement, a table name, a stored procedure call, or the filename of a persisted recordset.

The ActiveConnection parameter is an optional variant that evaluates to a valid Connection object variable name, or a string containing connection string parameters.

TIP:  

For more information on the ActiveConnection argument, refer to the previous discussion of the ActiveConnection property of the Command object.

The CursorType parameter is an optional value that determines the type of cursor that the provider should use when opening the recordset. The possible values for CursorType are listed in Table 7-11.

Table 7-11: Possible Values for CursorType

Value

Description

adOpenForwardOnly

(Default) Used to open a forward-only cursor

adOpenKeyset

Used to open a keyset cursor

adOpenDynamic

Used to open a dynamic cursor

adOpenStatic

Used to open a static cursor

The LockType parameter is optional, and it's used to specify the type of locking the provider should use when opening the recordset. LockType can be any of the values listed in Table 7-9.

The Options parameter is an optional Long that indicates how the Source should be evaluated. The possible values for Options are the same as the values for the Options parameter of the Execute method of both Connection and Command objects. Refer to Table 7-10 for the possible values.

Recordsets can be created using an explicit or an implicit connection. When an existing connection is used to create a recordset (an open Connection object is specified as the ActiveConnection argument), the recordset is created using an explicit connection. When a connection string is specified as the Active-Connection argument, the recordset is created using an implicit connection. When an implicit connection is used, a Connection object is created behind the scenes; you cannot access the Connection object, and it's automatically closed when the recordset is closed.

The following code opens a recordset by calling the Open method of the Recordset object and specifying an explicit connection:

Dim cnADOConnection  As Connection
Dim rstAuthors       As adodb.Recordset
   
Set cnADOConnection = New Connection
Set rstAuthors = New adodb.Recordset
   
cnADOConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=c:\temp\biblio.mdb"
 
rstAuthors.Open "Authors", cnADOConnection, , , adCmdTable
   
rstAuthors.Close
cnADOConnection.Close

The following code opens a recordset by calling the Open method of the Recordset object and specifying an implicit connection:

Dim rstAuthors       As adodb.Recordset
   
Set rstAuthors = New adodb.Recordset
   
rstAuthors.Open "Authors", "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=c:\temp\biblio.mdb", , , adCmdTable
   
rstAuthors.Close

TIP:  

Creating recordsets that use implicit connections requires less coding than creating recordsets that use explicit connections. However, whenever possible, you should use explicit connections. When you share a connection by using it as an explicit connection for multiple Recordset objects, fewer connections are made on the data source and fewer resources are consumed.

Navigating records using the Recordset object

If you've used DAO or RDO, you'll find that ADO recordsets support a number of the properties and methods with which you're already familiar. In particular, all of the common navigation methods are supported (depending, of course, on the type of cursor used when the recordset was opened). Table 7-12 lists the supported navigation methods.

Table 7-12: Navigation Methods of the ADO Recordset Object

Method

Description

Move

Moves to the next record in the recordset

MoveFirst

Moves to the first record in the recordset

MoveLast

Moves to the last record in the recordset

MoveNext

Moves to the next record in the recordset

MovePrevious

Moves to the previous record in the recordset

When navigating recordsets, it's important to watch for the beginning of the file (BOF) and the end of the file (EOF) in order to prevent a runtime error. The BOF occurs when the record pointer of a recordset is positioned immediately before the first record in the recordset, while the EOF occurs when the record pointer is positioned immediately after the last record in the recordset. You can determine if a recordset's pointer is at beginning-of-file or end-of-file by evaluating the Recordset object's BOF and EOF properties, respectively. If RecordsetObject.BOF is True, the recordset pointer is at beginning-of-file. If RecordsetObject.EOF is True, the recordset pointer is at end-of-file. If a recordset is empty, both BOF and EOF will return True.

The following code illustrates how you can use EOF to check for an end-of-file condition, removing the chance that the code will attempt to retrieve data when there is no current record:

Dim rstAuthors As adodb.Recordset
   
Set rstAuthors = New adodb.Recordset
   
rstAuthors.Open "Authors", "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=c:\temp\biblio.mdb", , , adCmdTable
   
Do While Not (rstAuthors.EOF)
   Debug.Print "Author's Name is " & rstAuthors![Author]
   rstAuthors.MoveNext
Loop
   
rstAuthors.Close

TIP: 

When using DAO, only forward-only recordsets automatically position the pointer at the first record; other types of recordsets position the pointer at BOF. In ADO, the pointer is always at the first record, regardless of the cursor type, if at least one record exists in the recordset.

Accessing data in a recordset

Once a recordset is created and the recordset pointer is pointing to a record, you can access the data in the current record in one of two ways:

If you don't know the name of a field, but you do know its ordinal number, use the Fields collection to access the field. For instance, the following code prints the names of all fields in the Recordset object along with the field value of the current record to the Immediate window (assuming that the recordset is a valid Recordset object):

Dim lngIndex      As Long
   
For lngIndex = 0 To rstAuthors.Fields.Count - 1
   Debug.Print rstAuthors.Fields(lngIndex).Name & ": " & _
   rstAuthors.Fields(lngIndex).Value
Next lngIndex
 
RstAuthors.Close

The Fields collection makes it easy to access the properties of any field, but it's not the only way to access the value of a field. Instead, if you know the field name, reference the Recordset object, followed by the bang symbol ( ! ), then reference the field name as shown here:

Debug.Print "Author's Name is " & rstAuthors![Author]
Debug.Print "Author was born in " & rstAuthors![Year Born]

TIP: 

You are not required to surround the field name in brackets unless the field name contains a space. To maintain good programming practices, however, you should always surround a field name with brackets, regardless of whether or not the field name contains spaces. This makes it much easier to search and replace field names in the future, should you need to do so.

Finding records using a Recordset object

Although it's usually best to create a well-defined SQL statement to locate a specific record, there are times when it's necessary to search for a record within a recordset. In DAO, you could use the FindFirst, FindLast, FindNext, and Find-Previous methods to search for particular records in a recordset. In ADO, you have just one method that replaces these four: the Find method.

The ADO Recordset.Find method has the following syntax:

RecordsetObject.Find Criteria As String, [SkipRecords as Long], _
                     [SearchDirection As SearchDirectionEnum = _
                     adSearchForward], [Start As Variant])

The Criteria argument is used to identify the records to look for. In DAO, Criteria could be any valid SQL WHERE clause, but ADO's Find method is more restrictive (it does accept the =, >, >=, <, <=, and LIKE operators, however). Actually, ADO's Find method has a number of limitations, including:

Of course, these are huge limitations in comparison with DAO. There are workarounds for many of the limitations, but they are beyond the scope of the exam and of this book.

The SkipRecords parameter is used to emulate the DAO methods FindNext and FindPrevious. The SearchDirection argument determines in which direction the search is performed, and can be either adSearchForward or adSearchBackward . The Start argument is used for specifying where in the recordset to start, and may be either adBookmarkLast or adBookmarkCurrent. Table 7-13 shows how these arguments affect Find by comparing them to the DAO methods that Find is designed to replace.

Table 7-13: How the ADO Find Method's Arguments Affect a Search

DAO Find Method

SkipRecords

SearchDirection

Start

FindFirst

0

adSearchForward

adBookmarkLast

FindLast

0

adSearchBackward

adBookmarkLast

FindNext

1

adSearchForward

adBookmarkCurrent

FindPrevious

1

adSearchBackward

adBookmarkCurrent

The following example shows how you could use ADO's Find method to find the first author's name that begins with "f," then locate the next author's name that begins with "f:"

Dim cnADOConnection  As Connection
Dim rstAuthors       As ADODB.Recordset
   
Set cnADOConnection = New Connection
Set rstAuthors = New ADODB.Recordset
   
cnADOConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=c:\temp\biblio.mdb"
   
rstAuthors.Open "SELECT * FROM [Authors] ORDER BY [Author]", _
                cnADOConnection, adOpenDynamic, adLockOptimistic, adCmdText
      
'* Locate the first author whose name starts with f.
rstAuthors.Find "[Author] LIKE 'f*'", 0, adSearchForward
   
'* Locate the next author whose name starts with f.
rstAuthors.Find "[Author] LIKE 'f*'", 1, adSearchForward, adBookmarkCurrent
   
rstAuthors.Close

TIP: 

ADO does not support the NoMatch property that many DAO developers rely on when using the DAO Find... methods. If the ADO Find method is unsuccessful, the record pointer is positioned at the end of the recordset.

Adding records using a Recordset object

To add records to a Recordset object, the cursor type of the recordset discussed earlier in this chapter must support adding data, and the recordset pointer must first be positioned to a record. To add records to a recordset, call the AddNew method of the recordset. The recordset pointer is then positioned on a new record, and you are free to set the values of the fields as you see fit. To save the new record, invoke the Update method of the recordset. The following code illustrates adding a record to the Authors table in Biblio.mdb :

Dim rstAuthors As adodb.Recordset
   
Set rstAuthors = New adodb.Recordset
   
rstAuthors.Open "Authors", "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=c:\temp\biblio.mdb", adOpenDynamic, _
                adLockOptimistic, adCmdTable
   
rstAuthors.AddNew
   rstAuthors![Author] = "Foxall"
   rstAuthors![Year Born] = 1969
rstAuthors.Update
 
rstAuthors.Close

If, after invoking the AddNew method, you decide not to save the new record, call the CancelUpdate method in place of the Update method, like this:

...
rstAuthors.AddNew
   rstAuthors![Author] = "Foxall"
   rstAuthors![Year Born] = 1969
rstAuthors.CancelUpdate
...

Editing records using a Recordset object

One of the changes from DAO to ADO that may take some getting used to is editing data in a recordset. In DAO, you explicitly placed the recordset in edit mode by calling the Edit method of the recordset. In ADO, you're not required to put the recordset in edit mode. As long as the cursor type of the recordset supports modifying data, you can freely make changes to the values in a record. As with the AddNew method, you commit changes by calling the Update method, and you cancel them by calling the CancelUpdate method. The following illustrates editing a record using the Edit method:

Dim rstAuthors As adodb.Recordset
   
Set rstAuthors = New adodb.Recordset
   
rstAuthors.Open "Authors", "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=c:\temp\biblio.mdb", adOpenDynamic, _
                adLockOptimistic, adCmdTable
   
rstAuthors.MoveFirst
      
'* Start editing.
rstAuthors![Author] = "Foxall"
rstAuthors.Update
 
rstAuthors.Close

Deleting records using a Recordset object

To delete a record from a recordset, first position the record pointer so that the record you want to delete is the current record, then call the Delete method of the Recordset object. For instance, to delete the first record in a recordset, you might use code such as this:

Dim rstAuthors As adodb.Recordset
   
Set rstAuthors = New adodb.Recordset
   
rstAuthors.Open "Authors", "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=c:\temp\biblio.mdb", adOpenDynamic, _
                adLockOptimistic, adCmdTable
   
rstAuthors.MoveFirst
      
rstAuthors.Delete
 
rstAuthors.Close

Processing SQL Statements Using the Execute Method

It's possible to execute SQL statements against a data source by using the Execute method of the Connection object or the Command object. If the SQL statement will be executed only once, use the Execute method of the Connection object. If you intend on executing the same SQL statement more than once during the user's session, or if you are calling a stored procedure, use the Execute method of a Command object. When used more than once, a Command object can create an optimized temporary stored procedure, making successive Execute method calls faster.

The Execute method has the following syntax:

ConnectionObject.Execute CommandText As String, RecordsAffected, Options

The Execute method in ADO is similar to the Execute method of the Database object in DAO. For instance, to change the value of a Status field of a record using a SQL statement, you could use code such as the following (this code assumes a valid Connection object):

strSQL = "UPDATE tblQuotes SET [WorkOrderStatus] = 'Open' WHERE " & _
         "[QuoteNumber] = " & m_lngQuoteNumber
 
cnADOConnection.Execute strSQL

In addition to performing action queries, the Execute method can perform data-definition commands (such as Create Table) or data-manipulation commands (such as SELECT statements). The Execute method can also return a recordset. For example, the following code uses Execute to create a recordset:

Dim cnADOConnection  As Connection
Dim rstAuthors       As adodb.Recordset
 
cnADOConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=c:\temp\biblio.mdb"
 
Set rstAuthors = cnADOConnection.Execute("SELECT [Author] FROM tblAuthors") 
 
cnADOConnection.Close

Managing Transactions

A transaction is defined by MSDN as "a unit of work that is done as an atomic operation--that is, the operation succeeds or fails as a whole." In other words, a transaction is a unit of change in a data source. Consider banking, for example. When money is moved from one account to another, one account is debited and the other credited. These two actions together define a single transaction; if one part fails, both parts must fail. If only one part of the transaction were to succeed, balances would be incorrect and money would appear to either disappear or grow.

In order for a transaction to be considered valid, it must pass the ACID test. The following list explains each of the ACID properties:

Atomicity
A transaction must be an atomic unit of work; the transaction either commits or aborts.

Consistency
The changes that result from completing the transaction must leave the data in a consistent state, without violating any business rules of the data source.

Isolation
Modifications to data made by one user's transactions must not directly affect another user's transactions. Another user's transaction accesses data in the state it was in either before the transaction was started or after it was completed; another user's transaction must never use transitional data.

Durability
Transactions must fully commit, and once a transaction is committed, its effects must persist in the data source even if there are system failures.

ADO allows you to begin and commit individual transactions explicitly (if transactions are supported by the provider). If you don't explicitly define a transaction, ADO runs in auto-commit mode, meaning each change to the database is treated as a single transaction. For instance, updating records using a SQL statement is considered a single transaction, as is deleting a record using the Delete method of the Recordset object.

To define and commit a transaction explicitly, you use the BeginTrans and CommitTrans methods of the Connection object. The behavior of these methods is similar to that of the Workspace object's methods of the same name in DAO. The following code initiates a transaction using the BeginTrans method, executes two distinct SQL statements, and finally commits the changes as a single transaction:

Private Sub TransferFunds(curAmount as Currency)
   Dim cnADOConnection  As Connection
   Dim strSQL           As String
 
   Set cnADOConnection = New Connection
 
   cnADOConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=c:\temp\biblio.mdb"
   
   cnADOConnection.BeginTrans
      strSQL = "UPDATE tblSavings SET [Funds] = [Funds] - " & curAmount
      cnADOConnection.Execute strSQL
 
      strSQL = "UPDATE tblChecking SET [Funds] = [Funds] + " & curAmount
      cnADOConnection.Execute strSQL
 
   cnADOConnection.CommitTrans
   
   cnADOConnection.Close
 
End Sub

If part of a transaction fails, the entire transaction is rolled back and no changes are made to the data source. To cancel a transaction explicitly, use the RollBackTrans method of the Connection object. When you create explicit transactions, commit them or roll them back as soon as possible, because ADO imposes locks on the data source the entire time the transaction is processing.

TIP: 

It's possible to nest transactions. When creating nested transactions, no database changes are committed until the final, top-level transaction commits; if the top-level transaction fails, none of the nested transactions modify the database.

The ADO Error Object

Any operation performed on an ADO data source may generate one or more errors from the data provider. To help manage these errors, the Connection object contains an Errors collection. Each time an ADO operation causes a provider error, the Errors collection is cleared, and the new error or errors are added to the Errors collection. The Errors collection supports the typical Clear, Item, and Count members found with most collections. For instance, to determine the number of errors in the Errors collection, you could use code such as this:

Debug.Print cnADOConnection.Errors.Count

Each object in the Errors collection corresponds to an individual error encountered during a single operation; remember, each time an operation generates an error, it clears the Errors collection before adding its errors as unique error objects. Table 7-14 lists the properties of the ADO Error object.

Table 7-14: Properties of the ADO Error Object

Property

Description

Description

Contains a textual description of the error. This text is usually provider specific, and it may contain the source of the error.

HelpContext

Contains the context ID identifying the help topic to be displayed if requested by the user.

HelpFile

Contains the help file associated with the current error.

NativeError

Contains the provider-specific error code (Long).

Number

Contains the Long integer value of the error constant.

Source

Contains text that identifies the object that raised the error.

SQLState

Contains a five-character string that follows the ANSI-SQL standard error codes.

The ADO Errors collection is completely separate from the VBA Error object. The ADO Errors collection contains only errors returned by providers, never errors encountered in your Visual Basic code. Also, ADO has an Errors collection, whereas Visual Basic keeps track of only one error at a time.

The Errors collection allows you to test and evaluate errors that may occur during an operation on a data source. Exactly how you handle a given error, however, is determined by the situation in which the error occurs.

Using the ADO Data Control

If you've read all of the information on ADO up to this point, the discussion of the ADO Data control will be anticlimactic. Essentially, the ADO Data control is the ADO version of the standard Data control, allowing you to access ADO data sources. In most situations, you're better off writing Visual Basic code to work with recordsets, rather than using the ADO control. This is because ADO Data controls can't share connections, and the ADO Data control is simply a wrapper around ADO that supports only limited functionality of ADO itself. However, there may be times when you wish to use the ADO Data control, and you may encounter questions about it on the exam, so it's mentioned here.

In order to use the ADO Data control, it must first be referenced in a project. To reference the ADO Data control in a project, choose Components from the Project menu and then select Microsoft ADO Data Control 6.0 (OLE DB). Once the control is referenced within the project, you can add it to a form just as you do a standard control. The ADO Data control has a number of properties and methods, and for the most part, they correspond to ADO objects and properties, so there's no need to discuss them here as well. The properties that you will want to set to create a recordset are:

ConnectionString
Set to a valid connection string

UserName
Set to a valid user of the database

Recordsource
Use the property pages to define a command

Once an ADO Data control contains a valid recordset, you can manipulate the data in the recordset using the Recordset property of the control, much as you manipulate Recordset objects in code. For instance, to move to the first record in a recordset of an ADO Data control, you could use a statement such as this:

adodc1.Recordset.MoveFirst

To bind a control to an ADO Data control:

  1. Set the control's DataSource property to the ADO Data control.
  2. Set the control's DataField property to a field in the ADO Data control's recordset.

The Data Environment Designer

Visual Basic 6 includes a new designer that may make creating database applications in Visual Basic easier: the Data Environment designer. The Data Environment designer allows you to create Connection and Command objects interactively at runtime. Although many developers prefer to write code to perform these functions rather than use yet another development component, the Data Environment designer allows you to easily create bound forms, something that you can't do using Visual Basic code alone.

TIP:  

The Data Environment designer effectively replaces the ActiveX UserConnection designer available in previous releases of Visual Basic.

To use the Data Environment, you must add a Data Environment designer to the project, much as you would add a form to a project. To do this, choose Data Environment from the Project menu. The new Data Environment designer then appears in the Project Explorer window (see Figure 7-3).

TIP:  

If you have many ActiveX designers installed on your computer, you may have a More ActiveX Designers submenu on the Project menu, and you may have to choose Data Environment from this submenu.

Figure 7-3. Data Environment designers appear in the Designers folder of the Project Explorer window

 

The Data Environment designer consists primarily of a tree view control and a toolbar (see Figure 7-4). The tree view displays all of the defined objects (Connections and Commands), and it can display them by object type or by connection. The toolbar is used to add and delete items such as connections, commands, and stored procedures, as well as to access properties for each item.

Figure 7-4. The Data Environment's main interface is a tree view of defined objects

 

TIP:  

The general process of using the Data Environment designer is discussed in this section. For more specifics, complete Exercise 7.2 at the end of this chapter.

Generally, the first step you'll perform is to define one or more connections. By default, the first connection is made for you and it's named Connection1, though you can rename it. To edit the connection's properties, choose Properties from its Shortcut menu or select the item and click the properties button on the toolbar; either way, you're shown the Data Link Properties dialog box shown in Figure 7-5.

Figure 7-5. Use the Data Link Properties dialog box to define connections

 

Using the Data Link Properties dialog box, you can choose a provider, select a data source file, and supply a username and password--essentially the same things you can do with a Connection object in Visual Basic code.

Once a connection is defined, you can add commands. To create a command, click the Add Command button on the toolbar. Once the command appears in the list, you can rename it and edit its properties using the Command Properties dialog box shown in Figure 7-6. To aid you in building SQL commands, you can click the SQL Builder button on the Command Properties dialog box to display the SQL Builder window (see Figure 7-7).

Figure 7-6. Creating commands in the Data Environment designer is similar to creating Command objects in code, with some additional functionality

 

Figure 7-7. The SQL Builder helps with the task of designing complex SQL statements

 

Once you've created your connections and commands, you can use them in code or use them to easily build bound forms. To manipulate a connection defined in the Data Environment designer, you use the Connections collection of the Data Environment designer, and to use a command you use its Commands collection. For example, to use a connection named BiblioConnection and manipulate a recordset created with a command named AllAuthors, you could use code such as this:

DataEnvironment1.Connections("BiblioConnection").Open
 
With DataEnvironment1.Recordsets("AllAuthors")
   .Open
   .MoveFirst
   Debug.Print ![Author]
   .Close
End With
 
DataEnvironment1.Connections("BiblioConnection").Close

In addition to using the connections and commands in code, you can easily create a bound form for a given command. To create a bound form, drag the desired command and drop it on a form. The Data Environment designer then creates a label and a control on the form for each data field (see Figure 7-8), and automatically binds the fields to the command--pretty slick.

Figure 7-8. The simple act of dragging and dropping a command creates a bound form

 

When a control is bound to a Data Environment designer connection or command, the DataSource property of the control is set to the name of the Data Environment designer, and the DataField property is set to the name of a field in the recordset generated by a command; this is all similar to binding a control to the ADO Data control. However, controls bound to a Data Environment designer also have a Command object specified in their DataMember property, because Data Environment designers can contain multiple commands.

Key Facts

Applying What You've Learned

It's now time to apply what you've learned about creating ActiveX controls. Start by answering all of the questions in the Skills Assessment. If you can correctly answer all of these questions, you'll be demonstrating a solid understanding of creating ActiveX controls. After you've answered the assessment questions, work through the hands-on exercises.

Skills Assessment

  1. What is the name of Microsoft's strategy to create a universal interface for accessing relational and nonrelational data sources?
  2. OLE DB is Microsoft's latest data access technology. OLE DB is capable of accessing what type of data?
  3. The object that acts as a layer between OLE DB and a data source is called:
  4. What is the name of the application interface layer of OLE DB?
  5. Which data access method is ADO most similar to?
  6. What are considered key features of ADO versus other data access methods? (pick two)
  7. To establish a connection to a data source, you use which object?
  8. When a Connection object is used to process a command or create a recordset (as opposed to using a connection string), the connection is said to be:
  9. Which of the following statements creates a connection using a Connection object?
  10. Which method is used to disconnect an open Connection object?
  11. A Connection object is required in order to use a Command object.
  12. Which of the following statements defines a Command object as using an implicit connection?
  13. Which of the following is a valid way to execute a SQL statement?
  14. Which method provides the most flexibility when creating a recordset?
  15. You want to create a recordset that reflects changes and deletes made by other users, but not additions to the recordset. Which statement would you use?
  16. When optimistic locking is used, record locks are created:
  17. Which cursor type creates read-only recordset that allows full navigation, and is most similar to DAO's snapshot recordset type?
  18. Which cursor type offers the fastest performance, and is also the default cursor type?
  19. You want to create a recordset that ensures updates to underlying records always complete successfully. Which statement would you use?
  20. In order to execute a SQL statement using a Command object:
  21. How do you instruct a Command object to treat its command text?
  22. To find the first record in an Authors table where the author's last name begins with "f," you would use which statement?
  23. Which method is used to add records to a recordset?
  24. When editing a record in a recordset, you must explicitly initiate the edit by calling the Edit method.
  25. An atomic operation (a unit of change in a data source) is called a:
  26. A transaction has been initiated using CommitTrans, but things have occurred that necessitate the need to stop the transaction. Which method would you use?
  27. An error has occurred while attempting to make a connection to a data source. Which of the following could be used to obtain the error number of the first error?
  28. Which user-interface element can be used to create ADO recordsets for bound controls?
  29. You can only define one connection per Data Environment designer.
  30. When binding controls to a Data Environment designer, which property of the bound control must be set that isn't set when binding to an ADO Data control?

Answers to Skills Assessment
1. c

7. d

13. d

19. d

25. d

2. d

8. a

14. c

20. b

26. b

3. a

9. a

15. a

21. a

27. a

4. b

10. b

16. c

22. c

28. b

5. b

11. b

17. c

23. b

29. b

6. a, c

12. b

18. d

24. b

30. c

Hands-on Exercises

Exercise 7-1: Establish a connection with a data source, and use a Command object to generate a recordset

In this exercise, you'll use the following skills:

Section 1. Establishing a connection

In this section, you'll establish a connection to the Biblio.mdb database that ships with Visual Basic. Since you'll have to type in the path, you may want to move the database to the root folder or perhaps a folder just off the root.

  1. Start a new Standard EXE Visual Basic project.
  2. Add a reference to ADO by choosing References from the Project menu and then selecting Microsoft ActiveX Data Objects 2.x Library. (The exact version you select will depend on what is installed on your computer.)
  3. Add a new command button to the form and set its properties as follows:
  4. Property

    Value

    Name

    cmdConnect

    Caption

    Connect

  5. Add the following code to the command button's Click event:
  6.    Dim cnADOConnection  As New Connection
       Dim rstAuthors       As ADODB.Recordset
       Dim comADOCommand    As New Command
    

  7. Add the following statement, which actually establishes a connection using the Jet OLE DB provider (change the path as necessary):
  8.    cnADOConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=c:\temp\biblio.mdb"
    

Section 2. Creating a recordset using a Command object

In this section, you'll use a Command object to define a SQL statement to return data, and then use the Command object's Open method to create a recordset:

  1. Add the following code to the procedure:
  2.    Set comADOCommand.ActiveConnection = cnADOConnection
       comADOCommand.CommandText = "Authors"
       Set rstAuthors = comADOCommand.Execute(, , adCmdTable)
    

Section 3. Print the contents of a field in the recordset

In this section, you're going to add a line of code to display the contents of a field in the recordset:

  1. Add the following statement to the Click procedure:
  2. MsgBox "First Author = " & rstAuthors![Author],vbOKOnly
    

Section 4. Close the recordset and the connection

In this section, you're going to clean-up open objects the proper way, rather than letting them go out of scope:

  1. Add the following statements to the end of the Click procedure:
  2.    rstAuthors.Close
       cnADOConnection.Close
    

    The Click event should now look like this:

    Private Sub cmdConnect_Click(  )
       
       Dim cnADOConnection  As New Connection
       Dim rstAuthors       As ADODB.Recordset
       Dim comADOCommand    As New Command
     
       cnADOConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source=c:\temp\biblio.mdb"
     
     
       Set comADOCommand.ActiveConnection = cnADOConnection
       
       comADOCommand.CommandText = "Authors"
       
       Set rstAuthors = comADOCommand.Execute(, , adCmdTable)
     
       MsgBox "First Author = " & rstAuthors![Author], vbOKOnly
       
       rstAuthors.Close
       cnADOConnection.Close
       
    End Sub
    
  3. Run the project and click the button. The message box shown in Figure 7-9 should display (possibly with a different author name, if you've modified your database). If it does not, you may not have the Jet provider installed on your computer.
  4. Figure 7-9. Creating the connection, command, and recordset in this exercise culminates in obtaining a value from the recordset and displaying it in this message box

     

Exercise 7-2: Using the Data Environment designer to create a bound form

In this exercise, you'll create a Data Environment designer containing a single Connection and Command object, and then create a bound form based on the Command object:

You'll use the following skills in this exercise:

Section 1. Adding the Data Environment designer to a project

In this section, you'll reference the Data Environment designer (if it's not already referenced), and add a new Data Environment designer to a project:

  1. Start a new Standard EXE project in Visual Basic.
  2. Ensure that the Data Environment designer is installed and referenced in the project by choosing Components from the Project menu, clicking the Designers tab, and selecting Data Environment.
  3. Choose Add Data Environment from the Project menu.

Section 2. Defining a connection in the Data Environment designer

In this section, you'll define a connection to the Biblio.mdb Jet database that ships with Visual Basic:

  1. Double-click the Data Environment designer in the Project Explorer window to display the designer.
  2. Right-click Connection1 in the designer and choose Properties from the Shortcut menu.
  3. Select the Microsoft Jet 3.51 OLE DB Provider, if installed on your computer (see Figure 7-10), and click Next.
  4. Figure 7-10. The first step to defining a connection is choosing a provider

     

  5. Click the Browse button to the right of the Database Name field, and locate and select the Biblio.mdb database.
  6. To test the connection, click Test Connection. If the connection you've defined is valid, you'll see the message box shown in Figure 7-11.
  7. Figure 7-11. The Data Link Properties dialog box can test a connection for you

     

  8. Click OK to save the connection definition.

Section 3. Defining a command in the Data Environment designer

In this section, you'll create a Command object, and you'll use the Data Environment designer's SQL builder to define the command text for the command:

  1. Click the Add Command button on the toolbar to create a new command.
  2. Right-click the command and choose properties from the Shortcut menu to display the Command Properties dialog box.
  3. Change the Command Name to Authors.
  4. In the Source of Data frame, select SQL Statement.
  5. Get the SQL Builder started by entering the following text in the white central text box portion of the window:
  6. SELECT * FROM Authors

  7. Click the SQL Builder button. Your screen should now look like Figure 7-12.
  8. Figure 7-12. The SQL Builder lets you enter SQL text or interactively define a SQL statement

     

  9. For this exercise, you're going to sort the recordset on the Author field. Drag the Author field from the Authors table list, and drop it in the row underneath the row with the asterisk.
  10. Since the Authors field is already being returned (as part of the asterisk selection), deselect the Output checkbox for the Author row.
  11. Click the Sort Type column of the Author row and select Ascending. Your window should now look like Figure 7-13.
  12. Figure 7-13. The text for the SQL statement is updated as you make changes to the definition of the SQL statement

     

  13. Click the Close button of the SQL Builder form, and choose Yes to save changes.

Section 4. Creating a bound form using the Data Environment designer

In this section, you'll use the Command object you've created to build a bound form:

  1. Creating a bound form is actually pretty easy (at least creating the bound fields is). Drag the command Authors from the Data Environment designer and drop it on the project's default form. Visual Basic creates bound controls for each of the fields in the SQL result set (see Figure 7-14).
  2. Figure 7-14. The Data Environment designer removes a lot of manual steps that are ordinarily required to create bound forms

     

  3. Move the group of controls on the form until they're roughly in the location shown in Figure 7-14.
  4. Add a new command button to the form. Change its Name to cmdMove-Forward and its Caption to Move Forward.
  5. Double-click the command button to access its Click event, and add the following code:
  6. DataEnvironment1.Recordsets("Authors").MoveNext

  7. Run the project by pressing F5 and notice how the fields populate with the first record in the database. Click the command button to cycle forward through the recordset.
  8. TIP:  

    This code doesn't check for end-of-file, but it serves to show how a recordset in a Data Environment designer is accessed. If you wanted to add a Move Previous button, you'd have to change the cursor type of the Command object in the Data Environment designer.

Highlighter's Index

Universal Data Access (UDA)

ActiveX Data Objects (ADO)

Connection Objects

Command Objects

Recordsets

Transactions

ADO Error Object

ADO Data Control

Data Environment Designer

Back to: MCSD in a Nutshell: The Visual Basic Exams


O'Reilly Home | O'Reilly Bookstores | How to Order | O'Reilly Contacts
International | About O'Reilly | Affiliated Companies

© 2001, O'Reilly & Associates, Inc.
webmaster@oreilly.com