OpenRecordset Method

Applies To   Connection object, Database object, Dynaset-Type Recordset object, QueryDef object, Recordset object, Snapshot-Type Recordset object, Table-Type Recordset object, TableDef object.

Description

Creates a new Recordset object and appends it to the Recordsets collection.

Remarks   In a Microsoft Jet workspace, if object refers to a QueryDef object, or a dynaset- or snapshot-type Recordset, or if source refers to an SQL statement or a TableDef that represents a linked table, you can't use dbOpenTable for the type argument; if you do, a run-time error occurs. If you want to use an SQL pass-through query on a linked table in a Microsoft Jet-connected ODBC data source, you must first set the Connect property of the linked table's database to a valid ODBC connection string. If you only need to make a single pass through a Recordset opened from a Microsoft Jet-connected ODBC data source, you can improve performance by using dbOpenForwardOnly for the type argument.

"SELECT LastName, FirstName FROM Authors
WHERE LastName = 'Smith';
SELECT Title, ISBN FROM Titles
WHERE ISBN Like '1-55615-*'"

See Also   Cancel method, Connect property, StillExecuting property, Type property.

Sub OpenRecordsetX()

    Dim wrkJet As Workspace
    Dim wrkODBC As Workspace
    Dim dbsNorthwind As Database
    Dim conPubs As Connection
    Dim rstTemp As Recordset
    Dim rstTemp2 As Recordset

    ' Open Microsoft Jet and ODBCDirect workspaces, Microsoft
    ' Jet database, and ODBCDirect connection.
    Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
    Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
    Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb")
    Set conPubs = wrkODBC.OpenConnection("", , , _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

    ' Open five different Recordset objects and display the
    ' contents of each.

    Debug.Print "Opening forward-only-type recordset " & _
        "where the source is a QueryDef object..."
    Set rstTemp = dbsNorthwind.OpenRecordset( _
        "Ten Most Expensive Products", dbOpenForwardOnly)
    OpenRecordsetOutput rstTemp

    Debug.Print "Opening read-only dynaset-type " & _
        "recordset where the source is an SQL statement..."
    Set rstTemp = dbsNorthwind.OpenRecordset( _
        "SELECT * FROM Employees", dbOpenDynaset, dbReadOnly)
    OpenRecordsetOutput rstTemp

    ' Use the Filter property to retrieve only certain
    ' records with the next OpenRecordset call.
    Debug.Print "Opening recordset from existing " & _
        "Recordset object to filter records..."
    rstTemp.Filter = "LastName >= 'M'"
    Set rstTemp2 = rstTemp.OpenRecordset()
    OpenRecordsetOutput rstTemp2

    Debug.Print "Opening dynamic-type recordset from " & _
        "an ODBC connection..."
    Set rstTemp = conPubs.OpenRecordset( _
        "SELECT * FROM stores", dbOpenDynamic)
    OpenRecordsetOutput rstTemp

    ' Use the StillExecuting property to determine when the
    ' Recordset is ready for manipulation.
    Debug.Print "Opening snapshot-type recordset based " & _
        "on asynchronous query to ODBC connection..."
    Set rstTemp = conPubs.OpenRecordset("publishers", _
        dbOpenSnapshot, dbRunAsync)
    Do While rstTemp.StillExecuting
        Debug.Print "    [still executing...]"
    Loop
    OpenRecordsetOutput rstTemp
    rstTemp.Close
    dbsNorthwind.Close
    conPubs.Close
    wrkJet.Close
    wrkODBC.Close

End Sub

Sub OpenRecordsetOutput(rstOutput As Recordset)

    ' Enumerate the specified Recordset object.
    With rstOutput
        Do While Not .EOF
            Debug.Print , .Fields(0), .Fields(1)
            .MoveNext
        Loop
    End With

End Sub

Sub UKOrders()
    Dim dbs As Database, rst As Recordset
    Dim strSQL As String

    ' Return reference to current database.
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM Orders WHERE [ShipCountry] = 'UK'"
    Set rst = dbs.OpenRecordset(strSQL)
    rst.MoveLast
    Debug.Print rst.RecordCount
    rst.Close
    Set dbs = Nothing
End Sub

Dim db As Database, rs1 As Recordset
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Application.ScreenUpdating = False
Set theDialog = DialogSheets.Add
Set list1 = theDialog.ListBoxes.Add(78, 42, 84, 80)
Set label1 = theDialog.Labels.Add(78, 125, 240, 25)
label1.Text = "Recordsets for " & db.Name
i = 0
Do Until i = db.TableDefs.Count
    list1.AddItem (db.TableDefs(i).Name)
    i = i + 1
Loop
Sheets("Sheet1").Activate
Application.ScreenUpdating = True
If theDialog.Show = True Then
    If list1.Value = 0 Then
        MsgBox "You have not selected an item from the list."
    Else
        Set rs1 = db.OpenRecordset(db.TableDefs(list1.Value - 1).Name)
        ActiveCell.CopyFromRecordset rs1
    End If
End If
rs1.Close
db.Close