This chapter discusses the technologies that Visual Basic provides to connect applications to data sources, and the terms Microsoft uses for these data access strategies. You will create data-enabled forms using the Data Environment Designer, the Data Form Wizard, and the ADO DataControl. You will also learn about some of the additional data tools provided in VB, including the Data Report Designer and the Query Builder.

In Chapter 3 you learned that Microsoft provides tools for database integration. When working with a simple, local database (for example, a table in an Access database residing on your own hard disk) then integrating an application with data can be quite simple.

However, things are rarely so straightforward in the real world. Your application may need to use data from the user’s hard disk, from a separate relational database residing on your network, or maybe even across the Internet. You may even need to use a data store that is not traditionally considered a database, like Excel or Outlook.

The issue for developers, therefore, is how they can get the data they need in a manner that is simple, reliable, and consistent. Microsoft’s answer is Universal Data Access (UDA).

Microsoft defines UDA as their "strategy for providing high-performance access to all types of information (including relational and non-relational data) across an organization from the desktop to the enterprise." (For more information see microsoft.com/data/). But still left unanswered is what UDA actually is.

1. UDA is a strategy, not a technology. (You’re not going to drop a UDA control onto your form.) Microsoft defines UDA as the union of the following four technologies: ADO (ActiveX Data Objects)
2. ODBC (Open Database Connectivity)
3. OLE DB
4. RDS (Remote Data Service)

ActiveX Data Objects (ADO) are the direct, everyday interface between the VB developer and the data. An ActiveX Data Object behaves like any other object—you invoke its methods, you reference its properties, and you monitor its events.

A sample Access database for the fictional company Northwind has been provided with your copy of VB. The following VB code segment demonstrates how easy it is to use ADO to get any needed data from this Access database.

Dim myConnection As ADODB.Connection
Dim myRecordSet As New ADODB.Recordset

Set myConnection = New ADODB.Connection
myConnection.Provider = "Microsoft.Jet.OLEDB.3.51"
myConnection.Open "C:\Program Files\Microsoft Visual Studio" & _
"\VB98\NWIND.MDB", , "admin", ""
, "admin", ""
myRecordSet.Open "SELECT * FROM Employees", myConnection
txtName.Text = myRecordSet.Fields("LastName")

myRecordSet.Close
myConnection.Close

These nine lines are all that is required to use the ADO to read the LastName field from the first record in the Employees table, and display the content in a text field txtName. All of the information contained in the database can be found in the Connection object myConnection and the Recordset object myRecordset.

ADO is a successor to and a superset of two previous object models: DAO (Data Access Objects) and RDO (Remote Data Objects). If you are maintaining previously authored applications in VB it is possible to continue using these older technologies, but Microsoft recommends using ADO for new development.

ODBC is a protocol for using SQL-based relational databases. After installing the appropriate ODBC driver for a particular database, it becomes possible to access the content in the database without having to know that particular database is implemented.

It is a common misperception that ODBC can only be used with sophisticated databases, such as Oracle or SQL Server. However, you can use ODBC to connect to any database if the appropriate driver exists. In addition to shipping drivers for Oracle and SQL Server with VB, Microsoft also ships ODBC drivers for Access, FoxPro, dBase, and even plain text. (This means you can use the power of SQL statements to run complicated queries on a simple text file, without importing it into a real database.)

Any maker of any version of any database product, if they write the needed driver correctly, can allow every VB developer in the world to use their product without having to learn a new interface. So why would anybody want to use anything else?

Here are three reasons:

1. ODBC can be limited. Because ODBC defines a common interface for all database products, it tends to enforce the lowest common denominator of product functionality. Because the ODBC interface predates the database design, the newest features are sometimes inaccessible.
2. ODBC can be slow. With a rigid interface, it is more difficult to tweak an application to optimize it for a destination platform. Each database vendor offers ways to optimize use of their own databases, but ODBC doesn’t always support these features.
3. ODBC can be difficult to use. Although its complexity is often abstracted away from a VB programmer, the low-level native ODBC API is complex to use and difficult to master.

For these reasons and others, Microsoft predicts that ODBC will eventually be eclipsed by OLE DB, discussed in the next section.

Many developers are using OLE DB every day, and they don’t even know it.

An application communicates with ODBC primarily by passing it text strings (usually SQL statements). By contrast, OLE DB uses a more granular object model. This is part of Microsoft’s larger strategy of breaking down data sources to provide smaller and more flexible components of functionality. (This approach isn’t accepted across the industry yet. Though Microsoft intends to expose all SQL Server functionality through OLE DB, this probably won’t be an option for some third-party databases for at least a couple of years.)

There are some advantages to this approach to exposing database functionality:

• As applications become more decentralized, it may become easier for applications to distribute database processing across multiple servers and tiers.
• It is easier for database vendors to support OLE DB than ODBC because more of the processing occurs within their own product.
• Because OLE DB is more of a specification than a technology, it should be able to accommodate a wider variety of future functionality than ODBC.

VB developers do not have direct access to OLE DB through a supplied control, but it is indirectly available either through ADO or by using an available API.

If you plan on doing DCOM development in the future, you will become much more intimately familiar with the details of OLE DB. However, because so much of its complexity is hidden from the VB developer, you can use it effectively without being familiar with all of its intricate workings. (If you do want to learn more about it, try www.microsoft.com/oledb/).

Of the four UDA components discussed, the only two that are directly accessible through the toolbox controls are ADO and RDS ("Microsoft ADO Control 6 (OLEDB)" and "Remote Data Control 6").

RDS is most useful for three-tiered applications—especially Web-based three-tiered applications.

When developing networked applications, reducing bandwidth is always a priority. If you are designing an Internet application, and one of your Web pages contains controls bound to a data source, you wouldn’t want your application to contact the data source tier across the network every time the user scrolls from record to record. RDS allows this information to be cached, either at the middle tier or at the client tier. This minimizes use of bandwidth, and thus speeds up execution.

RDS operates "on top" of ADO to negotiate the relationship with the data provider. While ADO works with a "live" connection to the data, RDO works with data that is disconnected and cached, either at the client or the middle tier.

You don’t have to worry about distributing RDS to your users, as long as they are using Internet Explorer 4 or higher. (When a user installs Internet Explorer 4 on their machine, they also install the RDS client automatically.)

Microsoft has presented developers with a cohesive strategy for accessing information throughout the enterprise, from the Desktop to enterprise server environments. Microsoft’s universal data access uses OLE DB as its low-level data access interface. For developers weaned on OBDC, OLE DB provides access to not only relational but nonrelational data as well. Contrary to competing forms of universal storage, OLE DB allows user access to data where it resides, avoiding the need to move the data into a single storage source.

OLE DB providers form the basis for simultaneous access to heterogeneous data sources. OLE DB providers for accessing VSAM, AS/400, and IBM AIX, and more are under development. Microsoft’s next version of SQL Server, version 7, will leverage OLE DB as its native data access interface.

Keep in mind that Microsoft ADO is the high-level data access interface built on top of OLE DB providers. RDO is an object model used for accessing relational data via OBDC. DAO is an object model for accessing local or SQL data via the Jet engine. All future development should focus on using ADO on top of OLE DB.

ADO is currently shipping version 2. ADO 2 provides numerous new features including persistent record sets, distributed transactions, and tighter integration with Visual Studio tools like the Data Environment Designer in VB.

The Data Environment Designer provides a highly programmable Design time environment for creating runtime programmatic data access. Data Environment objects can also leverage drag-and-drop to automate creation of data-bound controls.

For the exam, be sure to be familiar with the uses of the Data Environment, Data Form Wizard, Data Report Designer, and the ADO object model.

— by Michael Lane Thomas, MCSE+I, MCSD, MCT, A+

If you have developed database applications in other environments, then you have probably experienced some frustration in working with your data sources. It can take pages and pages of code to read in all the required data, present it to the user for modification, and save it back into the data store.

