OpenRecordset Method Example

This example prompts the user for the name of a recordset, given a list of all the available recordsets in the Nwindex.mdb database. The example opens a new recordset based on the recordset indicated by the user and then copies the records onto a new worksheet.

To create the Nwindex.mdb database, run the Microsoft Excel example for the CreateDatabase method.

Dim db As Database, rs1 As Recordset
Set db = _
    Workspaces(0).OpenDatabase(Application. _
    DefaultFilePath & "\NWINDEX.MDB")
prmpt = "Enter the name of the recordset to import:" & vbCr
i = 0
Do Until i = db.TableDefs.Count
    ' omit protected system tables
    If InStr(LCase(db.TableDefs(i).Name), "msys") = 0 Then
        tbls = tbls & vbCr & db.TableDefs(i).Name
    End If
    i = i + 1
Loop
res = Application.InputBox(Prompt:=prmpt & tbls, _
    Title:="Import Recordset", Type:=2)
If res = False Then GoTo 500
On Error GoTo 490
Set rs1 = db.OpenRecordset(res)
Set reportsheet = Worksheets.Add
With reportsheet
    .Activate
    .Cells(1).CopyFromRecordset rs1
    .Cells(1).CurrentRegion.Columns.AutoFit
End With
rs1.Close
GoTo 500
490: MsgBox "Not a valid table name"
500: db.Close