|< Day Day Up >|
The Jet database engine can actually supply quite a few schema recordsets other than the user roster. Table 19.1 lists the standard ADO schema recordsets that the Jet engine supports.
In addition to the standard schema recordsets, the Jet engine used by Access defines four provider-specific recordsets, each of which is identified by a GUID:
As an example of using a schema recordset, here's a procedure that returns some information on the tables in the current database:
Public Sub ListTables() Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim fld As ADODB.Field Dim i As Integer Set cnn = New ADODB.Connection On Error GoTo HandleErr 'Open connection to database Set cnn = CurrentProject.Connection 'Open schema recordset to grab table metadata Set rst = cnn.OpenSchema(adSchemaTables) ' Now walk through the entire returned recordset Do Until rst.EOF ' Go through each field in the row For i = 0 To rst.Fields.Count - 1 ' And print the name of the field and its value Debug.Print rst.Fields(i).Name & ": " & rst(i) Next i ' Print a blank line after the row Debug.Print rst.MoveNext Loop ExitHere: rst.Close Set rst = Nothing cnn.Close Set cnn = Nothing Exit Sub HandleErr: MsgBox "Error " & Err.Number & ": " & _ Err.Description Resume ExitHere End Sub
If you run this procedure in the sample database, you get back a list of data on each table. For example, here's the data that is returned for the Clients table:
TABLE_CATALOG: TABLE_SCHEMA: TABLE_NAME: Clients TABLE_TYPE: TABLE TABLE_GUID: DESCRIPTION: TABLE_PROPID: DATE_CREATED: 3/13/2004 11:30:57 AM DATE_MODIFIED: 5/15/2004 5:49:53 PM
The reason that many of the fields are blank is because the schema recordsets are general-purpose, and any given field might not apply to a specific database. For a definition of the fields in each schema recordset, refer to Appendix B of the OLE DB Programmer's Reference, which you can find online at http://msdn.microsoft.com/library/en-us/oledb/htm/oledbschema_rowsets.asp.
|< Day Day Up >|