Using the ADOData control
The ADOData control uses Microsoft ActiveX Data Objects (ADO) to quickly establish a connection between a data-bound control and a data provider. A data-bound control is any control that has a DataSource property. The data provider can be any data source that conforms to the OLEDB specification. You can also easily create subset data providers using Visual Basic's class modules.
Although you can use ActiveX data objects directly in your application, the ADOData control has the advantages of being a graphical control (with "forward" and "backward" buttons) and an easy-to-use interface that allows you to use minimal code Create a database application.
In the Visual Basic "Toolbox", many controls can be used as data-bound controls, including check boxes, combo boxes, images, labels, list boxes, picture boxes, and text box controls. In addition, Visual Basic also includes several data-binding ActiveX controls, such as DataGrid, DataCombo, Chart, and DataList controls. Users can also create their own data-bound ActiveX controls, or purchase controls from other developers.
Previous versions of Visual Basic provided intrinsic Data controls and RemoteData controls (RDC) for data access. Both controls are still included in Visual Basic to provide backward compatibility. However, because ADO is more adaptable, it is recommended that users use the ADOData control to create new database applications.
More information A complete list of data-bound controls is in "Controls Bound to ADOData Controls." To learn how to use these intrinsic Data controls or the RemoteData control, see "Using Data Controls" or "Using RemoteData Controls." For more information about creating data providers, see "Creating Data Awareness Classes."
Possible usage
Connect to a local or remote database.
Opens a specified database table, or defines a set of records based on a Structured Query Language (SQL) query, stored procedure, or view of a table in the database.
Pass the values of a data field to data-bound controls, where they can be displayed or changed.
Add new records, or update a database based on any changes to the data displayed in the bound control.
To create a client or front-end database application, add the ADOData control to the form, as well as any other required Visual Basic controls. You can place multiple ADOData controls on a form based on your needs. However, please note that this control is a rather "expensive" way to create connections and should be used when the first control has at least two connections and each subsequent control has at least one more connection.
Create a front-end database application with minimal code
By setting a few properties at design time, you can create a database application with minimal code. If you are using an OLEDB data source, the corresponding Microsoft DataLinkName (.MDL) must be created on your machine. See "Creating Northwind's OLEDBDataLink" for a step-by-step example.
To create a simple front-end database application:
1. Place an ADOData control on the form (the tool tip of the icon is ADODC). If the control is not in the "Toolbox", press the CTRL T key to display the "Parts" dialog box. In the Components dialog box, click MicrosoftADODataControl.
2. In the "Toolbox", click to select "ADOData Control". Then press the F4 key to display the Properties window.
3. In the Properties window, click ConnectionString to display the ConnectionString dialog box.
4. If you have created a Microsoft DataLinkfile (.MDL), select "Use OLEDB file" and click "Browse" to locate the file on your computer. If using a DSN, click Use ODBC Data Source Name and select a DSN from the box, or click New to create one. If you want to create a connection string, select Use ConnectionString, click Generate, and then use the Data Link Properties dialog box to create a connection string. After creating the connection string, click OK. The ConnectionString property will be populated with a string similar to the following line:
driver={SQLServer};server=bigsmile;uid=sa;pwd=pwd;database=pubs
5. In the "Properties" window, set the "Record Source" property to a SQL statement. For example: SELECT*FROMTitlesWHEREAuthorID=72 When accessing a table, you should always include a WHERE clause. Failure to do so would lock the entire table, which would be a serious impediment to other users.
6. Place another "Text Box" control on the form to display database information.
7. In its "Properties" window, set the "Data Source" property of Text1 to the name of the ADOData control (ADODC1). This binds the text box and the ADOData control together.
8. In its "Properties" window, click "Data Fields" to get a drop-down list of available fields. Click the name of the field you want to display.
9. Repeat steps 6, 7, and 8 for each other field you wish to access.
10. Press F5 key to run the application. The user can use the four arrow buttons in the ADOData control, allowing the user to reach the beginning of the data, the end of the record, or move from one record to another within the data.
Set ConnectionString, Source, DataSource and DataField in the program. The following code demonstrates how to set these four properties in the program. Note that you need to use the Set statement to set the DataSource property.
PRivateSubForm_Load()
WithADODC1
.ConnectionString=driver={SQLServer};&_
server=bigsmile;uid=sa;pwd=pwd;database=pubs
.RecordSource=Select*FromTitlesWhereAuthorID=7
EndWith
SetText1.DataSource=ADODC1
Text1.DataField=Title
EndSub
ADOData control events
The ADOData control provides several programmable events. The following table describes these events and when they occur, but this table is not a complete list of all conditions for when these events occur. For more complete information, see the individual event reference topics.
For more information If you are trying to use the ADOData control step by step, see "Creating a Simple Database Application Using the DataGrid and the ADO Data Control," "Creating a Simple DataCombo Application," and "Creating a DataGrid Connected to a DataList Control."
Set database-related properties of the ADOData control
When creating a connection, you can use one of three sources: a connection string, an OLEDB file (.MDL), or an ODBC data source name (DSN). When you use DSN, you do not need to change any other properties of the control.
However, if you know more about database technology, you can change some other properties that appear in the ADOData control. The following list describes some of the database-related properties of this control. This list also suggests the logical order in which to set these properties.
Note that database technology is complex and the following suggestions are not meant to be considered rules.
1.ConnectionString—The ConnectionString property is a string that can contain all the setting values required to make a connection. The parameters passed in this string are driver-specific. For example, the ODBC driver allows the string to contain the driver, provider, default database, server, user name, password, etc.
2.UserName—The name of the user. When the database is password protected, this attribute needs to be specified. Similar to the Provider property, this property can be specified in the ConnectionString. If both a ConnectionString property and a UserName property are provided, the value in ConnectionString will override the value of the UserName property.
3.PassWord—also required when accessing a protected database. Similar to the Provider attribute and UserName attribute, if a password is specified in the ConnectionString attribute, the value specified in this attribute will be overwritten.
4.RecordSource - This property usually contains a statement used to determine what information to retrieve from the database.
5.CommandType—The CommandType attribute tells the data provider whether the Source attribute is a SQL statement, the name of a table, a stored procedure, or an unknown type.
6.CursorLocation—This attribute specifies the location of the cursor, whether it is located on the client or on the server
superior. This decision will affect your settings for the following properties.
7.CursorType—The CursorType attribute determines whether the record set is a static type, a dynamic type, or a key set cursor type.
8.LockType—The LockType property determines how the data you are editing is locked when someone else tries to change it. How to set this LockType property is a complex decision that depends on multiple factors.
9.Mode—The Mode attribute determines what operations you want to perform with the recordset. For example, if you just want to create a report, you can get performance improvements by setting this property to read-only.
10.MaxRecords—This property determines the size of the cursor. How the value of this property is determined depends on the size of the records being retrieved, and the amount of available resources (memory) on the computer. A large record (including many columns and long strings) costs more resources than a small record. Therefore, the MaxRecords property cannot be too large.
11.ConnectionTimeout—Set the time to wait to establish a connection, in seconds. If the connection times out, an error is returned.
12.CacheSize—The CacheSize attribute specifies how many records can be retrieved from the cursor. If CursorLocation is set to the client, this property can only be set to a small number (perhaps 1) without any adverse effects. If the cursor position is on the server side, you can adjust this number to the number of rows you want to be able to view at one time. For example, if you use the DataGrid control to view 30 rows, you can set the CacheSize to 60 so that you can scroll without retrieving more data.
13.BOFAction, EOFAction—These two properties determine the behavior of the control when it is at the beginning and end of the cursor. Options available include staying at the beginning or end, moving to the first or last record, or adding a new record (only at the end).
Controls bound to the ADOData control
Any control with a DataSource property can be bound to an ADOData control. The following intrinsic controls can be bound to the ADOData control:
CheckBox
ComboBox
Image
Label
ListBox
PictureBox
TextBox
The following data-bound ActiveX controls are also available in all versions of Visual Basic:
DataList
DataCombo
DataGrid
MicrosoftHierarchicalFlexGrid
RichTextBox
MicrosoftChart
DateTimePicker
ImageCombo
MonthView
Finally, users can create their own data-bound ActiveX controls using DataBinding objects.
For more information about creating your own data-bound controls, see "Creating Data-Bound User Controls."
->