Creating a Recordset from a Command Object

Suppose you need to create a recordset that is based on a parameter query. Most often, you won't know what values to supply until you get to that point in your code. The problem is, of course, how to supply those values?

The answer is to base your recordset on a Command object, which itself is based on the Parameter query. How does this solve your dilemma? The following is a typical example.

Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset

'Build the Command object With cmd

.ActiveConnection = CurrentProject.Connection .CommandText = "qryPrices" .CommandType = adCmdTable .Parameters.Refresh

.Parameters("City") = strCityVariable .Parameters("ProductIDM) = lngProductID End With

'Create the Recordset Set rs = cmd.Execute MsgBox rs!UnitPrice rs.Close

Set rs = Nothing Set cmd = Nothing

Since the Recordset object does not have a Parameters collection, you can see that the Command object is what executes the query and passes its dataset to the Recordset when the Recordset is created. The Command object does have a Parameters collection, so you can supply the query's parameters to it.

If you wanted to pass parameters to a stored procedure in an ADP, you would need to make the following changes:

'Build the Command object With cmd

.ActiveConnection = CurrentProject.Connection .CommandText = "spPrices" .CommandType = adCmdStoredProc .Parameters.Refresh

.Parameters("@City") = strCityVariable .Parameters("@ProductID") = lngProductID End With

0 0

Responses

  • liberio
    What is Command Object in access vba?
    7 years ago

Post a comment