What is ADO? | Beginning Access 2002 VBA (Programmer to Programmer)

ADO is a Microsoft technology designed to permit you to access data wherever it is stored, for example, SQL Server, Oracle, Excel, and Word. ADO may also be used when working with Microsoft technologies on the Internet, for example with .NET languages. Many ADO examples to be found on the Web are often used in the context of Active Server pages, or even ASP.NET and ADO.NET. The future of programming, with Microsoft technologies, not only in database access but also in web-based applications, lies in the realm of ADO and we will see an increased focus with the .NET languages. One of the nice things about ADO is that, unlike many other Microsoft technologies, the object model is fairly small. In the case of ADO you only have five objects to worry about. However while it has fewer objects than the DAO object model it does have more methods and properties that you can use.

In this section we will look at the main objects, that is, those you will use every day, in some detail. Record and Stream while important are really topics for a more detailed discussion than we can give here.

Connection

This is the top of the ADO food chain. Without the Connection object you have nothing. Creating a connection is fairly simple. Access, as usual, provides you with a nice shortcut when creating a connection. For example, the following snippet of code creates a connection used by the current database:

 Set Conn = CurrentProject.Connection

This statement simply sets a reference to the current database connection being used. We then create a recordset based on a SELECT statement to retrieve the required data. The code is a shorthand way to provide all the required connection information and it saves us having to provide the required connection information to Access. The actual connection information required by ADO to create the connection is shown below for information. If you do not use the current project connection you then have to create the connection string manually.

   Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\BegAccessVBA2002\Databases\IceCream21.mdb;     Mode=Share Deny None;     Extended Properties="";     Jet OLEDB:System database=""     Jet OLEDB:Registry Path=SOFTWARE\Microsoft\Office.0\Access\Jet.0;     Jet OLEDB:Database Password="";     Jet OLEDB:Engine Type=5;     Jet OLEDB:Database Locking Mode=0;     Jet OLEDB:Global Partial Bulk Ops=2;     Jet OLEDB:Global Bulk Transactions=1;     Jet OLEDB:New Database Password="";     Jet OLEDB:Create System Database=False;     Jet OLEDB:Encrypt Database=False;     Jet OLEDB:Don't Copy Locale on Compact=False;     Jet OLEDB:Compact Without Replica Repair=False;     Jet OLEDB:SFP=False

As you will see in the following examples, however, not all of the options in the demonstrated connection are required.

If you need to work with data held outside the current database, you must create your own connection string. For example to work with a database stored in C:\Beg Access2002VBA you could create a connection string as follows

 Provider-Microsoft.Jet.OLEDB.4.0; User ID = Admin Data Source = C\Beg Access2002VBA\ADOExample.mdb

Important

A great list of all the connections strings you could possibility require is available at http://www.able-consulting.com/ADO_Conn.htm.

This code can be used to create a connection to our data either in a relational database or other file format, such as a Microsoft Excel Spreadsheet. The connection is used to provide the relevant information required by the data provider. To connect to Microsoft Access, the following connection string can be used:

   "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=C:\Beg Access2002VBA\ADOExample.mdb;" & _     "User Id=admin;" & _     "Password="

When working with Microsoft Access you have a couple of choices when working with connections. You can create your own connection as above or you can use the same connection as that used by Microsoft Access. To use the same connection as the current database you would use the following:

   CurrentProject.Connection

This statement permits you to use the connection used by the database you're currently working in. All the properties of the connection are filled in "on the fly" by Access.

In order to connect to a secured Access database you must specify the location of the System.mdw file. For example:

   .Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=C:\BegAccess2002VBA\ADOExample.mdb;" & _     "Jet OLEDB:System Database=YourSystem.mdw", & _     "Username", "Password"

To connect to an SQL Server 2000 database you would use the following:

   With conn     .Provider = "SQLOLEDB"     .ConnectionString = "User ID=sa; Password='MyPassword" & _     "Data Source=Martin; Initial Catalog = Martinhome"     End With

Important

DO NOT leave the password blank as it is a major security hole in your code. Always set a strong password for this username when working with SQL Server. The SA user is the system administrator user and has full permissions on all objects within SQL Server. In fact better advice is to not use this account at all. It is used here to illustrate this point in the hope that more developers will heed the advice.

Access 2002 provides you with a new connection; the AccessConnection . When working with Microsoft Access both the AccessConnection and the CurrentProject.Connection return different OLE DB strings. The AccessConnection will use the Microsoft Jet OLE DB data provider ( Microsoft.Jet.OLEDB.4.0 ) and the Microsoft Access 10 OLE DB service provider ( Microsoft.Access.OLEDB.10.0 ). The CurrentProject.Connection , on the other hand, uses just the Microsoft.Jet.OLEDB.4.0 provider.

Important

Note that the AccessConnection property does not support Jet and Replication Objects code, it does not support ADO extensions for security (ADOX), and finally it does not support the ADO Seek method.

Connection Properties

Each ADO object has a set of provider-specific properties, which are added to this collection. These include, for example, the Name property, which will return the name of the provider. Using the property for a specific provider means we can meet that individual provider's requirements. The Properties collection applies to a number of ADO objects as shown in the object model previously. The most often seen property will be the connection string, which provides three types of information; the name of the provider to use, security information such as the username and password, and any information specific to the provider being used. The following table shows the more common connection properties you will use when working with Microsoft Access 2002.

Property	Comment
Data Source	The path and file name of the database to be used.
Mode	Instructions on how to open the database. See the table below for what is available here.
Prompt	Can be used to prompt the user for specific information, such as, for example, if a password is required.

The following table outlines the mode options available to you when you use the mode property. The default is shared use of the database.

Mode	Description
adModeUnknown	Permissions not set or cannot be determined.
adModeRead	Read-only permissions on the data.
adModeWrite	Write-only permissions on the data.
adModeReadWrite	Read/Write permissions on the data.
adModeShareExclusive	Stops others opening a connection.
adModeShareDenyWrite	Stops others opening a connection with Write permission.
adModeShareDenyRead	Stops other opening a connection with Read permission.

Errors

The collection contains errors returned by the OLE DB Provider. The Error object has the following properties, which can provide you with information about the error returned.

Property	Description
Description	A description of the current error.
HelpContext	The location of the help file for the associated with an error.
NativeError	The specific provider error number.
Number	The error code.
Source	The name of the object that actually created the error.
SQLState	The SQL state for any given error. For example syntax errors.

There are several errors that can occur when connecting to your data. The most common of these that you will need to trap and deal with are incorrect usernames and passwords, and system and server failure. The following generic code example shows how to trap errors by looping through the ADO Errors collection. This is just like trapping errors using the DAO Errors collection where we can loop through the errors collection dealing with each error as it arises.

Try It Out-Catching a Connection Error

In this example we are going to force a connection error by running a SELECT statement against a non-existent table, to demonstrate the Errors collection within ADO. In this case we will simply print out the error messages to the immediate window. For this example we are going to add some error code to the procedure created to populate the customer form above. We have yet to cover some of the objects you are using but don't worry as we will come to all of them in the following pages.

In the database window:

Open the IceCream.mdb example database and select Forms from the list of objects .
Open the form frmCompanyADOerr in design view (this form is supplied in the IceCream.mdb database file). Opening this form in normal view will trigger the error.
Ensure that the form as a whole is the focus and then select View Properties using the main menu and click on the Event tab on the form property sheet.
Click on the ellipsis button ( ... ) beside the On Open property.

Look at the On Open event procedure shown below. The error code has been highlighted in the example.

This code populates the form using an ADO recordset as opposed to binding the form directly to tblCustomer .

 Private Sub Form_Open(Cancel As Integer)   Dim conn As ADODB.Connection     Dim rs As ADODB.Recordset     Dim adoerr As ADODB.Error     On Error GoTo ADOError     'Reuse the same connection used by Access     Set conn = CurrentProject.Connection     'Create the new recordset     Set rs = New ADODB.Recordset     'Create the recordset basing it on an SQL statement     With rs     Set .ActiveConnection = conn     .Source = "SELECT * FROM tblCompany12"     .LockType = adLockOptimistic     .CursorType = adOpenKeyset     .CursorLocation = adUseServer     .Open     End With     Set Me.Recordset = rs     Exit Sub     'Trap the error by checking if the error collection contains any errors     'Print each error to the debug window     ADOError:     With conn     If .Errors.Count > 0 Then     For Each adoerr In conn.Errors     Debug.Print "The Error is", adoerr.Number, adoerr.Description     Next     End If     End With     Set Me.Recordset = rs     Set rs = Nothing     Set conn = Nothing     End Sub

Open the Immediate Window.
Keep the module open and return to your form.
Change from design view to normal form view. Because we have changed the table name to a non-existent table we generate an error.
Return to the module and examine the Immediate window. The error message below should be displayed.

The Error number is -2147217865. The Microsoft Jet database engine cannot find the input table or query 'tblCompany12'. Make sure it exists and that its name is spelt correctly.

How It Works

For this example we have created a SQL statement that attempts to retrieve records from a non-existent table in the database SELECT * FROM tblCompany12 , which is certain to cause an error. Once an error occurs the procedure passes control to ADOError where the contents of the Errors collection are checked.

   If .Errors.Count > 0 Then

If the count of the errors in the collection is greater than then we have an error and we then use the For loop to iterate through the collection while there are errors to process. We check the errors collection of the connection object:

   For Each adoerr In conn.Errors

With each error found, we print the error number and error description:

   Debug.Print "The Error is", adoerr.Number, adoerr.Description

We then move on to the next error using the Next statement. Once all errors have been processed we exit the For loop

Command

The Command object is used to ask questions of or update your data. Using the Command object we can define the command to execute, indicate if we are using parameters for queries (or stored procedures if working with SQL Server 2000), and inform the command object about the connection we are using. The command object has a parameters collection used to pass parameters to the query. Just like the other objects, the Command object has properties and methods.

Command Properties

Property	Comment
ActiveConnection	The connection string or object used by the connection.
CommandText	An Access query or SQL Server Stored procedure to be executed.
CommandType	The type of command to be executed.
CommandTimeout	Time in seconds to wait before the query execution is stopped .
Name	The name of a command object. This can be referenced as if it were a method on the Command object's ActiveConnection property.
Prepared	Set to True to run the command as a prepared statement. In this case a precompiled copy of your query will be saved by the provider. This can slow down the initial execution of the command but later executions should be faster.
State	Returns details on whether the command object is open or closed.

Let's look at some of these properties in a bit more detail.

ActiveConnection

The ActiveConnection indicates to ADO which connection it is to use for the execution of a query or to return a recordset.

CommandText

The CommandText contains the command to be issued to a specific provider. This can be an SQL string, an Access Query, or a SQL Server 2000 Stored Procedure.

Try It Out-Working with a Command

