Retrieving a User Recordset

 < Day Day Up > 

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 > 

    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: