Creating a Recordset Object from a Table

The method you use to create a Recordset object from a table depends on whether the table is local to the current database or is a linked table in another database. The following discussion explains the differences and provides examples for each type of table.

From a Table in a Local Microsoft Jet Database

The following example uses the OpenRecordset method to create a table-type Recordset object from a table, where strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset

Set dbs = OpenDatabase(strDbPath, dbOpenTable)
Set rst = dbs.OpenRecordset("Customers")

Note that you don’t need to use the dbOpenTable constant to create a table-type Recordset object. If you omit the type constant, Microsoft Jet chooses the highest-functionality Recordset object type available, depending on the object the Recordset object is created from, and the type of data source being used. Because the table-type Recordset object is available when you open a Recordset object from a local table, Microsoft Jet uses it by default.

From a Linked Table in a Different Database Format

The next example creates a dynaset-type Recordset object for a linked Paradox 3.x table. Because the table type is not available when you open a Recordset object from a linked table in a non-Microsoft Jet database, Microsoft Jet selects the next most efficient type, opening a dynaset-type Recordset object. In the following example, strDbPath is the path to the Microsoft Jet database:

Dim dbs As Database
Dim tdf As TableDef, rst As Recordset

' Get current database.
Set dbs = OpenDatabase(strDbPath)
Set tdf = dbs.CreateTableDef("PDXAuthor")

' Connect to Paradox table Author in database C:\JetBook\Samples\pdx.
tdf.Connect = "Paradox 3.X;DATABASE=C:\JetBook\Samples\pdx"
tdf.SourceTableName = "Author.db"

' Link table.
dbs.TableDefs.Append tdf

' Create a dynaset-type Recordset object for the table.
Set rst = tdf.OpenRecordset()

' Print each author's first and last name.
With rst
	Do While Not .EOF
		Debug.Print !au_fname & " " & !au_lname
		.MoveNext
	Loop
End With

You can also open a Paradox table as a table-type Recordset object by first opening the Paradox database.

See Also For more information about connecting to data in external databases and improving performance by using the OpenDatabase method, see Chapter 8, “Accessing External Data.”

Using an Index on a Table-Type Recordset Object

You can order records in a table-type Recordset object by setting its Index property. Any Index object in the Indexes collection of the Recordset object’s underlying table can be specified in the Index property.

The following example illustrates how to create a table-type Recordset object based on the Customers table that uses using an existing index called City. Note how setting the Index property re-sorts the records in the Recordset object. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset

Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Customers", dbOpenTable)

With rst
	.MoveFirst				' Move to first record.
	MsgBox !CompanyName	' Show first CompanyName field with no index set.
	.Index = "City"			' Select existing City index to reorder records.
	.MoveFirst				' Move to new first record.
	MsgBox !CompanyName	' Show new first CompanyName field.
End With

You can use the Seek method to locate a record in an indexed table-type Recordset object. You must set the Index property before using the Seek method. If you set the Index property to an index that doesn’t exist, a trappable run-time error occurs. If you want to sort records according to an index that doesn’t exist, either create the index first, or create a dynaset- or snapshot-type Recordset object using a query with an ORDER BY clause that returns records in a specified order.