[AccessD] Assign Jet User Roster Recordset to Form

Charlotte Foust cfoust at infostatsystems.com
Mon Jul 28 12:06:30 CDT 2003


For anyone who is interested, here's the solution I came up with so I
could use the schema recordset to populate a form:

Public Function SchemaToRecordset(rstSchema As ADODB.Recordset) As
ADODB.Recordset
' accepts a schema recordset and converts it into a memdory resident ADO
recordset
' for display.  No connection object is needed because this is a
disconnected recordset
' built in memory.  It also could be persisted to XML or ADTG format if
we wanted.
' Created by Charlotte Foust 28-Jul-03

  Dim rst As ADODB.Recordset
  Dim intElement As Integer
  
  'instantiate the recordset
   On Error GoTo SchemaToRecordset_Err

  Set rst = New ADODB.Recordset
    If rstSchema.RecordCount <> 0 Then
        rstSchema.MoveFirst
        With rst
            'append the fields/"dimensions"
            .Fields.Append rstSchema.Fields(0).Name, adVarChar,
Len(rstSchema.Fields(0).Name)
            .Fields.Append rstSchema.Fields(1).Name, adVarChar,
Len(rstSchema.Fields(1).Name)
            .Fields.Append rstSchema.Fields(2).Name, adBoolean,
Len(rstSchema.Fields(2).Name)
            .Fields.Append rstSchema.Fields(3).Name, adBoolean,
Len(rstSchema.Fields(3).Name)
          
            'put data in the recordset
            .Open
            
            Do While Not rstSchema.EOF
              .AddNew Array(.Fields(0).Name, .Fields(1).Name,
.Fields(2).Name, _
                    .Fields(3).Name), Array(Trim(rstSchema.Fields(0)),
Trim(rstSchema.Fields(1)), _
                    Trim(rstSchema.Fields(2)),
Trim(Nz(rstSchema.Fields(3), 0)))
              rstSchema.MoveNext
            Loop
            'write all pending changes - no save required
            .UpdateBatch
        End With
    End If
    Set SchemaToRecordset = rst

SchemaToRecordset_Exit:
   On Error Resume Next
   Exit Function

SchemaToRecordset_Err:
    MsgBox "Error: " & Err.Number & " - " & Err.Description,
vbExclamation, "SchemaToRecordset"
    rst.Close
    Set rst = Nothing
    Resume SchemaToRecordset_Exit
End Function

-----Original Message-----
From: Charlotte Foust 
Sent: Thursday, July 24, 2003 10:36 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Assign Jet User Roster Recordset to Form


Well it *may* return an array, but it responds like a recordset if you
don't use GetString.  You can use all the .MoveNext, .EOF, etc.,
methods, and the Fields collection has properties like Name available.
It seems like there should be a simpler way to use the results.

Charlotte Foust

<snip>


More information about the AccessD mailing list