[AccessD] Need ADO ADP help

Sun Jul 11 14:25:49 CDT 2004

Just out of curiosity, what is/are the advantage(s) of using a recordset
rather than a query or a bound form? I've been reading this thread
sporadically, but have never used a recordset for either a form or a
report. What significant powers am I missing due to this ignorance? So
do you do this because it's difficult or impossible to create a query
that does the same thing?

TIA,
Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Sunday, July 11, 2004 12:30 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Need ADO ADP help

No -- I haven't tried it in either version -- only used the Form's
Recordset property -- didn't know the Report property was limited. 

Susan H. 

Have you actually tried this Susan?:-) 

In an Access 2002 whitepaper I found the following and it matches my
case in Access 2003 thinking MS has not yet covered this:

In Microsoft Access 2002, it is now possible to use ADO recordsets with
reports in Microsoft Access project files. Unfortunately, the use of the
report Recordset property is limited to project files. If you try to set
or retrieve a report's Recordset property in a Jet database (.mdb) file,
you receive the following error message: 'This feature is not available
in an MBD'.

Regards,
Eric 

-----Oorspronkelijk bericht-----
Van: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] Namens Susan Harkins
Verzonden: zondag 11 juli 2004 18:08
Aan: 'Access Developers discussion and problem solving' 
Onderwerp: RE: [AccessD] Need ADO ADP help 

Ofcourse, if someone knows a work-around for the report ADO 'problem' in
my MDB that would also be just fine though I'd rather do not want to use
ODBC DNS.

 =============I may not have followed the exact problem -- but Access
2003 supports using Recordset objects as source for forms and reports --
using the Report object's Recordset property. And, the Recordset can be
ADO. 

Susan H. 

Recordset Property 
See AlsoApplies ToExampleSpecificsReturns or sets the ADO Recordset or
DAO Recordset object representing the record source for the specified
form, report, list box control, or combo box control. Read/write.

expression.Recordset 
expression    Required. An expression that returns one of the objects in
the

Applies To list. 

Remarks 
You cannot use this property with ODBCDirect recordset types in DAO. 

The Recordset property returns the recordset object that provides the
data being browsed in a form, report, list box control, or combo box
control. If a form is based on a query, for example, referring to the
Recordset property is the equivalent of cloning a Recordset object by
using the same query.

However, unlike using the RecordsetClone property, changing which record
is current in the recordset returned by the form's Recordset property
also sets the current record of the form.

This property is available only by using Visual Basic. 

The read/write behavior of the Recordset property is determined by the
type of recordset (ADO or DAO) and the type of data (Jet or SQL)
contained in the recordset identified by the property.

Recordset type Based on SQL data Based on Jet data ADO Read/Write
Read/Write DAO N/A Read/Write 

The following example opens a form, opens a recordset, and then binds
the form to the recordset by setting the form's Recordset property to
the newly created Recordset object.

Global rstSuppliers As ADODB.Recordset 
Sub MakeRW() 
DoCmd.OpenForm "Suppliers" 
Set rstSuppliers = New ADODB.Recordset 
rstSuppliers.CursorLocation = adUseClient rstSuppliers.Open "Select *
>From Suppliers", _ CurrentProject.Connection, adOpenKeyset,
adLockOptimistic Set Forms("Suppliers").Recordset = rstSuppliers End Sub

Use the Recordset property: 

To bind multiple forms to a common data set. This allows synchronization
of multiple forms. For example, 
   Set Me.Recordset = Forms!Form1.Recordset 

To use methods with the Recordset object that aren't directly supported
on forms. For example, you can use the Recordset property with the ADO
Find or DAO Find methods in a custom dialog for finding a record. 

To wrap a transaction (which can be rolled back) around a set of edits
that affect multiple forms. 
Changing a form's Recordset property may also change the RecordSource,
RecordsetType, and RecordLocks properties. Also, some data-related
properties may be overridden, for example, the Filter, FilterOn,
OrderBy, and OrderByOn properties. 

Calling the Requery method of a form's recordset (for example, 
Forms(0).Recordset.Requery) can cause the form to become unbound. To
refresh the data in a form bound to a recordset, set the RecordSource
property of the form to itself (Forms(0).RecordSource =
Forms(0).RecordSource).

When a form is bound to a recordset, an error occurs if you use the
Filter by Form command. 

Example 
The following example uses the Recordset property to create a new copy
of the Recordset object from the current form and then prints the names
of the fields in the Debug window.

Sub Print_Field_Names() 
    Dim rst As DAO.Recordset, intI As Integer 
    Dim fld As Field 

    Set rst = Me.Recordset 
    For Each fld in rst.Fields 
        ' Print field names. 
        Debug.Print fld.Name 
    Next 
End Sub 

The next example uses the Recordset property and the Recordset object to
synchronize a recordset with the form's current record. When a company
name is selected from a combo box, the FindFirst method is used to
locate the record for that company, causing the form to display the
found record.

Sub SupplierID_AfterUpdate() 
    Dim rst As DAO.Recordset 
    Dim strSearchName As String 

    Set rst = Me.Recordset 
    strSearchName = CStr(Me!SupplierID) 
    rst.FindFirst "SupplierID = " & strSearchName 
    If rst.NoMatch Then 
        MsgBox "Record not found" 
    End If 
    rst.Close 
End Sub 

The following code helps to determine what type of recordset is returned
by the Recordset property under different conditions.

Sub CheckRSType() 
    Dim rs as Object 

    Set rs=Forms(0).Recordset 
    If TypeOf rs Is DAO.Recordset Then 
        MsgBox "DAO Recordset" 
    ElseIf TypeOf rs is ADODB.Recordset Then 
        MsgBox "ADO Recordset" 
    End If 
End Sub 

-- 
_______________________________________________ 
AccessD mailing list 
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com 

  _____  

avast! Antivirus <http://www.avast.com> : Uitgaande bericht is niet
besmet. 

Virus Gegevensbestand (VPS): 0428-1, 09-07-2004
Getest op: 11-7-2004 18:31:59
avast! auteursrecht (c) 2000-2004 ALWIL Software.

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com