Visual Basic Concepts

Setting Database-Related Properties of the ADO Data Control

See Also

When creating a connection, you can use one of three sources: a Connection String, an OLE DB file (.UDL), or an ODBC Data Source Name (DSN). When using a DSN, it's likely you will not have to alter any of the other properties of the control.

However, if you are familiar with database technology, you can alter some of the other properties exposed on the ADO Data Control. The following list describes database-related properties of the control. The list also suggests a logical order for setting the properties.

Note   Database technology is complex, and the suggestions below are not meant to be taken as rules.

1. ConnectionString — The ConnectionString 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, provider, default database, server, username, and password.
   
   
2. UserName — The name of the user, necessary if the database is password-protected. Like the Provider property, this property can be specified in the ConnectionString. If you provide both a ConnectionString and a UserName, the ConnnectionString value will override the UserName property's value.
   
   
3. Password — Also needed when accessing a protected database. Like Provider and UserName, this property's value will be overridden if the value is specified in the ConnectionString.
   
   
4. RecordSource — This property generally contains a statement that determines what will be retrieved from the database.
   
   
5. CommandType — The CommandType property instructs the data provider if the Source is a SQL statement, a table name, a stored procedure, or an unknown type.
   
   
6. CursorLocation — This property specifies where the cursor is located, on the client or on the server. The consequences of this decision affect the next few properties you set.
   
   
7. CursorType — The CursorType property determines if the recordset is static, dynamic, or a keyset cursor type.
   
   
8. LockType — The LockType determines how the data is locked when others attempt to change data you are editing. How you set the LockType is a complex decision, dependent on many factors.
   
   
9. Mode — The Mode property determines what you intend to do with the recordset. For example, you can achieve some performance gains by setting it to read-only, if you are only interested in creating reports.
   
   
10. MaxRecords — This property determines 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. Consequently the MaxRecords property should be no larger than necessary.
    
    
11. ConnectionTimeout — Set the ConnectionTimeout to the number of seconds to wait while establishing the connection. An error is returned if the connection times out.
    
    
12. CacheSize — The CacheSize property specifies how many records can be retrieved from the cursor. If you've set the CursorLocation to client side, then this property can be set to a smaller number (as small as 1) with no adverse effects. If it's on the server side, you should optimize this figure to suit the number of rows you want to view at any one time. For example, if you use the DataGrid control to view 30 rows, set the CacheSize to 60, allowing you to scroll without retrieving more data.
    
    
13. 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).