VB provides a compelling alternative. In a few simple steps, you can take the controls you are familiar with (TextBox, CheckBox, and so on) and enable them to read from and write to the database. (This is the same approach described briefly in Chapter 3.)

In order to do this, we must first connect to a data source. There are two ways of creating this connection:

• Using a DataControl on the form (common in VB version 5 and earlier)
• Using the Data Environment Designer (new in VB version 6)

Although the Data Environment Designer is more powerful, it takes a few extra steps to set up. Therefore, we will first examine connecting to a Data Source using a ADO DataControl, and address the Data Environment Designer later.

When a control is connected to a data source, it is considered bound. Though in the code example above we used code to manually populate the txtName control with the contents of the LastName field, we could have saved time by using Bound mode instead. This means that we would have instructed the TextBox control to automatically load the contents of the field into the text box, and then automatically save the user’s changes back into the database when completed.

In its simplest form, using Bound mode requires only three steps:

1. Drag an ADO DataControl and a text box onto the form.
2. In the DataControl, identify the appropriate database and record source.
3. In the text box, identify the appropriate DataControl and field.

In this section, we will perform the first two steps. (We will perform step three in "Getting Data From a Connection", below.)

Before proceeding, we want to make a copy of the Northwind database, so we may mutilate it to our hearts’ content. (The location of this database may vary, but it probably will be in a path similar to C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb. If it is in another path, use the Windows Find command to learn its location; if it isn’t on your machine, it is available from the VB installation CD-ROM.)

Our goal in this exercise will be to create a simple VB program that links to the Northwind database. At the end of the exercise, you will be able to see how many records are in the Employees table, but you won’t be able to actually see the records themselves.

1. Create a new directory on your hard disk: C:\Vbdemo. (If you choose to use a different path, make sure you use that directory for all future references where appropriate.)
2. Create a new VB application, and save the project and form into your new directory. (You can accept the default project and filenames provided. In a real application you would want to name your forms and controls according your internal naming standards, but for the exercises in this chapter we will be accepting the defaults in most cases.)
3. Copy the original Nwind.mdb into your new Vbdemo directory and rename it NwindTest.mdb.
4. Drag an ADO DataControl onto your form. Note that the default name is AdoDC1. (If you do not have this control available in your toolbox, add "Microsoft ADO Data Control 6.0 (OLEDB)" from the Components menu. Do not confuse this control with the intrinsic Data control. The ToolTip over the control should be Adodc, not Data.)
5. Right-click on the ADO DataControl and select ADODC Properties. From the General tab, click the Build button. On the Provider tab, select Microsoft Jet 3.51 OLE DB Provider, and from the Connection tab enter C:\Vbdemo\NwindTest.mdb. (Together, this should populate the ConnectionString property.)
6. On the RecordSource tab, select the Command Type 2 - adCmdTable and select the table Employees. (Note that VB provides you with a list with all the tables.) Click OK to return to the form.
7. Under the MoveComplete event of AdoDc1, add the following line of code:

If you’re the kind of user that likes to click on every button to see what you can break (you know who you are!), you’ve probably clicked on the other buttons and seen how they provide navigation among the eight employee records.

Above, we observed how to set the needed DataControl properties to connect to a data source. The three most important properties to support this process are ConnectionString, CommandType, and RecordSource.

This property defines the location of the data source. Unfortunately, the contents used for this property vary greatly. When you use VB to populate this property, you are provided three options (presented here in ascending complexity, not in the order presented on the form, in Figure 4-1): ODBC Data Source Name, Connection String, and Data Link File.

• ODBC Data Source Name The simplest option is to point to a previously created DSN on the local machine. (Of course, this doesn’t eliminate the work needed to create the connection, it just means it is created through the ODBC Control Panel instead.) Example: DSN = Nwind
• Connection String The information stored in a DSN is just a concatenated list of needed parameters. Instead of pointing to a DSN to reference these parameters, it is possible to simply list the properties themselves. EXAMPLE: "ODBC;DSN=PubsSQLDB;UID=sa;PWD=Fnord", or "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Vbdemo\NwindTest.mdb"
• Data Link File Instead of specifying a DSN or all the parameters required for a DSN, it is possible to point to a data link file (extension .UDL) that contains the needed parameters. In this way, it bears some similarities to a File DSN, but the contents of the file are not stored in a human-readable form. Also, the information in the UDL file is oriented more toward using OLE DB. Example: FILE NAME=C:\Program Files\Common Files\System\ole db\Data Links\MyDataLink.UDL.

The default for this property is 8 - adUnknown, but you will probably want to change it to 1 - adCmdText (retrieve content using an SQL Statement), 2 - adCmdTable (retrieve an entire table), or 4 - adCmdStoredProc (retrieve content as defined in a stored procedure.) Note that stored procedures are often more efficient than defining a query with adCmdText, because the database can preprocess the procedure, and execution can be faster. However, you do need additional access rights on the database to create new stored procedures.

Depending on the contents of CommandType, the RecordSource property will be used to store the name of the Stored Procedure or Table in the database (for example, "Employees"), or the SQL required for creating the record set (for example, "select * from Employees")

It is necessary to set the properties ConnectionString, CommandType, and RecordSource for every instance of the ADO DataControl used, but the defaults for the other properties will frequently be usable. (More information about these other properties is provided later in this chapter.)

After completing Exercise 4-1, you can navigate back and forth among the employee records, but you can’t actually see any of the underlying content. Maybe your mom is impressed, but your clients probably won’t be. (If they are, try giving them some shiny bolts and washers to play with—they’ll be mesmerized for hours.)

Many of the controls included with VB can be bound, and many third-party controls have this ability as well. (To quickly see if a control can be bound, see if it has a Data section when the properties are categorized. )

In Exercise 4-1, we used the ADO DataControl to connect to the Employee table of the Northwind database. In Exercise 4-2, we will illustrate how easy it is to bind controls to this DataControl by setting the DataSource and DataField properties.

As the exercise above should make clear, it is possible to create a simple application with very little work, if you are willing to accept the default behavior provided by VB and the underlying database.

Above, we observed how to set properties to enable controls to bind to a data source. The four properties most frequently involved with this are DataSource, DataField, DataMember, and DataForm.

DataSource This can point either to a DataControl or a Data Environment. All of the Data Environments and all of the DataControls on the current form will appear in this property list, but not the DataControls on other forms.

DataField This determines which field from the DataSource or DataMember will be bound to the control.

DataMember When using the Data Environment Designer, this property defines the DECommand within that designer. When using a Data Control, this property is not used.

DataFormat This determines how to display the content of the field. (It is often not necessary to set this field.)

When using a DataControl is it necessary to set DataSource and DataField; when using the Data Environment Designer it is necessary to set DataSource, DataField, and DataMember.

In the examples in this section, we have bound controls to the DataControl at design time. However, when using VB version 6, it is also possible to bind a control to a data source at runtime. (For example, we could have two separate connections, and a text box could switch back and forth, depending on user preferences.) Note that this was not an option in previous versions of VB.

You are not limited the TextBox control when binding to a data source. A majority of the Microsoft intrinsic controls, and several nonintrinsic controls, support data binding. Some of these controls, and the issues involved in using them, are discussed in this section.

Note that this is not a comprehensive list of the available controls by any means. This section only covers the controls that support binding for the controls that are intrinsic to VB 6, and a few of the more common nonintrinsic data-bound controls. Note that to observe these nonintrinsic controls you must have the corresponding items enabled from the Components dialog box (as shown in Figure 4-3). Many third-party controls also support data binding; for details on their operation consult the documentation supplied by the vendor.

We have already demonstrated the techniques required to bind a text box to a DataControl. Binding a Label control uses an identical process, except that it is the Caption property, not the Text property, that automatically binds to the field. (Note that as a rule, the property binding to the database field is the same as the default property for that control.)

Although labels are, by their nature, not editable from the user interface, it is still possible to use them to update a database. If a Label control is bound, if the Caption property is modified programmatically, the new Caption value can be saved into the database.