The following procedure in the Chapter 21 Code module illustrates working with the Command object.

   Sub runcmdobj()     Dim cmd as ADODB.Command     Dim strSQL as String     Set cmd = New ADODB.Command     strSQL =  "SELECT * FROM tblcompany"     'Reuse the current Access connection     Set cmd.ActiveConnection = CurrentProject.Connection     Cmd.CommandText=strSQL     cmd.Execute     Set cmd = Nothing     End Sub

How It Works

In the above example we are simply assigning our SQL string "SELECT * FROM tblcompany " to the variable strSQL . The line Set cmd.ActiveConnection = CurrentProject.Connection defines the connection as the current connection used by the database. We set CommandText to the SQL string and with the final command cmd.Execute , we call the Execute method of the command object to actually run the SQL Statement. Note that the command text can be a SQL string as in the above example, but it could, for example, also be a call to a stored procedure on SQL Server. The Execute method will return a recordset but in the case of this example the recordset is not stored and will be lost when we execute the code. Later when we discuss recordsets we will look in more detail at working with them, as these are probably one of the most common ADO objects you will use. In this example we have also omitted the CommandType property and simply accepted the default of adCmdUnknown . We will be looking at CommandType in the next section. Of course, as usual, things are not always as straightforward and there are many different types of command that can be executed.

CommandType Property

This property simply returns the type of command being executed. The following table shows the common types available to you. It is better from a performance point of view to always tell the server which type of command you are going to run, because specifying the command type leads to a performance gain as the server knows exactly what it is receiving.

Type	Comment
adCmdTable	A table name. In this case an SQL statement, based on the table name, is created by the ADO Command object. When working with the Jet OLE DB provider you can use acCmdTableDirect to bypass this stage.
adCmdStoredProc	A stored procedure name.
adCmdTableDirect	A table name. Must be used if you're using the ADO Seek method. Using the Seek method on any other type of recordset will result in an error.
adCmdUnknown	Unspecified. Performance can suffer as the provider must evaluate the command. If you fail to specify an option adCmdUnknown will be used as the default. This can slow your application down as the OLE DB provider is required to provide information on the type of command to be executed.
adCmdtext	A SQL statement to be executed.
adCmdFile	Used when working with a persisted recordset.
adCmdUnspecified	This is the default value and no information is passed about how to evaluate the command.

When you don't state the CommandType property ADO has a best guess as to the actual type of command you are running. This can impact on performance and slow down execution. However when working with SQL strings many developers believe the performance gain is so small as to be irrelevant. For example we could amend our former example and place a CommandType statement in the procedure:

 Sub runcmdobj()   Dim Cmd as ADODB.Command   Dim strSQL as String   Set Cmd = New ADODB.Command   strSQL =  "SELECT * FROM tblcompany"   Set Cmd.ActiveConnection = CurrentProject.Connection   Cmd.CommandText=strSQL   Cmd.CommandType = adCmdText   Cmd.Execute   Set Cmd = Nothing End Sub

CommandTimeout

If anything goes wrong, you don't want to be sitting about all day waiting for a SQL statement to execute. This property allows you to specify a time in seconds to wait before stopping execution. The default is currently 30 seconds. In the example above we could instruct ADO to wait 40 seconds before terminating execution of the command with the following code:

   Cmd.CommandTimeout = 40

Name

We can give our command a name and then refer to it. This name uniquely identifies a command object. For example, for our example above:

   Cmd.Name ="MartinsCommand"

Prepared

This property returns a Boolean property of True or False which indicates if a complied version of the command should be saved. The default is False . If complied prior to execution the command should executer faster each time it is executed.

   Cmd.Prepared=True

State

The State property returns a value that indicates the current state of the connection object.

   Cmd.State

Command Methods

Method	Comment
Cancel	Stops the execution of a statement.
CreateParameter	Creates a parameter for a query or stored procedure.
Execute	Runs the command query or stored procedure.

As the Cancel method is fairly obvious (it simply cancels the execution of a command (using Cmd.Cancel ), we will concentrate on the other methods.

We will take the next two methods slightly out of order and look at the Execute method first as it will place many of our later examples into context including those used with CreateParameter .

Execute

The Execute method can be used to create recordsets and run SQL commands, for example INSERT and DELETE . We have already used the Execute syntax in the previous example:

 Set Cmd.ActiveConnection = CurrentProject.Connection   Cmd.CommandText=strSQL   Cmd.CommandType = adCmdText   Cmd.Execute   Set Cmd = Nothing

The options for the Execute method are as follows.

RecordsAffected

This does not apply when you are returning records as a recordset. It is designed to inform you of how many records have been affected by an Action Query, such as how many records your statement deleted. If you need to know how many records are returned in a recordset you can use RecordCount .

Parameters

This is optional and uses an array of input parameters.

Options

This provides the type of command in the CommandText property, such as adCmdtext . When executing a command that does not return records, you can use adExecuteNoRecords to let ADO know that you are not concerned with any records returned. adExecuteNoRecords is not supported by the CommandType property, which is why it is used here.

Try It Out-Executing an Update Statement

Enter the following code as a new procedure in the Chapter 21 Code module.

   Public Sub usingexe()     Dim Cmd As ADODB.Command     Dim strSQL As String     Dim recs As Long     Set Cmd = New ADODB.Command     strSQL = "UPDATE tblCompany SET CompanyName = 'The Amethyst Group'" _     & "WHERE CompanyName = 'Amethyst Group'"     Set Cmd.ActiveConnection = CurrentProject.Connection     Cmd.CommandText = strSQL     Cmd.CommandType = adCmdText     'Get the records affected by the statement and tell ADO that     'a recordset is not required.      Cmd.Execute RecordsAffected:=recs, Options:=adExecuteNoRecords      'Print out the result to the debug window     Debug.Print recs & " Updated"     Set Cmd = Nothing     End Sub

Using the immediate window execute the code. From within the module window press Ctrl + G to open the Immediate window. Enter the procedure name " usingexe " into the Immediate Window, and press return to execute the code.
Just to prove it works, open tblcompany and check out the amended name:

Execute is commonly used with SQL strings and the connection objects, particularly with SQL statements that do not return records, such as INSERT , DELETE , and UPDATE . We have already discussed many of the features of Execute , but it's worth noting that when you use Execute , ADO will create a recordset even if one is not required. You now have the ability to improve performance slightly using adExecuteNoRecords .

CreateParameter

In addition to executing simple SQL statements we can also pass parameters using the Command object. In order to work with parameters we can use the CreateParameter method of the Command object. This provides you with a very flexible method when using ADO and SQL, including Microsoft Access Queries and SQL Server 2000 stored procedures. The formal syntax for CreateParameter is:

Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value) Option	Description
Name	An optional value containing the name of the parameter.
Type	An optional DataTypeEnum that provides the data type of the parameter.
Direction	Specifies an Input, Output, or Input/Output parameter.
Size	An options value that specifies the size of the parameter.
Value	The value for the parameter object.

