Access™ 2007 VBA Programmer's Reference

7.7. Creating Schema Recordsets

You're no doubt familiar with using recordsets to access and manipulate data. But ADO also allows you to open recordsets that contain information about your database's tables. Of course, you can get at this information using ADOX, but some details are more readily accessed using ADO schema recordsets.

To open a schema recordset, issue the OpenSchema method against the Connection object. The OpenSchema method has three parameters you can use to specify more options. Here's the syntax:

connection.OpenSchema Schema, Restrictions, SchemaID As Recordset

The Schema parameter specifies the type of information to return as the result. The available values are defined in Appendix F.

The optional Restrictions parameter allows you to filter the output. For example, you can filter the recordset to return only a single table or view. The available values are listed in Appendix F.

The SchemaID parameter is required only when the Schema parameter is set to adSchemaProvider-Specific, so you must also supply a globally unique identifier (GUID) that identifies the provider schema to return. These are shown as Constants in the example code at the end of this section. For instance, the following code prints the details of every table and view in the current database:

Dim rs As ADODB.Recordset Dim fld As ADODB.Field ' Create the recordset Set rs = CurrentProject.Connection.OpenSchema(adSchemaTables) ' Loop through the recordset rows Do Until rs.EOF For Each fld In rs.Fields ...