Remember that the check box doesn’t have two states, it has three: checked, unchecked, and grayed. You can usually bind a check box to your database’s equivalent of a Boolean field, but the precise behavior may vary slightly from database to database. If you find that your results are unsatisfactory, consider using a combo box with Yes/No options bound to a text field, or alternatively leaving the CheckBox control unbound and manipulating it with code instead.

These controls allow you to view Binary Large Objects that are stored in a database. The PictureBox and Image controls can only be used with standard VB graphic types (BMP, ICO, WMF, GIF, or JPG.) By contrast, the OLE control can be used with a much wider variety of graphic and document formats.

Distinguishing among these four controls is a cause of endless confusion both in real-world development and on the exam. We will focus our discussion on the distinction between the ComboBox and the DataCombo, but you can apply the same principles to ListBox and DataList, accordingly. (For an explanation of the distinction between the ComboBox and ListBox controls, refer to Chapter 3.)

A combo box binds to a DataControl exactly like a text box does, and with the same results: The Value field reads from and writes to the field specified in DataField and DataSource. The developer is still responsible for populating the List field manually to provide the options available for the Value field.

All that is added in DataCombo (and DataList) is the automated population of these lists. A separate DataControl needs to be created and referenced in the RowSource property of the DataCombo control, and the field to load needs to be referenced in the BoundColumn property. This can be disorienting, but Table 4-1 should clarify the issue.

Note that it is conceivable that the DataSource and RowSource could refer to the same control, for example, if you wanted a list to provide the values already entered in that field. However, in a majority of cases the RowSource will be pointing to an entirely different table or query.

A DataGrid is a very powerful way to create a lot of functionality very quickly. This control provides a direct exposure to all of the data defined by the DataControl in a spreadsheet format. Although the compact format sometimes can be disorienting to an end user, data grids are popular for presenting data for navigational or summary purposes.

Although a data grid has a DataSource property, unlike the controls listed above, it has no DataField property (or, more precisely, there are several DataField properties that are assigned to the specific columns inside the grid).

If you go back to the form created in Exercise 4-1, you can bind a new grid with the following steps:

• Drag a DataGrid control onto the form (if it is not available in the Toolbox, add it from the Components dialog box).
• While the grid is selected, press F4 (Properties) and change DataSource to AdoDc1. (If you select Properties from the right-click contextual menu, you will instead see the tabbed property box.)

Right click on the DataGrid, and select Retrieve Fields. (When you retrieve fields, you copy the definition of each column currently defined in the record set into a corresponding column in the grid. These become the defaults for each column, but is easy to override these definitions.)

• Run the application. You should see a grid containing all the records and fields in the Employee table.

Though the appearance may look a little clumsy, these grids are easy to customize. If you stop the application and right-click into the data grid to investigate the properties available, you will see how easy it is to change the appearance of the grid—for example, making certain columns wider, locked, or invisible. Some of the more frequently used properties are listed below:

At the grid level: Caption, AllowAddNew, AllowDelete, ColumnHeaders, BorderStyle

At the split (splits are columns grouped together) level: AllowRowSizing, RecordSelectors, MarqueeStyle, ScrollBars, AllowFocus

At the column level: Caption, DataField, DataFormat, Alignment, Width, WrapText, Locked, Button

The full feature set of the data grid exceeds our discussion here, but you may want to browse through the various tabs of the DataGrid control to familiarize yourselves with the options available before taking the certification exam. (If you like the functionality available in the data grid, you may want to investigate purchasing True DataGrid, manufactured by Apex, at www.apexsc.com/. The grid provided for free within VB is a simplified version of Apex’s commercial product.)

We will observe an example of a form with a data grid on it below, in "Creating Data-Bound Forms Using the Data Form Wizard."

The MSHFlexGrid control allows the user to visually sort, format, and combine summary and detail database content. There are two major differences between the MSHFlexGrid and the DataGrid:

• The data grid is read/write, and the MSHFlexGrid is read-only.
• While the data grid presents all data in a simple static matrix, the MSHFlexGrid can respond to user interaction to alter its presentation.

This control allows multiple iterations of another control to be displayed in the space defined by the DataRepeater control.The control to be repeated should be populated into the RepeatedControlName property (for example, with MSCAL.Calendar.7 or RICHTEXT.RichTextCtrl.1.) This control is new in VB version 6.

Although this control is more frequently used by programmatically populating it with the data points, is possible to bind this control and present the retrieved record set as a graph.

Microsoft provides a variety of tools in VB to view and manipulate your connections to your data sources. We will discuss the following three in this section:

• Data Environment Designer
• Data View Window
• Query Builder

In Exercise 4-1 and 4-2 we used a DataControl, but for the remainder of this chapter we will use a new feature in VB 6 called the Data Environment Designer.

Using Data Environment Designer has many advantages over using form-based DataControls:

• It segregates the complexity of database operations from your form code.
• It enables a drag-and-drop interface with forms and DataReports.
• It integrates with other Visual Studio tools.
• It facilitates distribution of database connections across applications and among developers.
• It permits more control over the formatting of the bound fields.
• It allows multiple forms to access data in the same way.

The Data Environment Designer allows you to create and manipulate the following objects:

DataEnvironment This is the highest-level object in the designer, but it really is only used to group the other content; it isn’t used for programming.

DEConnection This determines which database will be used, how the database will be accessed, and with what permissions.

DECommand This is the actual definition of the data accessed within the DEConnection. It can refer to a table, an SQL statement, or a stored procedure.

Because the Data Environment Designer didn’t exist before VB version 6, you likely will encounter DataControls if you are working on legacy applications. If you have to provide support for these existing applications you can continue to use DataControls, but to take advantage of the newest Microsoft technologies, you will have to convert your application to support the Data Environment Designer. In Exercise 4-3 we will perform this conversion on the project built in Exercises 4-1 and 4-2.

1. Open the existing application, run it, and scroll back and forth to confirm that everything is still functioning. Stop the application. Delete the existing DataControl.
2. Select Project | More ActiveX Designers | Data Environment. Observe the DataEnvironment1 window that appears.
3. On the Provider tab, select Microsoft Jet 3.51 OLE DB Provider. Right-click on Connection1 and select Properties to bring up the Data Link Properties window. (In some configurations, this window may come up automatically when creating a Data Environment.)
4. On the Connection tab, select the path to the database (should be in the Vbdemo directory). Click Test Connection and confirm that your test connection succeeded. (The other options on the Connection and Advanced tabs are used for more sophisticated database operations.) Click OK.
5. Click the Add Command button. Right-click the newly created Command node (Command1) and select Properties. (Again, this window may automatically be displayed on creation of a new command.)
6. Set Database Object to Table and Object Name to Employees. Click OK.
7. Return to Form1. For each of the bound controls, change the DataSource to DataEnvironment1, and DataMember to Command1.
8. Run the application. The first record ("Nancy Davolio") should appear on the form, but notice that there is no longer a navigation device on the form itself.)

Your application is now connected to your database through the Data Environment Designer.

If you return to the Data Environment Controller you created, you will observe many more features available. Some of these include the following:

• Define the access rights available for each DEConnection. The access rights are as follows: Read, ReadWrite, Share, Deny None, Share Deny Read, Share Deny Write, Share Exclusive, and Write. The default behavior is Share Deny None.
• Create DECommands under the DEConnection; these allow more detailed actions to be stored and distributed. The tabs containing the properties that can be modified are General, Parameters, Relation, Grouping, Aggregates, and Advanced. (The Grouping tab is displayed in Figure 4-4.) You can also add child DECommands that nest under the existing DECommands.

• Change the default control mapping used to represent the DECommand field when dropped onto a form, under Options. (The default for all fields is a text box, except for the following two: Boolean is a check box, and a Caption is a label.)
• Arrange the content by DEConnections, or by Objects. (Their relationship is hierarchical, but not permanent. Using the General tab of the Property dialog box, you can assign an existing DECommand to a new DEConnection.)
• Write VB code for the associated events at the DataEnvironment, DEConnection, or DECommand level.