So, to pass a CompanyID to a parameter we could use the following:

   Set param =Cmd.CreateParameter("CompanyID", adInteger, adParamInput,,ID)

Note that we have accepted the default values of Size and Direction neither of which apply to Microsoft Jet. It is also very important to know that your parameter is not automatically added to the Parameters collection. In order to add the parameter to the collection you must append it.

   Cmd.Parameters.Append param

Your parameters must be appended to the parameters collection in the same order that they are defined in your query or SQL Server stored procedure.

In our example, the procedure simply deletes one record from the IceCream Customer table based on the Primary Key value, CompanyID passed in to the procedure. Before getting to the next code example we will need to create an Access query that accepts a single parameter and then deletes the required company from tblcompany .

Try It Out-Deleting a Company Record from tblcompany

Create a Delete Query that accepts a single parameter, CompanyID , and save it as qryDeleteCompany :

In Queries click the New button, and then in the New Query dialog select Design View and then OK .
In the Show Table dialog add tblcompany and then Close.
In the main menu select Query Delete Query ; this will change the query into a Delete Query.
Double-click the * symbol in tblcompany to select all records.
Double-click CompanyID to add it to the query.
In the Criteria cell for CompanyID enter [Enter ID] as the user prompt.
Close and save the query as qryDeleteCompany.
Click Modules.
Click New.

Enter the following code into the ADO module:

   Public Sub DeleteCust(lngID As Long)     Dim cmd As ADODB.Command     Set cmd = New ADODB.Command     With cmd     .ActiveConnection = CurrentProject.Connection     .CommandText = "qryDeleteCompany"     .CommandType = adCmdStoredProc     .Execute , Parameters:=lngID     End With     End Sub

In this case we set the CommandType argument to adCmdStoredProc . That way the server knows what to expect.

In the Immediate window enter DeleteCust(14) and press Enter.

The customer record with a Primary Key value of 14 is then deleted. (Note that if you use a value that does not exist or a company with current related sales records then you will receive an error message.)
Open tblcompany and you will find that the entry with the CompanyID of 14 has been deleted.

This is a simple way to pass a value to the procedure if you have a single value. If you need to pass multiple values you can use the VBA Array function to pass multiple values, for example:
```
 cmd.Execute Parameters:=Array("Joes Dinner", "24 New Road", London") 
```

The order of the parameters must be identical to those stated in the stored procedure. We can also use the more common approach for the Parameters collection of the command object to pass multiple parameters to a procedure.

Try It Out-Using CreateParameter

For this example we will again reuse qryDeleteCompany , and pass the parameter to the procedure as part of the command object using CreateParameter .

Open the Chapter 21 Code module and enter the code below:

   Public Sub DeleteCustParam()     'Using CreateParameter to delete a company record     Dim cmd As ADODB.Command     Dim prm As ADODB.Parameter     Set cmd = New ADODB.Command     With cmd     .ActiveConnection = CurrentProject.Connection     .CommandText = "qryDeleteCompany"     .CommandType = adCmdStoredProc     'Create the parameter     Set prm = cmd.CreateParameter(Name:="MyParam", Type:=adInteger, _     Direction:=adParamInput)     'Append the parameter to the parameters collection     .Parameters.Append prm     prm.Value = 16     .Execute     End With     End Sub

This is a modified version of the previous example and is used to illustrate how you can use the CreateParameter methods.

To execute the procedure enter DeleteCustParam in the Immediate Window and press Return .

How It Works

The main body of the code is similar to the other examples we have seen. The changes are contained within the With construct. The following line creates our parameter object;

 Set prm = cmd.CreateParameter(Name:="MyParam", Type:=adInteger,Direction =AdParamInput)

We provide a name to the parameter, specifying its type as an Integer and direction as adParamInput . The parameter is not automatically added to the parameters collection and we therefore have to add it ourselves using Append . The line cmd.Parameters.Append prm appends our parameter to the collection. We then provide the value for the parameter, prm.value=16 , which is the Primary Key value for the record we wish to delete, and then execute the code using .Execute . The more information we can provide about the parameter the better the code will perform as ADO will not have to query the provider to find out such details. The following parameter direction property settings are available to you when specifying the direction of a parameter.

Direction	Comment
AdParamInput	Default. Input parameter.
AdParamOutput	Output parameter.
AdParamInputOutput	Indicates a two-way parameter.
AdParamReturnValue	A return value.
AdParamUnknown	Direction is not known.

