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
 
- Microsoft Word 2010 : Creating a Table of Contents (part 2) - Adding a TOC to a Document - Inserting a Customized Table of Contents
- Sharepoint 2013 : Managing and Configuring Profile Synchronization (part 3) - SharePoint Profile Synchronization
- Diagnosing SQL Server 2012 Using Extended Events (part 5) - Extended Events Terminology - Histogram
- Windows Server 2012 : Securing IIS 8 (part 2) - Auditing Web Services , Using SSL Certificates
- Sharepoint 2013 : Organizing and managing information - Browsing through content types
- The Windows 8 Apps (part 2) - Finance, Internet Explorer
- Microsoft PowerPoint 2010 : Preparing a Slide Show - Triggering Animations
- Windows 8 for Business : Virtualization (part 4) - VHD Shell Integration,Remote Desktop and Remote Desktop Host
- Microsoft Lync Server 2013 Edge Server : Edge Server Overview (part 2)
- Microsoft Exchange Server 2010 Requirements : Getting the Right Server Hardware (part 2) - Memory Recommendations, Network Requirements
 
 
Top 10
 
- Microsoft Sharepoint 2013 : Administering Sharepoint with Windows Powershell - Basic PowerShell Usage (part 3) - Controlling Output
- Microsoft Sharepoint 2013 : Administering Sharepoint with Windows Powershell - Basic PowerShell Usage (part 2) - PowerShell Help , PowerShell Variables
- Microsoft Sharepoint 2013 : Administering Sharepoint with Windows Powershell - Basic PowerShell Usage (part 1) - Listing the SharePoint Commands
- Microsoft Sharepoint 2013 : Administering Sharepoint with Windows Powershell - Commands
- Microsoft Sharepoint 2013 : Microsoft SharePoint 2013 Management Shell and Other Hosts
- Microsoft Word 2010 : Using Advanced Text Features - Addressing Envelopes
- Microsoft Word 2010 : Using WordArt (part 2) - Changing the Format of a WordArt Object
- Microsoft Word 2010 : Using WordArt (part 1) - Inserting WordArt Text
- Microsoft Word 2010 : Creating and Formatting Text Boxes (part 3) - Formatting Text in a Text Box, Linking Text Boxes
- Microsoft Word 2010 : Creating and Formatting Text Boxes (part 2) - Resizing a Text Box , Moving a Text Box