You need to perform two steps to open an external table directly:
Note The dbname argument of the OpenDatabase method is used to specify the database name, and the source argument of the OpenRecordset method is used to specify the table name for all IISAM drivers. For external data sources that store one table per file, the dbname argument should contain the full directory or network path to the file, and the source argument should contain the data file name with no extension. For external data sources that store multiple tables in a file, dbname should contain the full directory or network path to the file, including the file name and extension; source should contain the table name.
You can specify a type argument for the OpenRecordset method if you want more control over the Recordset object’s type. For more information about the types of Recordset objects you can open, see Chapter 5, “Working with Records and Fields.”
Important The dbOpenTable constant is not a valid argument for ODBC databases.
After you’ve opened the table, you can access the Recordset object you created just as you would any other Recordset object in your Microsoft Jet database. Keep in mind, however, that you must re-establish your connection to the external data source and re-create the Recordset object for each session.
The following code example opens a table that is stored in a Microsoft Excel 8.0 workbook. The example opens a single worksheet in a workbook.