Read an Excerpt
Chapter 14: ADO at Work
After you've successfully opened a connection, your next step will probably be to read some records from the data source. You can accomplish this in several ways, but all of them involve the creation of a Recordset object.
Opening a Recordset Object
Before you open a Recordset, you must decide which records you want to retrieve, which type of cursor you want to create (if any), the cursor's location, and so on.
The source string
The most important property of a Recordset object is its Source property, which indicates which records should be retrieved. This property can be the name of a database table or view, the name of a stored procedure, or the text of a SELECT command. When you're working with file-based Recordsets, the Source property can also be the name and path of a file. (File-based Recordsets are described later in this chapter.) Here are a few examples:
' Select a different source, based on an array of option buttons. Dim rs As New ADODB.Recordset If optSource(0).Value Then Database table ElseIf optSource(1).Value Then Stored procedure ElseIf optSource(2) Then SOL query 'A*'" End If
When you open a Recordset, you must specify the connection that you want used. You can do this in at least four ways:
- You create a stand-alone Connection object with all the properties you want (connection timeout, user name and password, and so on), you open it, and then you assign it to the Recordset's ActiveConnection property before opening the Recordset.
- You create a stand-alone Connection object as described in the previous point and pass it as the second argument of the Recordset's Open method. The effects of this sequence are identical, but it lets you save one statement.
- You pass a connection string as the second argument of the Recordset's
- You create a stand-alone Connection object as shown in the first two I'll describe a few other ways to open a Recordset, based on the Command object, in the "Using Command Objects" section, later in this chapter. Here are some code examples, all of which open the Authors table of the Pubs database on the SQL Server named P2:
' Method 1: explicit Connection assigned to the ActiveConnection property. Dim cn As New ADODB.Connection, rs As New ADODB.Recordset cn.ConnectionTimeout = 5 cn.Open "Provider=sqloledb;Data Source=P2,Initial Catalog=pubs;", "sa" Set rs.ActiveConnection = cn rs.Open "Authors" ' Method 2: explicit Connection passed to the Open method. Dim cn As New ADODB.Connection, rs As New ADODB.Recordset cn.ConnectionTimeout = 5 cn.Open "Provider=sqloledb;Data Source=P2;Initial Catalog=pubs:", "sa" rs.Open "Authors", cn ' Method 3: implicit Connection created in the Recordset's Open method. ' Note that you need to embed additional connection attributes (such as ' connection timeout and user ID) in the connection string. Dim rs As New ADODB.Recordset rs.Open "Authors", "Provider=sqloledb;Data Source=P2:" _ ' Method 4: the Execute method of the Connection object. By default, it ' opens a server-side forward-only, read-only Recordset with CacheSize=1. Dim cn As New ADODB.Connection, rs As New ADODB.Recordset cn.Open "Provider=sqloledb-,Data Source=P2:Initial Catalog=pubs;", "sa" Set rs = cn.Execute("Authors")
Notice a substantial difference among all these approaches: The first, the second, and the fourth methods let you easily share the same connection among multiple Recordsets, whereas if you open multiple Recordsets using the third method each Recordset would use a different connection even if you use the same connection string for all of them.
TIP If you have used a connection string to open a Recordset and then you want to reuse the same implicit Connection object to open another Recordset, you can exploit the ActiveConnection property of the first Recordset, as follows:
' Open a new Recordset on the same connection as "rs". Dim rs2 As New ADODB.Recordset rs2.Open "Publishers", rs.ActiveConnection
You can pass many types of strings to the Open method or the Source property and let ADO determine what they represent. This has a price, however, because you force ADO to send one or more queries to the database just to find out whether the source string is the name of a table, a view, a stored procedure, or the text of an SQL command. You can avoid these additional trips to the server by assigning a correct value to the last argument of the Open method, as in the following examples:
' Select a different source, based on an array of option buttons. If optSource(0).Value Then ' Database table Else optSource(1).Value Then ' Stored procedure ElseIf optSource(2) Then ' SOL query End IfCursors and concurrency
Recordsets can greatly differ in functionality and performance. For example, a Recordset can be updatable or read-only; it can support only the MoveNext command or be fully scrollable. Another key difference is in whether the Recordset contains the actual data or is just a collection of bookmarks that are used to retrieve the data from the database when necessary. It goes without saying that a client-side Recordset based on bookmarks takes fewer resources in the client application but might generate more network traffic when new data needs to be retrieved. Incidentally, this makes it almost impossible to compare the performance of different data retrieval techniques because they depend on too many factors.
The kinds of operations supported by a Recordset heavily depend upon the cursor on which the Recordset is based. Cursors are a collection of records that can be stored and maintained by the server database or by the client application. As you know from Chapter 13, ADO supports four types of cursors: forward-only read-only, static, keyset, and dynamic.
Cursors aren't very popular among professional programmers because of their appetite for resources and CPU time. Moreover, cursors often use locks on the database, which further reduces their scalability. Most heavy-duty client/server applications rely on cursorless Recordsets for retrieving data, and then update and insert records using SQL commands or, even better, stored procedures.
So, what are cursors good for? For one, when you're retrieving small sets of data - some hundreds of records, for example - a cursor is a reasonable choice. Cursors are also necessary when you want to enable your users to browse data and scroll back and forth through it, and you must use a cursor when your user interface is based on bound controls. In some cases, you're more or less forced to use cursors (in particular, client-side cursors) because a few interesting ADO features are available only with them. For example, persistent file-based Recordsets and hierarchical Recordsets can be based only on client-side static cursors, and you can use the Sort method only on this type of Recordset....