The properties associated with the DEConnections and the DECommands will be discussed in "Creating Data-Bound Forms Using the Data Environment Designer."

If the Data Environment Designer offers all this, why would anybody ever use the ADODC control? Good question.

Data Environment Connections will eventually replace standalone DataControls. When Microsoft introduces a technology that is somewhat redundant to another Microsoft technology, support for the old technology usually withers and dies within a couple of years. (If you doubt me, go have a chat with a crusty old Excel Macro programmer.)

That said, there may be some legitimate reasons for continuing to use DataControls, at least for a while:

• The configuration of a DataControl is slightly quicker than the equivalent configuration of a Data Environment Designer DEConnection. If you’re building a very small application, you may save some development time.
• If you need a navigation mechanism on your form anyway, there’s one built into the DataControl.
• Even for developing new forms, you may be able to find a lot of code that has been developed using DAO and RDO DataControls, and it would be slightly easier to convert that code to support a ADO DataControl than it would be to support a DECommands.
• Finally, because Microsoft has years of experience supporting DataControls, while the Data Environment Designer is a new feature, it may be possible that these DataControls may provide more, er, consistent behavior, at least until Microsoft releases VB version 6 service release 3.

The UserConnection designer (available from Project | Add Microsoft UserConnection) is a simpler version of the Data Environment Designer, intended for developers who are still supporting RDO applications. Because Microsoft recommends using ADO for new development, it is unlikely that this designer would be used frequently, except for legacy applications. It is not on Microsoft’s exam outline, and is only mentioned here to put it in proper context.

At first blush, it can be hard to tell the difference between the Data Environment Designer and the Data View window (both new in VB 6.) They both appear to be hierarchical lists of the tables and fields available to your application.

The primary distinction is that the Data Environment Designer interacts much more closely with your application, while the Data View Window interacts more directly with the databases. From the Data Environment Designer, you can drag fields onto a form, but from the Data View window you can view the structure of the underlying tables using the Database Designer. (It is also possible drag items from the Data View window to the Data Environment Designer, but not vice versa.)

The Data View window provides an excellent summary of your data resources. In addition to allowing the user to view DEConnections from multiple DataEnvironments, it also allows drag-and-drop access to the DataEnvironment connectors.

Perhaps more valuable, however, is the integration of the Data View window with the Microsoft Visual Database Tools—the Database Designer and the Query Designer. (The Query Designer is discussed under "Creating Database Queries Using Query Builder"; the Database Designer is discussed below.)

The Database Designer provides a visual representation of the relationships among the tables, by creating database diagrams. Unfortunately, these diagrams cannot be used with every database provider. For example, they work with SQL Server databases, but they do not work with Access databases. (Similarly, you can create new tables and views from the VB interface when using SQL Server databases, but not with Access databases.)

A Data View window showing this contrast is presented in the following illustration.

Because our Northwind Access database will not support database diagrams, the screen shots from this section are from the Pubs database that comes with SQL Server.

After creating a new Data Link to the Pubs database (Right-click on Data Links, then choose Add a Data Link), right-click on Database Diagrams, and select New Diagram.... This presents a big, empty white screen. However, if you drag the Authors, Titles, and TitleAuthor tables from the Data View onto the big empty white screen, you will not only see the fields available for these tables but also the relationships among these tables.

