You can execute a query with ADO as easily as
you can with DAO. ADO uses a slightly different technique for working with
queries, but provides more functionality than DAO provides. ADO also has a
number of abbreviated syntaxes that allow you to do the same thing as you would
with DAO but with a lot less code.
Executing a Non-Parameterized Stored Query
A non-parameterized stored query is an SQL
statement that has been saved in the database and does not require that
additional variable information be specified in order to execute. The following
listings demonstrate how to execute such a query.
DAO
Sub DAOExecuteQuery()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Open the Recordset
Set rst = db.OpenRecordset("Products Above Average Price", _
dbOpenForwardOnly, dbReadOnly)
' Display the records in the
' debug window
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOExecuteQuery()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Open the recordset
rst.Open "[Products Above Average Price]", _
cnn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
' Display the records in the
' debug window
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
End Sub
The code for executing a non-parameterized,
row-returning query is almost identical. With ADO, if the query name contains
spaces you must use square brackets ([ ]) around the name.
Executing a Parameterized Stored Query
A parameterized stored query is an SQL statement
that has been saved in the database and requires that additional variable
information be specified in order to execute. The code below shows how to
execute such a query.
DAO
Sub DAOExecuteParamQuery()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim fld As DAO.Field
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Get the QueryDef from the
' QueryDefs collection
Set qdf = db.QueryDefs("Sales by Year")
' Specify the parameter values
qdf.Parameters _
("Forms!Sales by Year Dialog!BeginningDate") = #8/1/1997#
qdf.Parameters _
("Forms!Sales by Year Dialog!EndingDate") = #8/31/1997#
' Open the Recordset
Set rst = qdf.OpenRecordset(dbOpenForwardOnly, dbReadOnly)
' Display the records in the
' debug window
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOExecuteParamQuery()
Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Open the catalog
cat.ActiveConnection = cnn
' Get the Command object from the
' Procedure
Set cmd = cat.Procedures("Sales by Year").Command
' Specify the parameter values
cmd.Parameters _
("Forms![Sales by Year Dialog]!BeginningDate") = #8/1/1997#
cmd.Parameters _
("Forms![Sales by Year Dialog]!EndingDate") = #8/31/1997#
' Open the recordset
rst.Open cmd, , adOpenForwardOnly, _
adLockReadOnly, adCmdStoredProc
' Display the records in the
' debug window
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
End Sub
Alternatively, the ADO example could be written
more concisely by specifying the parameter values using the Parameters
parameter with the Command object's Execute method. The following lines of code:
' Specify the parameter values
cmd.Parameters _
("Forms![Sales by Year Dialog]!BeginningDate") = #8/1/1997#
cmd.Parameters _
("Forms![Sales by Year Dialog]!EndingDate") = #8/31/1997#
' Open the recordset
rst.Open cmd, , adOpenForwardOnly, _
adLockReadOnly, adCmdStoredProc
could be replaced by the single line:
' Execute the Command, passing in the
' values for the parameters
Set rst = cmd.Execute(, Array(#8/1/1997#, #8/31/1997#), _
adCmdStoredProc)
In one more variation of the ADO code to execute
a parameterized query, the example could be rewritten to not use any ADOX code.
Sub ADOExecuteParamQuery2()
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Create the command
Set cmd.ActiveConnection = cnn
cmd.CommandText = "[Sales by Year]"
' Execute the Command, passing in the
' values for the parameters
Set rst = cmd.Execute(, Array(#8/1/1997#, #8/31/1997#), _
adCmdStoredProc)
' Display the records in the
' debug window
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
End Sub
Executing Bulk Operations
The ADO Command object's
Execute method can be used for row-returning queries,
as shown in the previous section, as well as for non row-returning queries—also
known as bulk operations. The following code examples demonstrate how to
execute a bulk operation in both DAO and ADO.
DAO
Sub DAOExecuteBulkOpQuery()
Dim db As DAO.Database
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Execute the query
db.Execute "UPDATE Customers SET Country = 'United States' " & _
"WHERE Country = 'USA'"
Debug.Print "Records Affected = " & db.RecordsAffected
' Close the database
db.Close
End Sub
ADO
Sub ADOExecuteBulkOpQuery()
Dim cnn As New ADODB.Connection
Dim iAffected As Integer
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Execute the query
cnn.Execute "UPDATE Customers SET Country = 'United States' " & _
"WHERE Country = 'USA'", iAffected, adExecuteNoRecords
Debug.Print "Records Affected = " & iAffected
' Close the connection
cnn.Close
End Sub
Unlike DAO, which has two methods for executing
SQL statements, OpenRecordset and Execute,
ADO has a single method, Execute, that executes
row-returning as well as bulk operations. In the ADO example, the constant adExecuteNoRecords indicates that the SQL statement is non
row-returning. If this constant is omitted, the ADO code will still execute
successfully, but you will pay a performance penalty. When adExecuteNoRecords
is not specified, ADO will create a Recordset object as
the return value for the Execute method. Creating this
object is unnecessary overhead if the statement does not return records and
should be avoided by specifying adExecuteNoRecords when
you know that the statement is non row-returning.
EmoticonEmoticon