programming4us
 
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 OneOnte 2010 Mobile on iPhone
- Microsoft Acecss 2010 : Power Control Techniques (part 1) - Add Fields to a Form, Add Multiple Fields to a Form at the Same Time
- Microsoft Project 2010 : Capturing Project Progress (part 4) - Assessing the Impacts of Updates
- Developing Custom Microsoft Visio 2010 Solutions : Creating SmartShapes with the ShapeSheet (part 1) - Introducing the ShapeSheet
- Office 2013 Revealed (Part 2)
- Microsoft OneNote 2010 : Prioritizing and Categorizing Notes with Tags (part 2) - Search for Tagged Notes, Removing a Tag from a Note
- Microsoft Excel 2010 : Analyzing Worksheet Data - Creating a Drop-Down List & Exporting a Table to a SharePoint List
- Microsoft Outlook 2010 : Store and Access Contact Information (part 3) - Printing Contact Records
- Office Applications - Office 2013 File Formats (Part 1)
- Microsoft Excel 2013 : Saving a Workbook with Different Formats - Save a Workbook with Another Format
 
 
REVIEW
 
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- Sigma 24mm f/1.4 DG HSM Art

- Canon EF11-24mm f/4L USM

- Creative Sound Blaster Roar 2

- Alienware 17 - Dell's Alienware laptops

- Smartwatch : Wellograph

- Xiaomi Redmi 2
 
Popular tags
 
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
 
Top 10
 
- How To Install Windows Server 2012 On VirtualBox
- How To Fix Skype High CPU And Memory Usage In Windows 8
- Add 270 Additional Cleaning Options To CCleaner With CCEnhancer
- FPPT Provides More Than 2000 Free And Attractive PowerPoint Templates
- Setup Free Media Server To Stream Videos To DLNA Compatible TV, Xbox 360 & PS3 (Play Station 3)
- How To Install Android Market & Google Apps On Kindle Fire
- How To Make Ubuntu Look Like Windows 7
- How To Add A New Account in MS Outlook 2013
- Get Android & Mac OS X Style Gadgets For Windows 7 & Windows 8 With XWidget
- How To Activate Microsoft Office 2013
<