The default behavior is to present the names of the columns only. However, if you right-click within the table in the database diagram, you can choose among these display options: Column Properties, Column Names, Keys, Name Only, and Custom. (The properties shown by Custom are selected in Modify Custom View. An example of Column Properties is presented in Figure 4-6. Note that in many cases the properties of the tables and relationships can be modified as well as displayed.

The remaining options available from the Diagram menu are primarily associated with formatting. The diagram in Figure 4-6 includes an example of a text annotation, which can be added by selecting New Text Annotation from the Diagram menu.

Some other options include:

• Displaying labels to identify the relationships
• Viewing and recalculating the page breaks
• Resizing or arranging the tables on the diagram

If you’ve worked with other database applications in the past, you’ve probably worked with SQL statements. It probably wasn’t too hard to hand-code simple SQL statements, like the following:

Select EmployeeID, Salary from Salaries where Salary < 50000

However, it can be difficult to manually construct correct grammar for SQL queries that span multiple tables with complex filtering. Fortunately, Microsoft has provided a visual tool to automatically generate grammatically correct SQL statements from simple parameters defined by the developer.

1. From the Add Ins menu, select Visual Data Manager.
2. From File, select Open Database | Access…, and browse to the NwindTest.mdb database. (Observe that you now can see the available tables in the Database window.)
3. From the Utility menu, select Query Builder.
4. From the tables list on the left, select the tables Employees and Orders.
5. Click on the table Set Table Joins.
6. From the Join Tables dialog box, highlight the Employees and Orders tables; then select the EmployeeID field in both of the field lists (see Figure 4-7). Click Add Join to Query, and then Close. Underneath the Set Table Joins button should now appear the relationship Employees.EmployeeID=Orders.EmployeeID.

To use the queries built by this screen, click the Copy button to bring the SQL into the clipboard. Then the SQL statement is available for use anywhere else in your application (for example, as the RecordSource for a DataControl.)

Query Builder is one component of the Visual Data Manager add-in, illustrated in Figure 4-9, below. The Visual Data Manager is not as new as the Database Designer, but it still has the following advantages over its rival:

• You can view and edit the actual data underlying the data source.
• You can use its form builder to automatically create forms based on an underlying database. This is a distinct tool from the Form Wizard, but in some ways it is more flexible.
• The entire Data View tool described above is itself programmed in VB, and Microsoft has provided the source code. If you’re the kind of developer that learns more from sample applications than from formal documentation, in this application you can find code to perform almost every kind of data manipulation imaginable.

Starting with VB version 6, you can also build queries by creating a New View from the Data View menu. To do so, simply drag the desired tables from the Data View into the New View window, create any additional relationships required, check the boxes for the fields you want, specify any needed criteria, and select Run from the Query menu to confirm that the query runs correctly. The resulting screen should resemble Figure 4-10. A note for the test: the VB test outline mentions the Query Builder, but not the Query Designer. However, even if the Query Designer doesn’t appear on your test, it is still valuable to know what functionality it provides.

This tool is intended to create new views to be saved back into the database, but there is no reason you cannot use it to create grammatically correct SQL statements. Just select the SQL statements created, copy, and paste to where you need it.

Once you have determined and established access to your data sources, it is time to create the connection onto the form. In this section we will be discussing the following approaches to creating the needed forms:

• Drag-and-drop, using the Data Environment Designer
• Automated form generation, using the Data Form Wizard
• Direct programming, using the ADO DataControl

In addition, we will be discussing the Data Report Designer, which allows simple reports to be created and integrated with the Data Environment Designer quickly and easily.

Instead of dragging a control from the Toolbox and then setting its properties to bind to a DECommand, it is possible to automatically drag controls directly from a Data Environment Designer onto the form. To do this, you simply have to have the DECommand in the Data Environment Designer expanded to expose the fields, drag the field onto the appropriate form, and drop it at the desired location.

This seems very impressive, and it is a nice addition to the development environment, but it is clearer to understand what is happening if you realize that VB is simply performing the following three steps that you have already learned:

• Place a Label control on the form and set the Caption property to the field name
• Place a TextBox control on the form
• Set the DataField, DataMember, and DataSource properties of the text box to point to the Data Environment Designer.

The bound control is usually a text box; however Boolean and Caption fields have other defaults (CheckBox and Label, respectively.) You can change these settings under Options from the corresponding Data Environment.

When using this technique, remember that you have no DataControl on the form, and so you will need to develop your own navigation interface.

Although you can implement a quick-and-dirty application by keeping the property defaults, an understanding of all the properties associated with DEConnection and DECommand is vital for developing more complex applications. These properties are presented in the following sections. (Where applicable, sample values are listed in parentheses.)

The following five properties are grouped under Connection:

1. Attributes Determines whether a new transaction is automatically started after the previous transaction is committed or rolled back.
2. CommandTimeout (30) The number of seconds to wait for a command to execute before generating an error.
3. ConnectionSource (Provider=…) Determines the source for the DEConnection (a connection string, a DSN, or data link file).
4. ConnectionTimeout (15) The number of seconds to wait to create a connection before generating an error
5. CursorLocation (2 - adUseServer) Determines where the responsibility is for remembering the current record position: with the client application, or with the database. (Client-side cursors are well suited for smaller ResultSets—they reduce client traffic—while database cursors can improve performance for larger result sets. The default leaves it to ADO to determine which to use.)

The following four properties are grouped under Design Authentication:

1. DesignPassword Password stored during development.
2. DesignPromptBehavior (2 - adPromptComplete) Determines when (and if) user must log on during Design time.
3. DesignSaveAuthentication (False) Determines whether user identification is retained during development.
4. DesignUserName Username stored during development.

The following four properties are grouped under Run Authentication:

1. RunPassword Password used during execution.
2. RunPromptBehavior (2 - adPromptComplete) Determines when (and if) user must log on during execution.
3. RunSaveAuthentication (False) Determines whether user identification is retained during execution.
4. RunUserName Username used during execution.

The following three properties are grouped under Command:

1. CommandText (Customers) Name of table, stored procedure, or SQL statement to execute.
2. CommandType (2 - adCmdTable) Determines what the CommandText is requesting (SQL, Table, Stored Procedure, or Unknown).
3. ConnectionName (Connection1) Specifies the name of the parent DEConnection

The following nine properties are grouped under "Advanced":

1. CacheSize (100) Determines how many records are stored in memory at a time.
2. CallSyntax Identifies the structure of the CommandText parameter.
3. CommandTimeout (30) See DEConnection, above.
4. CursorLocation (3 - adUseClient) See DEConnection, above.
5. CursorType (3 - adOpenStatic) Determines the type of cursor used. See "CursorType Details", below.
6. GrandTotalName Defines name used for record set when aggregated.
7. LockType (1 - adLockReadOnly) Determines when the database locks the record content to ensure other users don’t edit the same content—when a user tries to first edit the record (2 - Pessimistic) or when they try to save it back to the database (3 Optimistic and 4 - LockBatchOptimistic).
8. MaxRecords (0) Indicates if there is a maximum number of records to return.
9. Prepared (False) Determines whether command should be precompiled before executing (slows down first execution, speeds up subsequent use).

There are four cursor types, listed below in descending order of flexibility:

• adOpenDynamic
• adOpenKeyset
• adOpenStatic
• adOpenForwardOnly

A dynamic record set allows the user to see all updates made by other users.A keyset record set will be able to see changes to records that existed when the record set was generated, but will not be able to see new records that have been created since then. A static record set is a read-only copy of the record set, and forward-only is like static except the cursor can only move in one direction. These cursor types are compared in Table 4-2.

Microsoft has presented developers with a cohesive strategy for accessing information throughout the enterprise, from the desktop to enterprise server environments. Microsoft’s universal data access uses OLE DB as its low-level data access interface. For developers weaned on OBDC, OLE DB provides access to not only relational but nonrelational data as well. Contrary to competing forms of universal storage, OLE DB allows user access to data where it resides, avoiding the need to move the data into a single storage source.

OLE DB providers form the basis for simultaneous access to heterogeneous data sources. OLE DB providers for accessing VSAM, AS/400, and IBM AIX, and more are under development. Microsoft’s next version of SQL Server, version 7, will leverage OLE DB as its native data access interface.

Keep in mind that Microsoft ADO is the high-level data access interface that is built on top of OLE DB providers. RDO is an object model used for accessing relational data via OBDC. DAO is an object model for accessing local or SQL data via the Jet engine. All future development should focus on using ADO on top of OLE DB.

ADO is currently shipping version 2. ADO 2 provides numerous new features including persistent record sets, distributed transactions, and tighter integration with Visual Studio tools like the Data Environment Designer in VB.

The Data Environment Designer provides a highly programmable Design time environment for creating runtime programmatic data access. Data Environment objects can also leverage drag-and-drop to automate creation of data bound controls.

For the exam, be sure to be familiar with the uses of the Data Environment, Data Form Wizard, Data Report Designer, and the ADO object model.

— By Michael Lane Thomas, MCSE+I, MCSD, MCT, A+

When you use a connection string, you are essentially concatenating multiple parameters required by the database into a single property, separated by semicolons. For example:

ODBC;DRIVER=SQL Server;UID=sa;DATABASE=MyDatabase;SERVER=SqlServer65

A DSN is very similar to a connection string, storing the same parameters but in a different place—either in the Registry, or in a standalone file.

There are three types of DSN connections: User, System, and File. These are segregated on the first three tabs on the ODBC Data Source Administrator dialog box, illustrated in Figure 4-11. All three of these DSNs allow similar access to database resources, but they are primarily distinguished by their scope:

• User DSN enables access for the user that is currently logged on.
• System DSN enables access for the users of that computer, and local system services.
• File DSN enables access for any application that can navigate to the current path.

The attributes for the File DSN are stored in a simple text file, so it provides an excellent resource to understand what is going on behind the scenes. (The User and System DSNs are stored in the Registry, so they’re a little harder to observe.) If you have created a File DSN, then you can open this DSN as a text file. The DSNs are frequently stored in C:\WINDOWS\ODBC\Data Sources\, but if you have trouble finding the directory on your machine, use the Window Find feature and search on the file name of your File DSN. If you open up your File DSN with Notepad, the content should resemble the following:

[ODBC]
DRIVER=SQL Server
UID=sa
DATABASE=MyDatabase
WSID=MARTOMI
APP=Microsoft (R) Developer Studio
SERVER=SqlServer65

What this should emphasize is that the DSN doesn’t contain mysterious information—just the parameters required to connect to the database.

When you use a connection string, you can either point to a DSN, or you can pass additional parameters in the connection string that identifies the information that would normally be in the DSN. This is called using a DSN-less connection.

The Data Form Wizard has been around for the last several versions of VB, but even though it isn’t cutting edge, there are still times where it may be the best choice for form development. Because it creates forms using a one-time wizard, you cannot use this interface to make changes after the initial creation. However, it still may be useful in the following circumstances:

• When you need a "quick and dirty" interface for an application that is needed temporarily.
• When you want to see examples of the code to support basic data manipulation.
• When you want to develop your own custom forms, and you’re willing to do your own coding and layout manually, but you want to work from a template to save time.

There are multiple ways to initiate this wizard:

• From Project | Add Form, select VB Data Form Wizard.
• From the Add-In Manager, select VB Data Form Wizard, and check Loaded/Unloaded. Now the option Data Form Wizard should be available from the Add-Ins menu.
• From the Visual Data Manager, select Utility | Data Form Designer… This is not exactly the same wizard referenced by the above two options, but the forms it creates are quite similar.

In Exercise 4-5, we will use the Data Form Wizard to create a data form containing the fields for the Employee record.

1. From the Project menu, select Add Form, and then choose VB Data Form Wizard.
2. From the screen labeled Introduction, click Next. Ignore the profile setting. Although we will not be using profiles in this exercise, know that a Wizard Profile (.RWP) allows you to create many forms with similar appearance and function.
3. From the screen labeled Database Type, select Access (the default). Click Next.
4. From the screen labeled Database, browse to C:\Vbdemo\NwindTest.mdb. Click Next.
5. From the screen labeled Form, select Master/Detail. (The other options available are Single Record, Grid Datasheet, MS HflexGrid, and MSChart.) Name the form frmEmployee. Select the binding type ADO Data Control. Click Next.
6. From the screen labeled Master Record Source, select Employees as the record source. Copy EmployeeID, LastName, FirstName, BirthDate, HireDate, and City to the left pane. Make EmployeeID the column to sort by. Click Next.
7. From the screen labeled Detail Record Source, select Orders as the record source. Copy OrderID, EmployeeID, OrderDate, RequiredDate, ShippedDate, and ShipName from the left pane to the right pane. Make OrderID the column to sort by. Click Next.
8. From the screen labeled Record Source Relation, select EmployeeID in both the Master and Detail columns. Click Next.
9. From the screen labeled Control Selection, accept all the defaults. Click Next.
10. From the screen labeled Finished, leave the profile name as (None). Click Finish.
11. Under the Project menu, select Project 1 Properties, and change the Startup Object to frmEmployee. Click OK.
12. Run the application (the form should resemble Figure 4-12). Using the DataControl at the bottom of the form, scroll to employee #4 (Margaret Peacock). The lowest number OrderID for Margaret Peacock should be 10250.

Using the Data Form Wizard, in just a few minutes you can construct a multitable relational interface. But more important, you now have an example that you can extend and customize to meet your specific needs.

The approaches to development are somewhat different when using the ADO DataControl. For example, you can’t drag bound fields directly onto the form, and you can’t refer to the same DataControl from multiple forms..

However, the actual properties associated with the ADO DataControl are remarkably similar to those used when developing with the Data Environment Designer. The biggest difference, of course, is that the properties associated with both the DEConnection and the DECommand are rolled up into a single object.

Table 4-3 lists the properties associated with ADO Data Control. As should be clear, most of them map quite closely to the DECommand or DEConnection, though in some cases, the mapping is approximate.

The following properties map less definitively to the Data Environment Designer controls.

• BOFAction determines the behavior when the user tries to move the current location before the first record in the record set. The options are to reject the user’s attempts to reposition, or to accept the attempt but trigger the reposition event on the invalid record.
• EOFAction is similar to BOFAction, but it applies to attempts to move past the end of the record set. There is a third option for EOFAction, which, instead of generating an error, allows the user to automatically create a new record and begin to populate it.
• Mode determines whether or not the user has the rights to modify the data.

If you’ve worked with previous versions of VB you know that the reporting tool was integrated loosely with the development environment, to put it mildly. (It was simply a version of Crystal Reports that came bundled with VB, but required separate and redundant connections to the same data source.)

In VB version 6 Microsoft has provided the Data Report Designer, which resembles a hybrid of the Access Reporting tool and a stripped-down version of Crystal Reports. Although the feature set isn’t nearly as full as these other tools, it does integrate much more tightly with the rest of your VB application, especially because it supports dragging-and-dropping fields from the Data Environments directly onto the Data Reports.

Superficially, the process of designing a data report resembles designing a form: add the data report to the project, drag controls from the Toolbox onto it, and then use the Show method from your code to display it to the user.

Note, however, that although the same General controls are available for forms, MDI forms, property pages, and user controls, these controls gray out while editing a Data Report. The only controls available for use will be the ones under Data Report in the Toolbox, as shown in Figure 4-13.

The steps for creating a data report will be explored in Exercise 4-6.

1. Select Project | Add Data Report. Save the new data report, accepting the default name (DataReport1.Dsr).
2. Change the properties for DataReport1 as follows: DataSource becomes DataEnvironment1, and DataMember becomes Command1.
3. Right-click on the report, and select Retrieve Structure. This synchronizes the values in the Data Environment Designer with the number of group header and footer sections on the Data Form.
4. In the Toolbox, click the Data Report heading. This will scroll the applicable controls to the top of the form, as illustrated below.
5. Drag a RptTextBox control into the Detail section of the data report. Change its properties as follows: DataMember to Command1, and DataField to LastName.
6. Under Project | Project Properties, set the startup form to DataReport1.
7. Run the application. You should now see all the Employees appear on your screen.

The runtime interface Microsoft provides is rather spartan. You can scroll from page to page, you can print, and you can export the contents of the report to a file. You cannot create other clickable events on the report (to provide a drill-down interface, for example). The few features available in the Data Report are summarized below:

• You can put text (RptLabel), lines (RptLine), shapes (RptShape), and pictures (RptImage) on the report, but there is no way to make any of these controls data-bound. For example, you cannot have a report that includes pictures that come from a database.
• You can use the RptFunction control to summarize data. (The data summarized is not necessarily the data in the detail section; you must bind the data field to this summary field separately.) You cannot enter formulas like you can in Access or Crystal Reports—instead you must choose from one of the eight provided functions (Sum, Average, Minimum, Maximum, Count of Rows, Count of Non-Nulls, Standard Deviation, and Standard Error.
• You can customize the formatting of the report and controls to a certain extent, such as determining whether or not a RptTextBox can grow in height when needed, where page breaks should be created, and whether or not like data should be grouped together on the same page.

Microsoft has integrated the data report with the data environment to allow developers to accelerate report development by performing the following steps:

• Expand the Data Environment Command so you can see the fields it contains.
• Drag a field from the data environment onto the data form, into the Detail section. Observe that both a RptLabel and a RptTextBox are created on the data form, and that the DataMember and DataField are both automatically populated to point to the appropriate Data Environment Designer.
• Drag the entire data environment command onto the gray bar above the Detail section. Observe that all of the fields have been added to the Detail section.

As you have observed, the functionality provided is limited but extremely easy to set up. For complicated applications you may need a more powerful reporting tool, such as the commercial version of Crystal Reports, but for simple applications (and the certification test) you probably won’t mind the small feature set.

Universal Data Access is Microsoft’s approach to providing flexibility and simplicity in connecting to data sources. It is made up of ActiveX Data Objects (ADO), Open Database Connectivity (ODBC), OLE DB, and Remote Data Services (RDS).

The primary VB tools for working with the structure of databases are the Data Environment Designer and the Data View window.

It is possible to create forms with bound controls by using the Data Environment Designer, the Data Form Wizard, and the ADO DataControl.

Microsoft provides additional tools for using data with forms, including the Data Form Designer (which allows database reporting) and the Query Builder (which allows users to design and test queries visually.)

1. UDA is the union of the following four technologies:ADO (ActiveX Data Objects)
2. ODBC (Open Database Connectivity)
3. OLE DB
4. RDS (Remote Data Service)

• An ActiveX Data Object behaves like any other object—you invoke its methods, you reference its properties, and you monitor its events.
• ODBC is a protocol for using SQL-based relational databases.
• VB developers do not have direct access to OLE DB through a supplied control, but it is indirectly available either through ADO or by using an available API.
• RDS is most useful for three-tiered applications—especially Web-based three-tiered applications.
• Three-tier architecture describes the distribution of application processing across the following three tiers:
• Client tier (for example, a user’s machine)
• Middle tier (such as Microsoft Transaction Server or an ActiveX DLL)
• Data source tier (such as an SQL Server)
• There are two ways to connect to a data source:
• Using a DataControl on the form (common in VB version 5 and earlier)
• Using the Data Environment Designer (new in VB version 6)
• When a control is connected to a data source, it is considered bound.
• The three most important Data Properties for Data Controls are ConnectionString, CommandType, and RecordSource.
• Many of the controls included with VB can be bound, and many third-party controls have this ability as well.
• The four most frequently involved Data Properties for Bound Controls are DataSource, DataField, DataMember, and DataForm.
• A majority of the Microsoft intrinsic controls, and several nonintrinsic controls, support data binding.
• Value, DataField, and DataSource apply to most data-bound controls, but List, BoundColumn, and RowSource apply only to the population of the lists in the DataCombo and DataList controls.Using Data Environment Designer has many advantages over using form-based DataControls.
• The Data Environment Designer interacts much more closely with your application, while the Data View Window interacts more directly with the databases.
• Microsoft has provided a visual tool called Query Builder to automatically generate grammatically correct SQL statements from simple parameters defined by the developer.
• Data Report Designer allows simple reports to be created and integrated with the Data Environment Designer quickly and easily.
• It is possible to automatically drag controls directly from a Data Environment Designer onto the form.
• Although you can implement a quick-and-dirty application by keeping the property defaults, an understanding of all the properties associated with DEConnection and DECommand is vital for developing more complex applications.
• The makers of the test frequently have questions where one or more of the options are properties or values that don’t exist. Even if it isn’t possible to memorize all of the terms, try to at least familiarize yourself with them so it is easier to tell when the test authors are pulling your leg.
• The Data Form Wizard has been around for the last several versions of VB, but even though it isn’t cutting edge, there are still times where it may be the best choice for form development.

The following Self-Test questions will help you measure your understanding of the material presented in this chapter. Read all the choices carefully, as there may be more than one correct answer. Choose all correct answers for each question.

1. Which of the following are components of Microsoft’s Universal Data Access?
1. ADO
2. ODBC
3. ODBE DB
4. OLE DB
5. RDOC
6. RDS
   A, B, D, and F. ADO, ODBC, OLE DB, and RDS. ODBE DB and RDOC do not exist.
2. Which of the following ODBC drivers are supplied with VB version 6?
1. Access
2. FoxPro
3. ISAM
4. Oracle
5. SQL Server
6. Text files
   A, B, D, E, and F. Access, FoxPro, Oracle, and SQL Server. ISAM is a term used to describe certain file-based databases, but there is no ODBC driver that accommodates all ISAM databases.
3. Which of the following is not an advantage of OLE DB?
1. It is easier to decentralize database processing.
2. It is relatively easy for VB programmers to program using the OLE DB object model.
3. Its implementation architecture is more open than ODBC.
4. Unlike ODBC, OLE DB allows the developer to pass SQL statements to the database.
   B. Exposure of OLE DB to VB developers. The OLE DB object model is not directly exposed to VB programmers (usually it is necessary to use ADO, or a specialized API).
4. Which of the following are controls included with VB version 6?
1. ADO Data Control 6 (OLEDB)
2. ODBC Control 6
3. OLEDB Data Control 6 (ADO)
4. RemoteData Control 6
5. Universal Data Access Control 5
   A and D. ADO Data Control 6 and RemoteData Control. ODBC and OLEDB are less directly accessible to the VB developer.
5. Which of the following would be least likely to be used with a VB application that used a database that resided on the user’s own hard disk?
1. ADO
2. ODBC
3. OLE DB
4. RDS
   D. RDS. RDS stands for Remote Data Services, and is primarily used for implementing three-tier applications, either on a LAN or across the Internet.
6. Which of the following properties would contain a 3 when the user was on the third record in the record set?
1. AbsolutePosition
2. Bookmark
3. CursorLocation
4. Index
   A. AbsolutePosition. CursorLocation determines whether the client or the server stores the current record position. Bookmark can be used to identify and locate a record, but it is not stored as a number. Although databases have indexes, in VB the Index property is most frequently associated with the creation and use of control arrays.
7. What are the three options available from the ADO Data Control General Property Page to populate the ConnectionString property?
1. Use Connection String
2. Use Data Link File
3. Use Database Link File
4. Use ODBC Data Source Name
5. Use RecordSource Name
   A, B, and D. Use Connection String, Use Data Link File, and Use ODBC Data Source Name. It is possible to connect to most databases using any one of these three techniques.
8. What is an advantage of using adCmdStoredProc instead of adCmdText?
1. Stored procedures support SQL, while this is not an option when using adCmdText.
2. Stored procedures are supported by more database vendors than the use of adCmdText".
3. Using a stored procedure allows the command to be precompiled by the database and facilitates faster execution.
4. You don’t need as many database access rights to use stored procedures as you would need when using adCmdText.
   C. Faster database execution. You sometimes need more database access rights to create a stored procedure than you would need to access existing tables with SQL. Both methods usually use SQL. ISAM databases frequently don’t support stored procedures.
9. Of the options below, which one best describes the ADO DataControl properties that always have to be set to access a data store?
1. ConnectionString
2. ConnectionString and RecordSource
3. ConnectionString, RecordSource, and CommandType
4. ConnectionString, RecordSource, CommandType, and Mode
   C. ConnectionString, RecordSource, and CommandType. There are no usable defaults for ConnectionString, RecordSource, or CommandType. (Mode determines read/write permissions, but it is populated with a usable default.)
10. You have two forms in your project. On Form 1 you have two data controls, on Form 2 you have one more, and you have three data environments. You add a text box to Form 1, and select the list for the DataSource property. How many items are available from the list?
1. None
2. One
3. Two
4. Five
   D. Five. Data controls on one form are not directly accessible from other forms. This is one advantage of data environments, which are available across all forms in a project.
11. For most controls (TextBox, CheckBox, and so on) what properties at a minimum must be set to bind a field?
1. DataSource
2. DataSource and DataField
3. DataSource, DataField, and DataMember
4. DataSource, DataField, DataMember, and DataFormat
   B. DataSource and DataField. DataMember is required when using DECommands, but not when using the ADO Data Control. Setting DataFormat is optional.
12. When can you bind a DataControl to data sources?
1. Runtime only
2. Design time only
3. Runtime or Design time
   C. Runtime or Design time. The ability to bind a control to a data source at runtime is a new feature in VB version 6. The RemoteData control (which used the RDO object model) and the Data control (which used DAO) available in previous versions of VB did not support this functionality.
13. In general, which control property contains the value that is bound to the field?
1. The Bound property
2. The Data property
3. The Value property
4. The default property for the control
   D. The default property for the control. Although the property that binds varies from control to control, it is usually the default property for that control (that is, the property that is used when you refer to the control without qualifying it with a property). The Value property binds for checkboxes, but not for most other controls. There is no Bound or Data property, though there is a Data grouping of properties for bound controls.
14. Which of the following data-bound controls can never update content to the database?
1. Chart
2. DataGrid
3. DataList
4. Label
5. MSHFlexGrid
6. TextBox
   A and E. Chart and MSHFlexGrid. Although the Label control cannot be directly edited by the end user, if changes are made to the control programmatically, these changes can be accepted into the database.
15. You have two combo boxes on a form—one ComboBox and one DataCombo. Of the options below, which properties are available for the DataCombo that are not available for the ComboBox?
1. DataField and BoundColumn
2. DataSource and DataField
3. DataSource and RowSource
4. RowSource and BoundColumn
   D. RowSource and BoundColumn. RowSource contains the data source where to find the picklist values, while BoundColumn defines the field referenced. Both DataSource and DataField are available for the ComboBox control.
16. Which of the following properties can be set for a single column in a DataGrid?
1. AllowRowSizing
2. Caption
3. ColumnHeaders
4. DataField
5. Format Type
   B, D, and E. Caption, DataField, and FormatType. AllowRowSizing is set at the split level, and ColumnHeaders is set at the grid level.
17. Select the best definition of what functionality the DataRepeater allows.
1. It allows itself to be presented several times within the space defined by another control.
2. It allows multiple instances of another control to be displayed within the space defined by the DataRepeater control.
3. It allows the user to repeat the content available in a data source across a wide variety of different controls.
4. It allows the user to visually sort, format and combine summary and detail database content.
   B. Multiple iterations of another control. Answer D describes the MSHFlexGrid control; the other options refer to no existing control.
18. What objects are available from within the Data Environment Designer?
1. DataControl
2. DataEnvironment
3. DataView
4. DECommand
5. DEConnection
6. DEControl
   B, D, and E. DataEnvironment, DECommand, and DEConnection. A single DataEnvironment object exists in every Data Environment Designer. A DEConnection is created under a DataEnvironemnt, and a DECommand is created under a DEConnection.
19. Which of the following are not access permissions available from the Advanced tab of the Data Links Property dialog box?
1. Exclusive
2. None
3. Read
4. ReadWrite
5. Share Deny None
6. Share Deny Read
7. Share Deny ReadWrite
8. Share Deny Write
9. Share Exclusive
10. Write
    A, B, G. Exclusive, None, and Share Deny ReadWrite. Most, but not all, of the available options are available as shared or nonshared. The default is Share Deny None.
20. Which of the following are tabs that exist under the Command Properties dialog box?
1. Aggregates
2. DesignPassword
3. General
4. Parameters
5. Public
6. Relation
   A, C, D, and F. Aggregates, General, Parameters, and Relation. Public is a property of the DataEnvironment, while DesignPassword is a property of the DEConnection.
21. Which of the following have a text box as their default field type mapping in the Data Environment Designer?
1. Binary
2. Boolean
3. Caption
4. Integer
5. Variant
   A, D, and E. Binary, Integer, and Variant. Boolean defaults to a checkbox, while a Caption defaults to a Label control.
22. Which of the following are groupings offered in the Data Environment Designer?
1. Arrange by Commands
2. Arrange by Connections
3. Arrange by Data Environments
4. Arrange by Objects
   B and D. Arrange by Connections and Arrange by Objects. The relationship between DECommands and DEConnections is hierarchical, but not permanent. You can assign an existing command to a new Connection from the General tab of the Property dialog box.
23. Drag-and-drop is supported from the Data Environment Designer to which of the following?
1. Data Report
2. Data View Window
3. Form
4. Visual Data Manager
   A and C. Data Report and Form. You can’t drag from the Data Environment Designer to the Data View window, but you can drag the other way around.
24. Which of the following actions can you perform from the Database Designer accessible through Data View?
1. Edit the structure of an Access database
2. Edit the structure of an SQL Server database
3. Edit the data in an Access database
4. Edit the data in an SQL Server database
   B. Edit the structure of an SQL Server database. It is not possible to create database diagrams for Access databases, so it is not possible to view their structure. You can not view the content in a table using the database designer (though the Visual Data Manager supports this).
25. What option do you choose from Query Builder to define which fields should link between two tables?
1. The Foreign Key button
2. The Relate Tables button
3. The Set Table Joins button
4. The Tables button
   C. The Set Table Joins button. This creates the equivalent of an SQL Join statement.
26. How do you get the SQL created by the Query Builder into an application?
1. Use the Copy button to bring the SQL into the clipboard, and then paste the SQL in the appropriate location in your application
2. Use the Create button to generate a form and all the needed bound controls to bind to the SQL statement.
3. Use the Export button to automatically create a DataControl that uses the query as its record source.
4. Use the SQL button save the content to the SQL Designer
   A. Use the Copy button. The Visual Data Manager is relatively loosely integrated with VB, and so you need to use the clipboard to use the content you’ve created.
27. Which DEConnection property determines if the record location is maintained on the server or on the user’s machine?
1. CurrentLocation
2. CursorLocation
3. IndexLocation
4. PositionLocation
   B. CursorLocation. The properties PositionLocation, CurrentLocation, and IndexLocation do not exist.
28. What setting for the DECommand property LockType will automatically lock the row as soon as the user attempts to edit it?
1. Optimistic
2. Pessimistic
3. Safe
4. Unprotected
   B. Pessimistic. Safe and Unprotected are not valid property values, while Optimistic doesn’t attempt to lock the row until the record is ready to be saved.
29. Which of the following record set types could reflect changes made by other users, assuming other users have created no new records since the record set was created?
1. Dynamic
2. ForwardOnly
3. Keyset
4. Static
   A and C. Dynamic and Keyset. Both Static and ForwardOnly are read-only. Note that a Keyset record set would not view records added after the record set was created, but a dynamic record set would.
30. Which of the following record set types usually provides the fastest performance?
1. Dynamic
2. Forward-only
3. Keyset
4. Static
   B. Forward-only. However, the forward-only record set type also provides the most limited functionality.
31. Which of the following terms refers to a DSN that is enabled for the users on a machine, and for all system services on that machine?
1. Global DSN
2. System DSN
3. Universal DSN
4. User DSN
   B. System DSN. Global and Universal DSNs do not exist, and system services cannot use a User DSN.
32. Where can you configure VB to add the command Data Form Wizard… to the VB menu?
1. Add-in Manager
2. Components
3. Options
4. Properties
5. References
   A. Add-in Manager. Add-in Manager allows the user to add components to the development environment, regardless of what project is currently loaded. References, Components, and Properties are all project-specific, while Options in general refers to the cosmetic appearance of the development environment.
33. Which of the following best describes what a Wizard Profile (.RWP) allows you to do?
1. Add new form layouts to the Data Form Wizard.
2. Create many forms with similar appearance and function.
3. Export all the properties of a form to a text file, to enable reuse in other Interdev components.
4. Re-edit an existing form with the Data Form Wizard.
   B. Create many forms with similar appearance and function. The Data Form Wizard cannot be used to edit previously existing forms, even if they were created using the Data Form Wizard.
34. Which of the following Form Layouts does the form wizard offer? (Choose all that apply.)
1. DataRepeater
2. Grid (Datasheet)
3. Master/Detail
4. Detail/Master
5. MS Chart
6. MS HFlexGrid
7. Pivot Table
8. Single Record
   B, C, E, F, and H. Grid (Datasheet), Master/Detail, MS Chart, MS HFlexGrid, and Single Record. These five layouts allow the user to select among the controls used to present the data, and in the case of Master/Detail, the relationship among multiple tables or queries displayed. (DataRepeater and Detail/Master are not options, and the Pivot Table is associated with Excel, not VB).
35. Which is not an advantage to using the ADO Data Control instead of the Data Environment Manager?
1. A navigation device is built into the Data Control.
2. Developing with the ADO Data Control is very similar to developing with the DAO and RDO Data Controls from previous versions of VB.
3. It is easy to use the same data control on multiple forms.
4. You need to set fewer properties with the ADO DataControl than you would with the Data Environment Manager.
   C. Using the same data control on multiple forms. The biggest advantage of the Data Environment Manager is its use in distributing your data connections across your applications.
36. The ADO Data Control contains most of the properties also associated with what other two objects?
1. Data and ADODC
2. DataField and DataSource
3. DataView and Data Environment
4. DEConnection and DECommand
   D. DEConnection and DECommand. Most of the functionality available by using the ADO Data Control is also available by using the DEConnection and DECommand from the Data Environment window.
37. Without writing code, which of the following tasks can be performed by clicking the buttons on the ADO Data Connection control?
1. Create a new record
2. Delete the current record
3. Move to the first or last record
4. Move to the previous or next record
   A, C, and D. Create a new record, move to first or last, move to previous or next. Though the four buttons on the ADO Data Connection Control are most frequently used to navigate among existing content, the EOFAction property can be set to 2 - adDoAddNew, which allows the user to create a new record simply by scrolling to the end.
38. Which of the following Data Form Controls can be bound to a DataField in the Detail Section?
1. RptFunction
2. RptImage
3. RptLabel
4. RptPicture
5. RptTextBox
   E. RptTextBox. Only the RptTextBox can be bound in the Detail section, though the RptFunction control can be bound in the report footer. There is no RptPicture control.
39. Which of the following can be created with the Data Report Designer?
1. A field that displays an image from a database
2. A graph that links to the data
3. A summary field that sums up the contents of a single field
4. A summary field that sums up the contents of two fields
   C. A summary field that sums up the contents of a single field. It is not possible to use the Data Report designer to support graphs, pictures, or complex formulas. (One option you have for formulas, though, is to perform the calculations in the query, and then use the Data Report Designer to present the simple results.)
40. Into which areas on a Data Form can you legally drag fields from a Data Environment Designer?
1. Page Header—gray bar
2. Page Header—body
3. Detail—gray bar
4. Detail—body
5. Report footer —gray bar
6. Report footer—body
   C. Detail—body. You can only drag a field into the detail body. You can, however, drag an entire table into the gray bar above the Detail section (this will automatically create controls for all the fields contained.)