Getting a recordset from a hierarchical 6-table database

[Skrmetti]

I am trying to fill a MSHFlexGrid with the results of a query of a 6-table database. The database is hierarchical, and I would like for the fields from each table to be displayed in the grid. I can manage to display the data from two tables using a join, but beyond that I am at a loss.

I think DataShape might work but I haven't been able to figure out how to get my recordset to use DataShape. If anybody could help me out with this, I'd really appreciate it. Here is the code I am using (the current SQL is a simple statement just to let my boss know that the grid does in fact display some sort of useful information):

Public Sub Query(strDBPath As String, strSQL As String)
Dim rsRecordset As ADODB.Recordset
Dim strConnect As ADODB.Connection
Dim fld As ADODB.Field
Set strConnect = New ADODB.Connection
With strConnect
.Provider = "Microsoft.Jet.OLEDB.3.51"
.Open strDBPath
End With

Set rsRecordset = New ADODB.Recordset
With rsRecordset
.Open strSQL, strConnect, adOpenStatic, adLockReadOnly

End With

Set TDBGrid1.DataSource = rsRecordset
Set MSHFlexGrid1.Recordset = rsRecordset
Dim x As Integer
For x = 0 To 1
MSHFlexGrid1.MergeCol(x) = True
Next x

strConnect.Close
Set rsRecordset = Nothing

End Sub

Private Sub cmdExecuteQuery_Click()
strSQL = "SELECT DeviceNumber, CableNumber, DestinationFrom, DestinationTo, ElectricalDrawings, Comments FROM Cable Order By DeviceNumber"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "
End Sub

Thanks in advance.

Jonathan Skrmetti
[e-mail][email protected][/e-mail]

[JHausmann]

You're welcome.

If you were using a DBMS that supported "views", the answer would be different (create a view).

[JHausmann]

1) create a *big* table that has all the fields from the other 6 tables

in your program
1) delete all entries from the *big* table
2) populate the table using data from the other six tables
3) populate the dbflexgrid from this one table

[Skrmetti]

Wow. Thanks for the suggestion. I'll try it out now and let you know how it works.

Wow.

Thanks,
Jonathan