Using Other Schema Recordsets

 < 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.

Table 19.1. Standard Schema Recordsets Supported by Access




A recordset of validation rules in the database.


A recordset of fields in the database.


A recordset that shows which columns are affected by which constraints.


A recordset of foreign keys in the database.


A recordset of indexes in the database.


A recordset that shows which columns are included in which keys.


A recordset that shows all the primary keys in the database.


A recordset of queries that have parameters.


A recordset that lists the data types that Jet supports.


A recordset of referential integrity constraints.


A recordset of database statistics.


A recordset of table-level validation rules.


A recordset of tables in the database.


A recordset of users and groups defined in the database.


A recordset of queries that do not have parameters.

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:

  • {8703b612-5d43-11d1-bdbf-00c04fb92675} Performance statistics recordset

  • {947bb102-5d43-11d1-bdbf-00c04fb92675} User roster recordset

  • {e2082df0-54ac-11d1-bdbb-00c04fb92675} Recordset of partial filters in replicas

  • {e2082df2-54ac-11d1-bdbb-00c04fb92675} List of conflict tables in replicas

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:




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

CASE STUDY: Using the Form Error Event to Resolve Locking Errors

You saw earlier in this chapter that you can use error handling to trap record-locking errors when working with data in a recordset. But in many applications (including the TimeTrack sample database), users interact with forms rather than with recordsets. What then?

You have two choices for handling locking errors in forms. The first is to allow Access to handle things with its own default messages. If you elect this course of action, the messages shown in Figures 19.1 and 19.3 are presented to users when record-locking problems come up in a multiuser setting.

The alternative is to use the form's Error event. This event is raised whenever a data error happens in a form. For this case study, we've added some simple code to the Clients form to handle record-locking errors. Here's the code:


 Private Sub Form_Error(DataErr As Integer, _  Response As Integer)   ' Handle multi-user errors   On Error GoTo HandleErr   ' Handle error based on error number   ' passed in by Access   Select Case DataErr     Case 7787 ' Write conflict       MsgBox "Another user has changed this record. " & _        "Click OK to see their changes.", vbCritical       Response = acDataErrContinue     Case 7878 ' Data Changed       MsgBox "Another user has changed this record. " & _        "Click OK to see their changes.", vbCritical       Response = acDataErrContinue     Case Else       ' Let Access handle the error       Response = acDataErrDisplay   End Select ExitHere:   Exit Sub HandleErr:   MsgBox "Error while handling errors", vbCritical   Resume ExitHere End Sub 

When you're working with the form's Error event, there are two arguments to deal with. The first, DataErr, is passed in by Access and includes the error number of the error that triggered the event. The second, Response, enables you to tell Access whether you've handled the error. You can pass back acDataErrContinue to tell Access that you've taken care of things, or acDataErrDisplay to tell Access to take its own default action (displaying the error).

In this particular case, you're trapping both varieties of locking error, warning the users that their data will get overwritten, and proceeding. Access automatically refreshes the form with the current data from the underlying database after this code runs. If any other data error (such as a validation error) occurs, this procedure just lets Access take its own default action. Finally, if an error occurs in the process of trying to handle some other error, the error handler displays a default message and bails out. This prevents the code from getting stuck in an infinite loop if something should go badly wrong.

     < Day Day Up > 

    Automating Microsoft Access with VBA
    Automating Microsoft Access with VBA
    ISBN: 0789732440
    EAN: 2147483647
    Year: 2003
    Pages: 186 © 2008-2017.
    If you may any questions please contact us: