Office
 

Microsoft Access 2010 : DATA ACCESS WITH ADO (part 1) - Using the Execute Method

 
11/21/2011 10:04:24 AM
Storing and retrieving data is the reason databases are employed, and a large proportion of programming usually revolves around manipulating those objects that deal with data: views, stored procedures, and recordsets. Data manipulation is quite easy to do in ADO, especially if you are already familiar with the SQL language.

1. Overview of the ADO Object Model

The ADO Object Model contains six main objects: Connection, Command, Recordset, Record, Field, and Stream. Table 1 provides a short description of each of these objects:

Table 1. The Major ADO Object Types
OBJECTDESCRIPTION
ConnectionProvides the ability to connect to an ADO data source.
CommandProvides the ability to manipulate and execute commands against a given data source.
RecordsetProvides the ability to read, write, and update records for a given data set.
RecordProvides the ability to manipulate a single row of data.
FieldProvides the ability to manipulate a table column.
StreamProvides the ability to read, write, and update data from a stream.

Although we've already explored some examples that use the Connection object to execute actions on a data source, that's really just the beginning of the ADO functionality. These other ADO objects provide much of the rich and powerful functionality of the ADO library.


2. Using the Execute Method

The most common method used in the ADO Object Library is, by far, the Execute method. The Execute method is used to perform actions, usually SQL statements, against the data source. Both the Connection and Command objects expose an Execute method to explicitly execute commands. These methods vary slightly depending on the object, but are essentially the same.

2.1. The Connection.Execute Method

The Connection object's Execute method takes three parameters and returns a Recordset object containing any records the command may have returned. The CommandText argument can be a SQL statement, the name of a table or a stored procedure, or a provider-specific text or command. The RecordsAffected is a ByRef parameter which returns the number of records affected by the operation. The Options argument can be a bitwise combination of any of the CommandTypeEnum and/or ExecuteOptionEnum member values.


To use the Connection.Execute method, simply instantiate a connection and call Execute with the desired SQL statement, as shown in this code:

Function ExecuteFromConnection(strSQL As String) As ADODB.Recordset

' Define Variables
Dim cn As New ADODB.Connection

' Open the connection
cn.ConnectionString = CurrentProject.Connection
cn.CursorLocation = adUseClient
cn.Open

' Execute the command and return the Recordset
Set ExecuteFromConnection = cn.Execute(strSQL)

' Clean up
Set cn = Nothing

End Function

The preceding function returns a Recordset object containing the results from the SQL operation specified in the parameter of this function.
2.2. The Command.Execute Method

The Command.Execute method provides a little more functionality for executing ADO commands. Specifically, the Command.Execute method allows parameters for the SQL statement to be specified. The Command.Execute method takes three parameters and returns a Recordset object, if the command supports returning records. Calling Execute from the Command object is slightly different; the CommandText parameter is not passed because it is a property of the Command object itself. The Command.Execute object takes the RecordsAffected, Parameters, and Options parameters.


A common way to execute a command from the Command object is to simply set the Command object's CommandText, CommandType, and ActiveConnection properties; then call the Execute method without any parameters. The following code illustrates how this can be done.

Function ExecuteFromCommand(strSQL As String) As ADODB.Recordset

' Define Variables
Dim cmd As New ADODB.Command

' Set the required properties
With cmd
.CommandText = strSQL
.CommandType = adCmdUnknown
.ActiveConnection = CurrentProject.AccessConnection
End With

' Execute the command and return the Recordset
Set ExecuteFromCommand = cmd.Execute(strSQL)

' Clean up
Set cmd = Nothing

End Function

The preceding function returns a Recordset object containing the results from the SQL operation specified in the parameter of this function.
2.3. Specifying Command Parameters

Instead of specifying the Command object's parameters in the SQL statement, the Parameter object can be used to set the parameters on the Command object. For example, the following function retrieves the price of a specified item by calling a Select query in the current database and providing the name of the item for the price to retrieve.

Public Function GetPrice(strName As String) As Double

' Define Variables
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset

' Build the Command object
With cmd
' Set the connection
.ActiveConnection = CurrentProject.AccessConnection

' Set other properties
.CommandText = "qryGetPrice"
.CommandType = adCmdTable

' To be able to refer to parameters by name,
' you must refresh the parameters collection
.Parameters.Refresh

' Supply the parameter for the query
.Parameters("[strItemName]") = strName
End With

' Execute the Query and return the price
Set rs = cmd.Execute

' Set the Price
If rs.RecordCount < 1 Then
MsgBox "There was no record for the Item Specified"
GetPrice = 0
Else
GetPrice = rs("Price").Value
End If

' Clean up
Set rs = Nothing
Set cmd = Nothing

End Function



2.4. Creating Parameters Dynamically

It's quite simple to create parameters for a query on-the-fly with ADO code. To create the parameter for a given query, call the CreateParameter method from the Command object. The benefit here is that you can specify a SQL statement in code and create the parameters for that statement when the code is run. Here's an example of creating parameters using the Command object:

Public Function GetPriceByCustomParameter(strName As String) As Double

' Define Variables
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

' Setup the Command object
With cmd
' Set the connection
.ActiveConnection = CurrentProject.AccessConnection

' Set the CommandText
.CommandText = "SELECT [Prices].* FROM [Prices] " & _
"WHERE [Prices].[ItemName]=[strItemName]"
.CommandType = adCmdUnknown

' Create the parameter and set the value
.Parameters.Append cmd.CreateParameter( _
"[strItemName]", adVarChar, adParamInput, 100)
.Parameters("[strItemName]") = strName
End With

' Execute the Query and return the price
Set rs = cmd.Execute

' Set the Price
If rs.RecordCount < 1 Then
MsgBox "There was no record for the Item specified"
GetPriceByCustomParameter = 0
Else
GetPriceByCustomParameter = rs("Price").Value
End If

' Clean up
Set rs = Nothing
Set cmd = Nothing

End Function


To pass parameters to a stored procedure in an ADP, you need to do two things: Specify the CommandType as adCmdStoredProc, and prefix field names with the @ symbol. Based on the code from our last example, specifying the parameters for a stored procedure would be something like this:

'Build the Command object for an ADP Stored Procedure
With cmd
.ActiveConnection = CurrentProject.AccessConnection
.CommandText = "GetPricesProc"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@ItemName") = strName
End With
 
Others
 
- Microsoft Word 2010 : Adding Supplementary Elements - Creating a Bibliography
- Microsoft Word 2010 : Adding Supplementary Elements - Figure Captions & Adding a Table of Figures
- Microsoft Visio 2010 : Tips for Creating Organizational Charts
- Microsoft Visio 2010 : Tips for Creating Timelines
- Microsoft PowerPoint 2010 : Prepare for Delivery - Rehearsing Presentations
- Microsoft PowerPoint 2010 : Prepare for Delivery - Adapting Presentations for Different Audiences
- Microsoft Excel 2010 : Analyzing Worksheet Data - Creating Groups and Outlines, Converting Text to Columns
- Microsoft Excel 2010 : Analyzing Worksheet Data - Charting a PivotTable
- Microsoft Outlook 2010 : Managing a Calendar - Setting Up a Meeting
- Microsoft Outlook 2010 : Managing a Calendar - Adding an Event
- Customizing Microsoft OneNote 2010 : Setting Preferences for Editing and Searching (part 2)
- Customizing Microsoft OneNote 2010 : Setting Preferences for Editing and Searching (part 1)
- Microsoft Project 2010 : Managing Multiple Projects (part 2) - Linking Tasks in Different Projects
- Microsoft Project 2010 : Managing Multiple Projects (part 1) - Creating a Master Project
- Microsoft Project 2010 : Using Sorts and Auto-filters
- Microsoft Access 2010 : Enhancing Reports with VBA - WORKING WITH VBA IN REPORTS
- Microsoft Access 2010 : Enhancing Reports with VBA - INTRODUCTION TO REPORTS
- Microsoft Word 2010 : Creating a Table of Contents
- Microsoft Word 2010 : Creating Footnotes and Endnotes
- Microsoft Visio 2010 : Tips for Creating Block Diagrams
 
 
Most View
 
- Creating Extended Events Sessions in SQL Server 2012 (part 3) - Monitoring for Page Splits with Extended Events
- Oracle Coherence 3.5 : Implementing Domain Objects - Adding support for schema evolution (part 1) - Implementing Evolvable objects
- Microsoft Exchange Server 2013 : Role assignment (part 2) - Creating roles for specific tasks, Specific scopes for role groups
- SQL Server 2012 : Query Plans (part 1)
- Windows 8 : Creating a Windows Network - Choosing a Network and Cabling System (part 3) - Phoneline and Powerline Networking
- Microsoft PowerPoint 2010 : Modifying and Formatting SmartArt Graphics
- SQL Server 2008 : Storage system sizing - Selecting an appropriate storage system
- Business Cases for Lync Server 2013 : Why Unified Communications (part 2)
- Microsoft Excel 2010 : Working with Other Microsoft Office Programs - Pasting Charts into Other Documents
- Getting MySQL for Python
 
 
Top 10
 
- Developing Custom Microsoft Visio 2010 Solutions : Creating SmartShapes with the ShapeSheet (part 6) - Adding Right-Click Actions to the SmartShape
- Developing Custom Microsoft Visio 2010 Solutions : Creating SmartShapes with the ShapeSheet (part 5) - Modifying the Text Block Using the ShapeSheet
- Developing Custom Microsoft Visio 2010 Solutions : Creating SmartShapes with the ShapeSheet (part 4) - Linking Subshape Text to Shape Data Fields
- Developing Custom Microsoft Visio 2010 Solutions : Creating SmartShapes with the ShapeSheet (part 3) - Controlling Grouped Shapes with the ShapeSheet
- Developing Custom Microsoft Visio 2010 Solutions : Creating SmartShapes with the ShapeSheet (part 2) - Creating Smart Geometry in the ShapeSheet
- Developing Custom Microsoft Visio 2010 Solutions : Creating SmartShapes with the ShapeSheet (part 1) - Introducing the ShapeSheet
- Developing Custom Microsoft Visio 2010 Solutions : Introducing the Notes Shape, Using the Developer Ribbon Tab
- Microsoft Excel 2010 : Working with Graphics - Inserting Clip Art, Inserting a Picture from File
- Microsoft Excel 2010 : Working with Graphics - Using Drawing Tools
- Windows Phone 8 : Walking Through the Bookshop Sample Application (part 5) - Overview of the Sample Bookshop WCF Service