Execute

We can use the Execute method of the Command object to open a query. Let's try a quick example and execute a simple Query in our Access Database.

Important

When running queries that do not return records you can use adExecuteNoRecords . Doing so informs ADO that the command being run doesn't require a recordset to be built, thus improving performance.

Try It Out-Executing a Query

The Query will update those companies in tblcompany whose Country = "England" to Country = "UK" . Of course you will first have to create the Update query.

Create a new query in Design view.
In the Show Table dialog add tblcompany and then close.
In the main menu select Query Update Query , this will change the query into a Update Query.
Create the query shown below:
Close and save the query as qryUpdateCountry .

Open the ADO module and enter the procedure below:

   Sub UsingQuery()     'Running an Update Query using adExecuteNoRecords     'This query changes the country field in tblcustomers to UK     'Where it is currently set to England     Dim conn As ADODB.Connection     Dim recs As Long     'Use the current connection for this database     Set conn = CurrentProject.Connection     'Execute our update query using the execute method of the     'connection object     With conn     .Execute "qryUdateCountry", recs, adExecuteNoRecords     .Close     End With     Set conn = Nothing     'Return a message to the screen telling us how many records got updated.     'Should be 4     MsgBox recs & " record(s) affected."     End Sub

Run the procedure in the Immediate window by typing:
```
 UsingQuery 
```
You will get a dialog box appearing telling you the numbers of records updated.

Recordset

Once you execute a query ADO will construct a Recordset object, which by default is read-only and is used to hold the records returned by your query. It is within the Recordset object that we can add, delete, amend, and search for records returned in response to our query. This is the object you will probably see most often when working with ADO and Access 2002. The syntax to open a recordset is as follows:

Rst.Open Source,Connection,CursorType,LockType,Options Parameter	Description
Source	Where the data is to come from.
Cursor Type	The type of cursor to use to move through the recordset when opened.
LockType	The type of record-locking to use.
Option	Similar values to the CommandText options seen earlier.

Cursor Type

There are several ways in which you can open a recordset, set the cursor location, and allocate a locking strategy. Each of the options is shown in the tables below.

A cursor is a structure within memory that contains your recordset. How you interact with the recordset depends on the type of cursor you open .

Cursor Type	Comment
adOpenForwardOnly	This is the fastest (and the default) of the cursor types but only supports forward movement through the recordset via MoveNext . Changes made to the recordset once it is opened are not available.
adOpenStatic	All fields are returned to the client. Works very like a DAO snapshot recordset type.
adOpenKeyset	Returns the Index key of the recordset only and then the full data set as required. Supports full movement but additions to the recordset are not visible once it is opened. A data buffer of records surrounding the current record pointer is also returned.
adOpenDynamic	Creates fully scrollable recordsets with full navigation. All changes are visible. However, this is not supported by the Jet engine. If you try to open a dynamic recordset using Jet, it will work, but if you check the Cursor Type property of the resulting recordset object, you'll find that it is adOpenKeyset . This is sometimes referred to as " graceful degradation".

It's worth noting that even if the provider you are using does not support the cursor, it will still open one.

So which cursor type do you use? Well it depends on what you want to do but more that that it depends on the performance. The less complex the cursor the less work ADO has to do in the background. For example, in a forward-only cursor because you do not need to move backwards you simply forget the records you have passed as you have no need to have ADO remember them. However, in a cursor that supports full movement, the cursor and the resulting records must be managed for you, in case you need to nip back to another record. In addition to the cursor type, we can also specify the location of the cursor. This will be new to DAO users as you don't have to worry about cursor locations using DAO. When working with Microsoft Access remember server-side cursors will also be created on your machine as Jet always runs on the local machine (that is, the one sitting in front of you now).

Cursor Location	Comment
adUseClient	ADO creates and manages the cursor for you. If you are using disconnected recordsets then this is the option to use.
adUseNone	Provided for backward compatibility and now no longer used.

Locking

The following table shows the locking strategy available to you when editing records via the recordset.

Locking	Comment
adLockReadOnly	You cannot update, add, or delete records. If you leave the parameter out this is the default recordset returned by ADO. It is also the best option if you do not need to change or work with the recordset.
adLockPessimistic	Data can be updated. As soon as you begin to edit a record it is locked and remains so until you either finish the edit or cancel the update
adLockOptimistic	Data is updateable but no locks are used until you actually begin to save the record. As soon as the changes are saved the lock is released.
adLockBatchOptimistic	All updates are cached locally until the UpdateBatch method is used. All updates are then committed to the database at once.

Now we will have a look at creating some recordsets.

Try It Out-Using ADO to Populate a Form in the IceCream.mdb file

In the Ice Cream Database ( IceCream21.mdb ) copy the frmCompany and paste it back into the database window as frmCompanyADO . To copy the form, select the form name and right-click, and in the context menu that appears select Copy from the context menu. To paste the form right-click again and select Paste from the menu. Remember for this example you need to have a reference set to the ADO 2.7 object library.
Enter frmCompanyADO as the new name for the form, and then click OK to save the new form.
Open frmCompanyADO in Design View.
From the main menu select View Properties to open the form's property sheet (or press F4 ). Click the Data tab.
Delete the table name from the Record Source property.
Click on the Event tab and delete the procedure from the On Current property.
Go to the On Open event for the form and click the ellipsis button (...) to open the Choose Builder dialog.
Select Code Builder and click OK.

