Default Recordset Object Types

Because Microsoft Jet automatically chooses the default Recordset object type depending on the data source and how the Recordset object is opened, you don’t need to specify a Recordset object type. However, you can force Microsoft Jet to create specific Recordset objects by specifying the type argument of the OpenRecordset method.

The following list describes the available types and the default type, depending on how you open the Recordset object:

• OpenRecordset method from a Database object:

  Set rst = dbs.OpenRecordset(datasource)

  If datasource is a table local to the database, all Recordset object types except the dynamic type are available, and the default type is table. The dynamic-type Recordset object is available only in ODBCDirect workspaces. If datasource is not a local table, only the dynaset, snapshot, or forward-only Recordset object types are available, and dynaset is the default.

• OpenRecordset method from a TableDef object:

  Set rst = tdf.OpenRecordset()

  If tdf refers to a table in a Microsoft Jet database or to an external ISAM database opened directly, all Recordset object types except the dynamic type are available and table is the default type. If tdf is in an ODBC database or is a linked table in an external database, only the dynaset, snapshot, and forward-only Recordset object types are available and dynaset is the default.

• OpenRecordset method from a QueryDef object or an SQL string argument:

  Set rst = qdf.OpenRecordset()

  Only the dynaset, snapshot, and forward-only Recordset object types are available, and the default is dynaset.

• OpenRecordset method from an existing Recordset object:

  Set rstNew = rstExisting.OpenRecordset()

  Only the dynaset, snapshot, and forward-only Recordset object types are available. The default is the Recordset object type of the source Recordset object. For example, in the preceding example, if rstExisting is a snapshot-type Recordset object, rstNew will also be a snapshot-type Recordset object.

Microsoft Access Users To create a Recordset object based on a Microsoft Access form, you can use the RecordsetClone property of the form. This creates a dynaset-type Recordset object that refers to the same underlying query or data as the form’s RecordSource property. If a form is based on a query, for example, referring to the form’s RecordsetClone property is the equivalent of creating a dynaset with the same query. You can use the RecordsetClone property when you want to apply a method that can’t be used with forms, such as the FindFirst method. The RecordsetClone property provides access to all the methods and properties that you can use with a dynaset-type Recordset object. The syntax for the RecordsetClone property is:

Set recordset = form.RecordsetClone

The form argument is the name of an open Microsoft Access form.

The following example returns a Recordset object variable from the record source of a form. It then searches for the specified record in the recordset, and if the record exists, the form’s Bookmark property is set to the value of the recordset’s Bookmark property. Setting the form’s Bookmark property displays the corresponding record.

In this example, strFormName is the name of a form, strFieldName is the name of a field on a form, and strFieldValue is the value to search for in the recordset.

Sub FindFormRecord(strFormName As String, strFieldName As String, _
	strFieldValue As String)

	Dim rst As Recordset
	Dim frm As Form
	
	' Open the specified form.
	DoCmd.OpenForm strFormName
	Set frm = Forms(strFormName)
	' Open a recordset based on the form's RecordSource property.
	Set rst = frm.RecordsetClone
	With rst
		' Search for the specified value.
		.FindFirst strFieldName & " Like """ & strFieldValue & "*" & """"
		' If value is not found, display message.
		If .NoMatch Then
			MsgBox "No record found for " & strFieldName & ": " & strFieldValue
		' If value is found, set form's Bookmark property to value of recordset's
		' Bookmark property.
		Else
			frm.Bookmark = .Bookmark
		End If
		.Close
	End With
End Sub

The RecordsetClone property always creates the type of Recordset object being cloned (the type of Recordset object on which the form is based); no other types are available.

Visual Basic Users You can create a Recordset object from a Data control by setting a new Recordset object variable equal to the value of the control’s Recordset object property. For more information, see the Microsoft Visual Basic Programmer’s Guide.