ADO Tutorial

ADO Tutorial

  1. Establish a data source connection.

  2. Define and execute a query.

In ADO, the Recordset object is the main interface to data. An example of the minimal Microsoft® Visual Basic® code to generate a Recordset from an ODBC data source is as follows:

set rstMain = CreateObject("ADODB.Recordset")
rstMain.Open "SELECT * FROM authors", _
"DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"

This generates a forward-only, read-only Recordset object.

A slightly more functional Recordset can be generated as follows:

set rstMain = CreateObject("ADODB.Recordset")
rstMain.Open "SELECT * FROM authors", _
"DATABASE=pubs;UID=sa;PWD=;DSN=Publishers",
adOpenKeyset, adLockBatchOptimistic

This creates a fully scrollable and batch-updatable Recordset.

The query string (the first argument to the Open method) must conform to the query dialect of the server. In the example, the server is Microsoft SQLServer 6.5, which uses an ANSI-standard SQL dialect known as ANSI92.

You can improve query performance by first defining a separate Command object, and setting it’s CommandText property with the query string, instead of passing the query as an agument to the Open method on the Recordset. This results in a compiled query, which will improve your application’s performance if your code needs to re-execute the query two or more times.

Do the code here…

Besides using an SQL statement as the query, ADO will let you pass a stored-procedure if the server will recognize it. The previous example could have been written like this:

set rstMain = CreateObject("ADODB.Recordset")
rstMain.Open "{exec sp_getauthors}", _
"DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"

In fact, executing a stored procedure on the server is preferred, in most client-server applications, because it further insulates the live data from user queries.

  1. Update the data returned from the query.

Establish a data source connection

Define and Execute a query

Update the Data Returned from the Query


© 1997 Microsoft Corporation. All rights reserved. Terms of Use.