The ADO Recordset object is versatile enough that you can perform any needed data
maintenance action by programming its object model.
You can forego a Recordset in your code for many routine data maintenance
activities, however, and instead use the Execute method of a Connection or Command object to
make calls to SQL statements or stored procedures that implement those SQL statements. Here are
the main areas where you could make such substitutions:
-
Deleting records - Instead of calling the Recordset's Delete
method, execute a stored procedure that uses the SQL DELETE statement.
-
Adding records - Instead of using the AddNew and Update methods of
the Recordset, execute a stored procedure that uses the SQL INSERT statement.
-
Updating existing records - Instead of changing fields in a Do...Loop
through the Recordset and then calling the Update method, execute a stored procedure
that uses the SQL UPDATE statement.
Listing 9.12 shows ADO code that uses Recordset manipulation (and therefore cursors)
to add, delete, and modify records.
Listing 9.13 shows ADO code that calls the Execute method of Command or Connection
objects (which are cursorless) to accomplish the same tasks with stored procedures. Listing
9.13 gives the texts of the stored procedure creation statements as they would appear in
SQL Server.
LISTING 9.12
EXAMPLE OF A PROCESS THAT USES A CURSOR
Private Sub cmdInsert_Click()
Set rsPubs = cmdPubs.Execute
RsPubs.Fields("ID").Value = txtTitleID
RsPubs.Fields("Title").Value = txtTitle
RsPubs.Update
End Sub
x
y
LISTING 9.13
EXAMPLES OF THE SAME ACTION OF LISTING 9.12, BUT RAN WITHOUT A CURSOR
Private Sub cmdInsert_Click()
Dim cmdPubs As ADODB.Command
Set cmdPubs = New ADODB.Command
Set cmdPubs.ActiveConnection = connPubs
With cmdPubs
.CommandType = adCmdStoredProc
.CommandText = "Insert_Titles"
'Refresh Parameters collection
.Parameters.Refresh
'and then set properties of each parameter:
'@id parameter
.Parameters("@id").Value = txtTitleID
.Parameters("@id").Direction = adParamInput
'@title parameter
.Parameters("@title").Value = txtTitle
.Parameters("@title").Direction
= adParamInput
'@output parameter
.Parameters("@PubID").Value = txtInsertPubID
.Parameters("@PubID").Direction
= adParamInput
.Execute
End With
lblResults = ""
lstResults.Clear
End Sub
LISTING 9.14
STORED PROCEDURE USED BY THE EXAMPLE OF LISTING 9.13
create procedure insert_titles
@id varchar(6),
@title varchar(80),
@PubID varchar(4)
AS
INSERT titles
(title_id, title, Pub_ID)
VALUES
(@id, @title, @PubID)
GO
There are two advantages to executing a stored procedure instead of manipulating an
ADO Recordset:
-
Better resource management - A Recordset object requires a cursor, and
cursors can represent a considerable set of resources. By calling a stored procedure,
you do not create another cursor and the server does the work.
-
Better management of tier integrity - You can encapsulate standard
business rules in a centralized place (the database) with stored procedures. The stored
procedures can be a "black box" to your application. The stored procedures
can be changed to meet changing business climates.
Of course, the Recordset is more appropriate where you need local control,
or where the application itself must exercise a great deal of intelligence about
the way it processes data.
