Hierarchische Recordsets - once more

[Lothar Geyer]

Wie kann ich einen hierarchischen Recordset nur mit ADO-Mitteln (also
ohne SQL) erzeugen?

Etwa so:

Set rs = New Recordset
rs.Fields.Append "Field1", dbInteger
rs.Fields.Append "Field2", dbInteger
rs.Fields.Append "Level2", dbChapter
With rs.Fields("Level2")
.Fields.Append "ABC"
...
End With

Aber das spielts ja nicht ...

Lothar Geyer

[Wilfried Dietrich]

Hallo Lothar,

> Wie kann ich einen hierarchischen Recordset nur mit ADO-Mitteln (also
> ohne SQL) erzeugen?
>
> Etwa so:
>
> Set rs = New Recordset
> rs.Fields.Append "Field1", dbInteger
> rs.Fields.Append "Field2", dbInteger
> rs.Fields.Append "Level2", dbChapter
> With rs.Fields("Level2")
> .Fields.Append "ABC"
> ...
> End With

vielleicht so.

Set rsParent = New ADODB.Recordset
Set rsChild = New ADODB.Recordset

rsParent.CursorLocation = adUseClient
rsChild.CursorLocation = adUseClient

rsParent.Fields.Append "Field1", adInteger
rsParent.Fields.Append "Field2", adInteger
rsChild.Fields.Append "Level2", adIDispatch
rsChild.Fields.Append "Test", adVarChar, 20

rsParent.Open
rsChild.Open

rsChild.AddNew
rsChild(0) = "Datensatz1"
rsChild.Update
rsChild.AddNew
rsChild(0) = "Datensatz2"
rsChild.Update
rsChild.MoveFirst

rsParent.AddNew
rsParent(0) = 100
rsParent(1) = 101
rsParent(2) = rsChild
rsParent.Update
...
etc.

Gruß
Wilfried

[Wilfried Dietrich]

Vor dem Senden habe ich es mehrfach gelesen, und doch.... :-o

Korrektur:

> ...

> rsParent.Fields.Append "Field2", adInteger
> rsChild.Fields.Append "Level2", adIDispatch
> rsChild.Fields.Append "Test", adVarChar, 20

> ...

Das muss natürlich wie folgt lauten:
...
rsParent.Fields.Append "Field2", adInteger
rsParent.Fields.Append "Level2", adIDispatch <<<<<

rsChild.Fields.Append "Test", adVarChar, 20

...

Gruß
Wilfried

[Peter Götz]

Hallo Lothar,

> Wie kann ich einen hierarchischen Recordset nur mit ADO-Mitteln (also ohne
> SQL) erzeugen?

Wozu sollte das gut sein?
Warum nicht einfach zwei Recordsets (1 RSmaster und
1 RSslave) erzeugen.
Bei jedem RSmaster_MoveComplete setzt Du dann
RSdetail.Filter = "ID = " & RSmaster.Fields("ID") .

Beispiel:
1 Form (Form1)
mit
1 DataGrid (DataGrid1)
1 DataGrid (DataGrid2)

' /// Code Form1
Option Explicit
Private WithEvents mRSmaster As ADODB.Recordset
Private mRSslave As ADODB.Recordset

Private Sub Form_Load()
CreateData
mRSmaster.MoveFirst
Set DataGrid1.DataSource = mRSmaster
End Sub

Private Sub CreateData()
Dim i As Integer
Dim k As Integer
Dim MaxK As Integer

Set mRSmaster = New ADODB.Recordset
With mRSmaster
.Fields.Append "ID", adInteger
.Fields.Append "Text", adVarChar, 24

.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.Open
End With

Set mRSslave = New ADODB.Recordset
With mRSslave
.Fields.Append "ID", adInteger
.Fields.Append "SubID", adInteger
.Fields.Append "Text", adVarChar, 24
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Open
End With

For i = 1 To 5
With mRSmaster
.AddNew
.Fields(0).Value = i
.Fields(1).Value = "Text " & CStr(i)
.Update
End With

MaxK = Fix((8 - 1 + 1) * Rnd + 1)
For k = 1 To MaxK
With mRSslave
.AddNew
.Fields(0).Value = i
.Fields(1).Value = k
.Fields(2).Value = "Sub Text " & CStr(i) & "; " & CStr(k)
.Update
End With
Next k
Next i
End Sub

Private Sub mRSmaster_MoveComplete _
(ByVal adReason As ADODB.EventReasonEnum, _
ByVal pError As ADODB.Error, _
adStatus As ADODB.EventStatusEnum, _
ByVal pRecordset As ADODB.Recordset)

If Not (mRSmaster.BOF Or mRSmaster.EOF) Then
Dim X As Variant
X = mRSmaster.Fields(0).Value
Debug.Print TypeName(X)
If VarType(X) = vbLong Then

mRSslave.Filter = _
"ID = " & CStr(mRSmaster.Fields(0).Value)

Set DataGrid2.DataSource = mRSslave
End If
End If
End Sub
' \\\ E N T E

Gruß aus St.Georgen
Peter Götz
www.gssg.de (mit VB-Tipps u. Beispielprogrammen)