Visual Basic Concepts
The Remote Data control implements data access by using the Microsoft Remote Data Objects. This technology gives you seamless access to many standard database formats and allows you to create data-aware applications without writing any code. The Remote Data control is suited to larger client-server databases, including Open Database Connectivity (ODBC) databases such as Microsoft SQL Server and Oracle.
It should be noted that the RemoteData control does not work with Image data types.
Note Both the Remote Data control and Data control are included with Visual Basic for backward compatibility. However, because of the flexibility of ActiveX Data Objects (ADO), it's recommended that you create new database applications using the ADO Data control. For more details, see "Using the ADO Data Control."
The Data control, Remote Data control, and the ADO Data control are all conceptually similar: all three are "data controls" that connect a data source to a data-bound control. All three also share the same appearance — a set of four buttons that allow the user to go immediately to the beginning of the recordset, end of the recordset, or scroll backwards and forwards through the recordset.
Creating a Simple Database Application with the Remote Data Control
The following procedures create a simple database application using the Data control.
To create a simple database application with the Remote Data Control
- Draw a Remote Data control on the form. (The icon's tooltip control is "MSRDC.")
If the Remote Data control is not in the Toolbox, press CTRL+T to display the Components dialog box. In the Components dialog box, click Microsoft RemoteData Control. Click OK to add it to the Toolbox.
- Click the Remote Data control to select it, and press F4 to display the Properties window.
- In the Properties window, set the DataSourceName property to the DSN of the database you want to connect to.
- On the Properties window, click the SQL property and type in a SQL statement, for example:
SELECT * FROM Products WHERE AuthorID = 72
You should always include a WHERE clause when accessing a table. Failing to do so will lock the entire table, which would be a major hindrance to other users.
- Draw a TextBox control on the form.
- Click the TextBox control to select it, and on the Properties window set the DataSource property to the Remote Data control.
- On the Properties window, set the DataField property to the name of the field in the database you want to view or modify.
- Repeat steps 5, 6, and 7 for each additional field you want to access.
- Press F5 to run the application.
Setting Data-Related Properties of the RemoteData Control
The following data-related properties can be set at design time. The list suggests a logical order for setting the properties:
- Connect — The Connect property is a string that can contain all the settings necessary to make a connection. The parameters passed in the string are driver-dependent. For example, ODBC drivers allow the string to contain driver, database, user name, and password.
- UserName — Identifies a user to a protected database. The user must also supply a valid password that the database management system recognizes. The user name can also be contained in the Connect property value, making it redundant here.
- Password — Along with UserName, the Password allows the user to access protected data. The password can also be contained in the Connect property value, making it redundant here.
- SQL — The SQL property contains the SQL statement used to retrieve a resultset. The size of the resultset may determine whether you want to use a client-side or server-side cursor. For example, a small resultset can be managed on a client-side cursor.
- RowSetSize — Sets the number of rows returned in a resultset, if the cursor is a keyset. You can fine-tune this number to the computer's resources (memory) for performance gains.
- ReadOnly — Specifies if the data will be written to. If writing data is not needed, setting this property to True can give you a performance gain.
- CursorDriver — Determines the location and type of driver. The setting of this property affects how you set other properties. For example, selecting ODBC client-side cursors may give you performance increases as long as the resultset is small.
- LockType — The LockType determines how the data is locked when others attempt to change the data. If you do not expect others to change the data (while you are looking at it) you can set the LockType to rdConcurRowVer — "optimistic" — leaving the data free to be viewed and changed by others. If you set it to rdConcurLock — "pessimistic" — others cannot access the data while you are accessing it.
- BOFAction, EOFAction — These two properties determine what will happen when the control is at the beginning and end of the cursor. Choices include staying at the beginning or end, moving to the first or last record, or adding a new record (at the end only).
- ResultSetType — Determines if the cursor is static or a keyset type.
- KeySetSize — If the cursor is a keyset, you can optimize the size of the returned resultset with the KeySetSize property.
- LoginTimeout — Sets the number of seconds to wait until an error is returned.
- MaxRows — Specifies how large the cursor will be. How you determine this depends on the size of the records you are retrieving and the resources available on your computer (memory). A large record (one with many columns and large strings) would take more resources than a smaller record. The MaxRows property should be consequently decreased.
- Options — Specifies whether or not the control executes queries asynchronously. Use asynchronous operation when you expect a query to take more than a few minutes to execute.
- Prompt — When RDO opens a connection based on the parameters of the RemoteData control, the Connect property is expected to contain sufficient information to establish the connection. If information like the data source name, user name, or password is not provided, the ODBC driver manager exposes one or more dialog boxes to gather this information from the user. If you do not want these dialog boxes to appear, set the Prompt property accordingly to disable this feature.
- QueryTimeout — Sets the number of seconds to wait for a query to complete before returning an error.
- BatchSize — This property determines how many statements can be sent in a batch--if batch statements are allowed by the driver.