Category list

Retrieving a User Recordset

< Day Day Up >

Retrieving a User Recordset

So far, most of the Recordset objects that you've worked with have contained data from your database. But there's another type of Recordset: the schema recordset . A schema recordset is one that contains information on the schema (design) of the database, or some other information maintained by the database engine.

To open a schema recordset, you use the OpenSchema method of the Connection object. This method takes three arguments:

QueryType A constant indicating the type of information to retrieve.
Criteria An optional filter to tell ADO to return less than a full schema recordset.
SchemaID If the QueryType argument is adSchemaProviderSpecific , this argument is required and specifies exactly which schema recordset to return.

Some further explanation might make the use of the SchemaID argument more obvious. The designers of ADO defined a number of standard schema recordsets that they expect most databases to be able to handle (for example, a list of all tables in the database). But the ADO designers couldn't anticipate every schema recordset that an individual database might like to supply. So they supplied adSchemaProviderSpecific as a sort of wildcard. If you call for this schema type, the actual type is determined by the value that you provide for the SchemaID argument; this value is typically a Globally Unique Identifier, or GUID.

You actually saw the use of the SchemaID argument in Chapter 16's case study. In that case study, you constructed the CurrentUsers form. This form uses a schema recordset to retrieve the names of all the users who have the database currently open:

Public Const JET_SCHEMA_USERROSTER = _

 "{947bb102-5d43-11d1-bdbf-00c04fb92675}"



Public Function ReturnUsers() As String

  Dim cnn As ADODB.Connection

  Dim rst As ADODB.Recordset

  Set cnn = New ADODB.Connection

  On Error GoTo HandleErr

  'Open connection to database

  Set cnn = CurrentProject.Connection

  'Open schema recordset to grab user metadata

  Set rst = cnn.OpenSchema(adSchemaProviderSpecific, , _

   JET_SCHEMA_USERROSTER)

  'return current users

  rst.MoveFirst

  Do Until rst.EOF

    ReturnUsers = rst(0) & ";" & ReturnUsers

    rst.MoveNext

  Loop



ExitHere:

  rst.Close

  Set rst = Nothing

  cnn.Close

  Set cnn = Nothing

  Exit Function

HandleErr:

  MsgBox "Error " & Err.Number & ": " & _

   Err.Description

  Resume ExitHere

End Function

As you can see, the code needs to define the JET_SCHEMA_USERROSTER constant itself; this constant is not built into ADO.

< Day Day Up >

Using Other Schema Recordsets

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

Constant	Description
`adSchemaCheckConstraints`	A recordset of validation rules in the database.
`adSchemaColumns`	A recordset of fields in the database.
`adSchemaConstraintColumnUsage`	A recordset that shows which columns are affected by which constraints.
`adSchemaForeignKeys`	A recordset of foreign keys in the database.
`adSchemaIndexes`	A recordset of indexes in the database.
`adSchemaKeyColumnUsage`	A recordset that shows which columns are included in which keys.
`adSchemaPrimaryKeys`	A recordset that shows all the primary keys in the database.
`adSchemaProcedures`	A recordset of queries that have parameters.
`adSchemaProviderTypes`	A recordset that lists the data types that Jet supports.
`adSchemaReferentialConstraints`	A recordset of referential integrity constraints.
`adSchemaStatistics`	A recordset of database statistics.
`adSchemaTableConstraints`	A recordset of table-level validation rules.
`adSchemaTables`	A recordset of tables in the database.
`adSchemaTrustees`	A recordset of users and groups defined in the database.
`adSchemaViews`	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:

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

NOTE

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.

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 >

Buy on amazon.com >>

Harkins S. S., Gunderloy M.

<< Previous page

Table of contents

Next page >>