Execute, Requery, Clear Methods Example

This example demonstrates the Execute method when run from both a Command object and a Connection object. It also uses the Requery method to retrieve current data in a recordset, and the Clear method to clear the contents of the Errors collection. The ExecuteCommand and PrintOutput procedures are required for this procedure to run.

Sub ExecuteX()

	Dim strSQLChange As String
	Dim strSQLRestore As String
	Dim strCnn As String
	Dim cnn1 As ADODB.Connection
	Dim cmdChange As ADODB.Command
	Dim rstTitles As ADODB.Recordset
	Dim errLoop As ADODB.Error

	' Define two SQL statements to execute as command text.
	strSQLChange = "UPDATE Titles SET Type = " & _
		"'self_help' WHERE Type = 'psychology'"
	strSQLRestore = "UPDATE Titles SET Type = " & _
		"'psychology' WHERE Type = 'self_help'"

	' Open connection.
	strCnn = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"
	Set cnn1 = New ADODB.Connection
	cnn1.Open strCnn

	' Create command object.
	Set cmdChange = New ADODB.Command
	cmdChange.ActiveConnection = cnn1
	cmdChange.CommandText = strSQLChange
	
	' Open titles table.
	Set rstTitles = New ADODB.Recordset
	rstTitles.CursorType = adOpenDynamic
	rstTitles.LockType = adLockPessimistic
	rstTitles.Open "titles", cnn1

	' Print report of original data.
	Debug.Print _
		"Data in Titles table before executing the query"
	PrintOutput rstTitles

	' Clear extraneous errors from the Errors collection.
	cnn1.Errors.Clear

	' Call the ExecuteCommand function to execute cmdChange command.
	ExecuteCommand cmdChange, rstTitles
	
	' Print report of new data.
	Debug.Print _
		"Data in Titles table after executing the query"
	PrintOutput rstTitles

	' Use the connection object's execute method to execute
	' SQL statement to restore data. Trap for errors,
	' checking the Errors collection if necessary.
	On Error GoTo Err_Execute
	cnn1.Execute strSQLRestore
	On Error GoTo 0

	' Retrieve the current data by requerying the recordset.
	rstTitles.Requery

	' Print report of restored data.
	Debug.Print "Data after executing the query " & _
		"to restore the original information"
	PrintOutput rstTitles

	rstTitles.Close
	cnn1.Close
	
	Exit Sub
	
Err_Execute:

	' Notify user of any errors that result from
	' executing the query.
	If Errors.Count > 0 Then
		For Each errLoop In Errors
			MsgBox "Error number: " & errLoop.Number & vbCr & _
				errLoop.Description
		Next errLoop
	End If
	
	Resume Next

End Sub

Sub ExecuteCommand(cmdTemp As ADODB.Command, _
	rstTemp As ADODB.Recordset)

	Dim errLoop As Error
	
	' Run the specified Command object. Trap for errors,
	' checking the Errors collection if necessary.
	On Error GoTo Err_Execute
	cmdTemp.Execute
	On Error GoTo 0

	' Retrieve the current data by requerying the recordset.
	rstTemp.Requery
	
	Exit Sub

Err_Execute:

	' Notify user of any errors that result from
	' executing the query.
	If Errors.Count > 0 Then
		For Each errLoop In Errors
			MsgBox "Error number: " & errLoop.Number & vbCr & _
				errLoop.Description
		Next errLoop
	End If
	
	Resume Next

End Sub

Sub PrintOutput(rstTemp As ADODB.Recordset)

	' Enumerate Recordset.
	Do While Not rstTemp.EOF
		Debug.Print "    " & rstTemp!Title & _
			", " & rstTemp!Type
		rstTemp.MoveNext
	Loop

End Sub

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