In the module window enter the ADO code shown below into the code stub in the module:

   Private Sub Form_Open(Cancel As Integer)     Dim conn As ADODB.Connection     Dim rs As ADODB.Recordset     Set conn = CurrentProject.AccessConnection     Set rs = New ADODB.Recordset     With rs     Set .ActiveConnection = conn     .Source = "SELECT * FROM tblCompany"     .LockType = adLockOptimistic     .CursorType = adOpenKeyset     .CursorLocation = adUseServer     .Open     End With     'Set the form's Recordset property to the ADO recordset     Set Me.Recordset = rs     Set rs = Nothing     Set conn = Nothing     End Sub

Close the form accepting all changes.

We have created a form bound directly to the company table. We then removed the record source of the form and replaced it with ADO code, which will provide the record source. Copying the form using Cut and Paste is just a quick way to create the form without having to manually create the objects to hold the field data.

The data is still available on the form but is now provided using an ADO recordset as opposed to being bound directly to the table.

How It Works

The first thing we do is declare our variables ; conn and rs . conn will be used to refer to the connection being used and rs is used to refer to the recordset being manipulated.

 Set conn = CurrentProject.AccessConnection

Here we are setting the connection to the database. In DAO we would have used the following code:

   Dim db as Database and Set db = Currentdb()

Once we have set up the connection and defined the recordset, we then get to the bones of our code and using the With syntax we set the properties of the recordset collection.

With provides you with a short way to manipulate and set the properties of a collection. To refer to a property you simply prefix it with a dot separator as follows:

 With rs     Set .ActiveConnection = conn     .Source = "SELECT * FROM tblCompany"     .LockType = adLockOptimistic     .CursorType = adOpenKeyset     .CursorLocation = adUseServer     .Open   End With

With rs informs ADO which object collection we are going to work with. In this case, it is the recordset collection.

We then s et the connection. W e are going to use the same connection as Access:

 Set ActiveConnection = conn

After this we inform ADO about the SQL statement we are going to use - in this case, we used a SQL string to provide the recordset. We could also have used a table name, a query, or a SQL Server stored procedure.

We next set the locking type of the recordset being returned - the functionality is the same as DAO but the syntax has changed slightly.

DAO Locking	ADO Locking
dbReadOnly	adLockReadOnly
dbPessimistic	adLockPessimistic
dbOptimistic	adLockOptimistic

Then we set the cursor type and cursor location, and then opened the recordset. Just as with DAO we must open the recordset in order to manipulate it. We then stopped the manipulation of the recordset collection using the With statement.

Finally we set the forms record source to the recordset that had been opened:

 Set Me.Recordset = rs

Finally we closed and cleaned up all objects by setting them to Nothing .

Using Access 2002 and ADO we now have a fully updateable recordset bound to our customers form as opposed to a table. Just like most things in the programming world, there are several ways to open and work with recordsets and the following examples show you various ways to proceed.

As well as working with SQL strings as in the above example we can also work with Access Queries. So, in the above example we could replace tblcompany with a query that selects all records. Using an existing Access query, qryExample, we can create a recordset as:

   Dim rs as ADODB.Recordset     Set rs = New ADODB. Recordset     rst.Open "qryExample", CurrentProject.Connection

Notice that on this occasion we are also passing the CurrentProject connection to the recordset. The Open method of the Recordset object will open a connection when passed a valid connection property. However, it's worth noting that a command object is created by ADO in the background to handle this process.

We can also use parameters in our SQL string, for example:

   strSQL = "Select * FROM tblcompany WHERE CompanName = " & txtCompanyName     rs.Open strSQL, conn, Options:=acCmdText

Moving Around in Recordsets

As you have already seen we can use the rs.MoveNext statement to move to the next record in the recordset. In addition to MoveNext we can also MoveFirst , MoveLast , and MovePrevious .

Property	Description
MoveFirst	Moves to the first record in the recordset. Works with all the cursor types.
MoveLast	Moves to the last record in a record. The cursor being used must support both forward and backwards movement.
MoveNext	Moves to the next record in a recordset. If the EOF (End-of-File) property is True and you use this method then you will get an error.
MovePrevious	Moves back one record. If you are at the first record then BOF (Beginning-of-File) will be True and an error will be created.

Inserting Records

We have seen a basic example of retrieving a recordset using ADO, but we can also insert records into our tables. The next example inserts a single record in the company table, tblCompany , using the AddNew method of the recordset.

Unlike DAO when you move to a new row or close the recordset your changes are committed to the database. However it is good practice to always deliberately update the row using the Update method. To discard the changes you must call the CancelUpdate method before moving off the current record.

