| If you prefer to use ADO instead of DAO (or if you're not using a Jet database so that DAO isn't available), you'll need to adjust your VBA code to make use of ADO objects. The examples in this section use ADO's Command object which, along with the Connection and the Recordset objects, is one of the three fundamental objects in the ADO model. You generally use the Command object when your code will execute an action query more broadly, a stored procedure. NOTE ADO tends to follow SQL Server terminology in this area, and SQL Server uses the term stored procedure instead of action query. The two terms are not synonymous, however: SQL that Access would term action queries constitute part but not all of stored procedures.
Using ADO to Execute Delete Queries The following VBA code, used in conjunction with ADO, looks a little different from that in use with DAO. Here's one way that you might use VBA and ADO to delete records with a date field's value that's earlier than 1/1/1997. Sub DeleteOldOrders() Dim cmd As New ADODB.Command Dim cnn As New ADODB.Connection Dim prm As ADODB.Parameter
Three ADO object variables are declared: cmd will represent the command to be carried out, cnn will represent the connection to the database, and prm will represent a parameter that accompanies the command. Then the connection is defined by specifying the provider, the data source, and the catalog, and how security is handled. The SSPI specification, short for Security Support Provider Interface, calls for NT user authentication: NT authorizes the user on the basis of his logon. cnn.Open _ ConnectionString:="Provider=SQLOLEDB.1;" & _ "Data Source=(local);" & _ "Initial Catalog=NorthwindCS;Integrated Security=SSPI"
After the connection is opened, it's assigned to the Command object. Set cmd.ActiveConnection = cnn
Then the code provides the specifics of the command. Its CommandText property, a string, either names the existing query or stored procedure that the command is to represent, or contains the SQL that the command will execute. The CommandType property specifies what CommandText contains. In the present example, it is a stored procedure named DeleteOldOrders. If instead the CommandText property included the actual SQL, the CommandType property would specify adCmdText that is, it would indicate that CommandText included command text. With cmd .CommandText = "DeleteOldOrders" .CommandType = adCmdStoredProc
Now a parameter is created. It's given the name WhichDate and a data type of adDate, meaning that a value assigned to it must be a date value. The Direction property might be new to you. In DAO and in Access SQL, parameters are unidirectional. They're input to the query and function as selection criteria. In ADO (and Transact-SQL and SQL Server), parameters can act as they do in DAO, as inputs, but they can also act as outputs, returning information about what happened when the query or other stored procedure executed. In this case, the parameter is of the familiar input type, as specified by the adParamInput value. Set prm = .CreateParameter(Name:="WhichDate", _ Type:=adDate, Direction:=adParamInput)
The parameter is appended to the command, and remains available to the command for as long as the command itself exists. The code gives the parameter a value in this case, 1/1/97 and the command executes, deleting all records according to the requirements of the existing DeleteOldOrders query. .Parameters.Append prm prm.Value = "1/1/97" .Execute End With End Sub
It's not necessary to formally establish and append a parameter to the command object in order to use one (however, see the next section for an example of how doing so can make things more convenient). The prior example of deleting records using a date parameter could be written like this: Sub DeleteOldOrdersWithSQL() Dim cmd As New ADODB.Command Dim cnn As New ADODB.Connection Dim prm As ADODB.Parameter Dim HowMany As Long cnn.Open _ ConnectionString:="Provider=SQLOLEDB.1;" & _ "Data Source=(local);" & _ "Initial Catalog=NorthwindCS;Integrated Security=SSPI" Set cmd.ActiveConnection = cnn With cmd .CommandText = "EXEC DeleteOldOrders '1/1/97'" .CommandType = adCmdText .Execute RecordsAffected:=HowMany End With MsgBox HowMany & " records were deleted." End Sub
This example shows that you don't need to declare or set a parameter to make use of one that already exists in a stored procedure. There are three functional differences between the two procedures given in this section: The command object's CommandText property does not merely name the stored procedure, but provides SQL that names the query and also states that it should be executed. The CommandText property includes the value that VBA will pass to the stored procedure's parameter. The CommandType property states that the command's type is Text that is, the command includes actual SQL syntax.
Another difference is that the second procedure shows the use of the Execute method's RecordsAffected property. It can be useful to know how many records were deleted (or, in the case of an Append or Update action query, how many records were inserted into a table or how many had their values modified). By declaring a variable as a Long integer and using it in the query's Execute method, you can view with a message box, or store in a worksheet cell, the number of records affected. If the number is zero, for example, you might know that something you expected to occur did not. And if you need an audit trail for changes that your code makes to the database, it can be valuable to know, as you move forward, the number of records that were affected by your code. Using ADO to Execute Update Queries Notice the use of the parameter object in the prior VBA code. The parameter used there, prm, is established with the Set statement. Then it is appended to the Parameters collection, which belongs to the Command object (cmd in the sample code). This is useful when you want to run a query several times, each time with a different value for the parameter. "Querying Multiple Tables," in Chapter 5, "Using Microsoft Query," discussed the setup of a database containing information on the maintenance of doors in office buildings. The following case study shows how data might get into that database from Excel. | 