The syntax is very simple:

   With rs     .AddNew     .Fields("FieldName") = "Value"     .Fields(FieldName") = "Value"     .Update     End With

Try It Out-Adding a New Customer Record

Open the Chapter 21 Code module and add the following code:

   Public Sub addCustomer()     Dim conn As ADODB.Connection     Dim rs As ADODB.Recordset     Set conn = CurrentProject.Connection     Set rs = New ADODB.Recordset     rs.Open "tblCompany", conn, adOpenDynamic, adLockOptimistic, adCmdTable     With rs     .AddNew     .Fields("CompanyName") = "Liams Diner"     .Fields("Address") = "44 Long Road"     .Fields("City") = "New York"     .Update     End With     End Sub

Open the Immediate window and type in a ddCustomer .
Open tblCompany and you will see that the new record has been entered in the table.

An important point to remember is that if you are using AutoNumber Primary Keys then you do not need to add this field to the AddNew statement. The Primary Key will be added by the database itself.

How It Works

In this case we are using the AddNew method of the recordset to insert one record. We are using the With syntax to avoid full references to the recordset each time. The AddNew method will add a row to the table. An important difference between DAO and ADO to remember here is that ADO will save the record when you move off it. However, we are using the Update method to save the record "just in case". Leaving a save record pending and closing a recordset will result in an error.

Another approach is to this is to use a Variant array. In this case ADO actually handles the Insert for you. For example, if you were to use the following code using an array as opposed to using the ADO fields collection to actually pass the values for insertion. The changes to the code are highlighted below.

   Public Sub addCustomerArray()   Dim conn As ADODB.Connection   Dim rs As ADODB.Recordset   Set conn = CurrentProject.Connection   Set rs = New ADODB.Recordset   rs.Open "tblCompany", conn, adOpenDynamic, adLockOptimistic, adCmdTable   varfields = Array("CompanyName", "Address", "City")     varValues = Array("Aines Diner", "26 Long Road", "Belfast")     rs.AddNew varfields, varValues   rs.Update End Sub

Updating Records

The next example uses a SQL string to update the CompanyName field in tblCompany table.

Try It Out-Updating Records using ADO

Add the following code into the Chapter 21 Code module:

   Public Sub ADOUpdate()     Dim rs As ADODB.Recordset     Dim strSQL As String     Set rs = New ADODB.Recordset     strSQL = "SELECT CompanyName, Address, City FROM tblCompany     WHERE (CompanyName = 'Liams Diner')"     rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic     With rs     !CompanyName = "Williams Diner"     .Update     End With     rs.Close     End Sub

Open the Immediate window and type in ADOUpdate to run the code.
Open tblCompany and you will see that the record for Liams Diner's name has been changed from Liams Diner to Williams Diner .

Note that in this case we have added the CurrentProject.Connection to the Open method rather than as a separate code line. ADO will create the connection for us using the recordset's Open method.

Many developers recommend not using recordsets to update records. They prefer to work with queries or stored procedures (if using Access as a front-end to an MSDE database for example). At times you may also want to update more than one record. If that is the case simply enclose the Update statements in a Do statement testing for the EOF or End-of-File. The If statement is also used to actually test the fields' value:

   Do Until rs.EOF     If rs!fieldname = "Field Required" Then     rs!fieldname ='New Value'     rs.Update     End if     rs.MoveNext     Loop

Deleting Records

Deleting a record using ADO is very similar. In this case we need to move to the row targeted for deletion and then use the Delete method of the recordset object to actually remove the record using rs.Delete .

Try It Out-Deleting a Record

Open the Chapter 21 Code module by double-clicking it or open a new module. Enter the following ADO into the module window:

   Public Sub RemoveCompany()     Dim rs As ADODB.Recordset     Dim strSQL As String     Set rs = New ADODB.Recordset     rs.Open "SELECT * FROM tblCompany WHERE CompanyID=13", _     CurrentProject.Connection, adOpenStatic, adLockOptimistic     With rs     'Make sure we have a record by checking the RecordCount property     If .RecordCount > 0 Then     'Delete the record     .Delete     End If     End With     'Close and clean up     rs.Close     Set rs = Nothing     End Sub

Change the CompanyID to that of the company you want to delete.
Run the code from the Immediate window and then open tblCompany to see that the record has been deleted.

Finding Records

ADO supports a couple of ways to search for records, Find and Seek , and of course using SQL. There is much discussion about which is the best way to find records. Personally when working with Access or SQL Server we tend to opt for the Stored Procedure or Query approach as opposed to using Find or Seek . In this section we will provide a quick overview of the three methods.

SQL

Perhaps the best way to actually find records is to use the tried and tested method of the SQL WHERE clause. It has been stated on many developer's lists that working experience has proven this method to be the best in terms of speed and flexibility. You can use a SQL string, or pass criteria to a query or stored procedure to filter the recordset. One other advantage in using queries to filter records is when you come to upsize the database to an Access Data Project your queries will be converted to stored procedures, saving you from having to amend code.

Find

Let's take a look at the Find method. In this example, Find is used to limit the recordset to a single record, that for the customer Candy's Cones. The Find method permits you to use a single field value as the criterion for your recordset.

Try It Out-Using Find

Open the Chapter 21 Code module or open a new module. Enter the following code into the module:

   Public Sub GetCompany()     Dim rs As ADODB.Recordset     Dim strSQL As String     Set rs = New ADODB.Recordset     strSQL = "SELECT tblCompany.CompanyName, tblCompany.Address," _     & "tblSales.Quantity, tblSales.DateOrdered" _     & " FROM tblCompany INNER JOIN tblSales ON tblCompany.CompanyID" _     & " = tblSales.fkCompanyID"     rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adOpenDynamic     With rs     Find "CompanyName='Eyshood Cocoa'"     End With     Debug.Print rs!CompanyName & " " & rs!Quantity & " " & rs!DateOrdered     rs.Close     Set rs = Nothing     End Sub

Run the code from the Immediate window by typing GetCompany and pressing Enter .

The Find method has one obvious drawback; you can only use one field value as the criterion. However you can also add a WHERE clause with the Find method, for example:

   rs.Find("CompanyName LIKE 'A*'")

Note that only one record is returned by the above examples even if there are multiple matches. In order to return all the records, you need to enclose the Find statement in a Do loop. This is unlike a SQL WHERE clause when we can pass multiple values to the WHERE string.

Seek

The Seek method can also be used to find records. Seek is mainly used with records that return tables. It is supported by the Jet engine but not by SQL Server. In order to work with the Seek method you must also use the Index property as your search fields must also have an index set. For example if we wanted to search by CompanyName and City , then we would create an index on tblCompany and include the fields CompanyName and City . We would then base our search on both fields.

Try It Out-Using Seek

For this example we have copied the table Customers from the Northwind example database. This table is included in the chapter database.

Enter the following code into the Chapter 21 Code module or create a new module for this purpose.

   Sub UsingSeek()     'Using seek to find a record     Dim rs As ADODB.Recordset     Dim conn As ADODB.Connection     Dim strSQL As String     Set rs = New ADODB.Recordset     Set conn = CurrentProject.Connection     With rs     .Open Source:="Customers", ActiveConnection:=conn,     CursorType:=adOpenKeyset, LockType:=adLockOptimistic,     Options:=adCmdTableDirect     .Index = "CompanyName"     .Seek ("Antonio Moreno Taquera")     End With     Debug.Print rs!CompanyName & " " & rs!ContactTitle & " " & rs!ContactName     End Sub

Run the code from the Immediate window by typing UsingSeek and pressing Enter .

How It Works

The only new lines at this stage are:

.Index - where we pass the name of the index to ADO
.Seek - passing the value we wish to search for

We then print out the value to the Immediate window. The problem with Seek is that it is only supported by Jet 4 and then support is ropey at best. The advice is to use either the Find method or our own preference, SQL with stored procedures and queries.

Fields

Each recordset comprises a Fields collection, made up of individual field objects. Each field of data in your table will have a corresponding field object. The field object contains a Value property, which is where your data actually resides. The Value property is also used to place any new or amended data before it is passed back to the database.

Record

Can be used to represent one row of data or a file or directory structure.

Stream

A Stream object can be used to manipulate the binary data stream, for example an e-mail or image file, which makes up a file or object.

Disconnected Recordsets

So far we have concentrated on pulling data directly out of a database and at times changing it and reinserting it into the database. The basic idea behind a disconnected recordset is to grab the data, save or persist it locally, manipulate it, and save it back to the database thus reducing the number of connections required to the database. Once we disconnect the recordset from its source we can save it to disk. The basic steps involved are shown below but like everything connected with Microsoft Access there are other ways to do this.

Get a recordset in the usual way.
Close the connection.
Save the recordset locally to file.
Change the recordset in some way.
Reconnect and update the database.

The next example does just that. In this case we have created a simple form based on the company table using the Access form wizard. Once the form is created we simply remove the recordsource ( tblCompany ) using the form's property sheet and enter the following code into the form On_Open event.

Try It Out-Adding a Record via a Disconnected Recordset

Use the form wizard to create a new form based on tblcompany . Select all the fields and click Finish . Save the form as frmCompanyAutoform.
Open the form in design view and then open the Properties window.
On the Data tab delete tblcompany as the form's Record Source.
Click Event and go to the On Open event.
Click the build ( ... ) button and then select Code Builder.

Enter the following code into the code sheet:

   Private Sub Form_Open(Cancel As Integer)     Dim conn As ADODB.Connection     Dim rs As ADODB.Recordset     Set conn = CurrentProject.AccessConnection     Set rs = New ADODB.Recordset     rs.CursorLocation = adUseClient     rs.Open "Select * from tblcompany", conn, _     adOpenForwardOnly, adLockOptimistic     Set rs.ActiveConnection = Nothing     Set Me.Recordset = rs     End Sub

Use the form to change a few records then examine the table.

None of your changes will appear in the table because you are working with a copy of the recordset held in memory. The recordset contains the changes but cannot as yet write the changes back to our database. We now need to add some code to actually perform the update back to the table. Before going into the code we need to have a quick word on working with batches.

When working with the database we do not have to write all changes back to the source as and when they occur. With a disconnected recordset this is possible to achieve but the better way would be to send all the changes to the database in one go. In order to do this we perform a batch update. Without a batch update the recordset can be changed but immediately "forgets" the update has taken place. It simply treats the new value as if it has always been part of the recordset. However when we use batch updates the recordset remembers the changes and as a result can write them back to our database. To turn batch updating on we set the locktype to adLockBatchOptimistic .

We are going to make some changes to the above code to allow us to update the recordset.

The changes required to the procedure above are shown highlighted below.

 Private Sub Form_Open(Cancel As Integer)   Dim conn As ADODB.Connection   Dim rs As ADODB.Recordset   Set conn = CurrentProject.AccessConnection   Set rs = New ADODB.Recordset   rs.CursorLocation = adUseClient   rs.Open "Select * from tblcompany", conn, & _   adOpenForwardOnly, adLockBatchOptimistic   'Kill the connection   Set rs.ActiveConnection = Nothing   'Set the form record source to our disconnected recordset   Set Me.Recordset = rs   'Reopen the connection to the database     Set conn = CurrentProject.AccessConnection     rs.ActiveConnection = conn     'Update the database with any changes from the recordset     rst.UpdateBatch     rs.Close     conn.Close     Set rs = Nothing     Set conn = Nothing   End Sub

An optional value can also be used with UpdateBatch that determines how many records will be affected by the statement.

Option	Comment
AdAffectCurrent	Write pending changes only for the current record.
AdAffectGroup	Write pending changes for the records that satisfy the current Filter property setting.
adAffectAll	(Default) Write pending changes for all the records in the Recordset object, including any hidden by the current Filter property setting.

This section only introduced the topic of disconnected recordsets. Disconnected recordsets provide you with a way to download records to locally manipulate those records and write the changes to the server at a later time. There are also three options which can be used with UpdateBatch .