Where has all the data gone? (Complicated and involved)

[Skrmetti]

I have a weird problem, and for once it's not anything related to me personally. Instead, it has to do with a TDBGrid that refuses to display all of the data I put into it. I have used DDL to create a temporary table, filling it with all the data from five other tables, and I want it to display all the information in the grid. As it happens now, all of the column headers appear, but the only columns with data are those representing the first two tables that were added into the Big Table. I tried breakingu up the insertion code, reasoning that maybe the SQL string was just too long for my computer to handle, but unfortunately that did not work. Any suggestions? I'm going to put the code at the bottom here, because it's very long and then a bit longer.

Thanks,
Jonathan

Private Sub cmdExecuteQuery_Click()

MousePointer = 11

'The way to solve the problem of displaying multiple tables is through SQL's DDL (Data
'Definition Language), which allows you to use SQL statements to create tables, fill them
'up with data, delete them, etc., during runtime. This is kind of tricky, but it seems to
'be the only viable option for making the grid display whatever the user wants out of all
'the tables of the SafeShutdown database.

'Create the BigTable with all the fields of the hierarchical tables (i.e., tables FireArea,
'FireZone, Component, Device, Cable - Raceawys can be Inner Joined after BigTable is created)

On Error GoTo ErrBigTable 'If BigTable already exists this will drop the existing one to make way for the new.

CreateTable:
strSQL = "CREATE TABLE tblBigTable ([FireArea] TEXT, [FireZone] TEXT, [Division] TEXT,"
strSQL = strSQL & "[SafeShutdownComments] MEMO, [ComponentNumber] TEXT, [SystemNumber] TEXT)"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "
strSQL = "ALTER TABLE tblBigTable ADD COLUMN PIDNumber TEXT"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "
strSQL = "ALTER TABLE tblBigTable ADD COLUMN PIDLocation TEXT"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "
strSQL = "ALTER TABLE tblBigTable ADD COLUMN DeviceNumber TEXT"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "
strSQL = "ALTER TABLE tblBigTable ADD COLUMN Location TEXT"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "
strSQL = "ALTER TABLE tblBigTable ADD COLUMN CableNumber TEXT"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "
strSQL = "ALTER TABLE tblBigTable ADD COLUMN DestinationFrom TEXT"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "
strSQL = "ALTER TABLE tblBigTable ADD COLUMN DestinationTo TEXT"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "
strSQL = "ALTER TABLE tblBigTable ADD COLUMN ElectricalDrawings TEXT"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "

'Fill the fields of BigTable with the values of the hierarchical tables

strSQL = "INSERT INTO tblBigTable SELECT FireArea FROM FireArea"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "
strSQL = "INSERT INTO tblBigTable SELECT FireZone,FireArea,Division,SafeShutdownComments FROM FireZone"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "
strSQL = "INSERT INTO tblBigTable SELECT ComponentNumber, SystemNumber, PIDNumber, PIDLocation FROM Component"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "
strSQL = "INSERT INTO tblBigTable SELECT DeviceNumber, Location FROM Device"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "
strSQL = "INSERT INTO tblBigTable SELECT CableNumber, DestinationFrom, DestinationTo, ElectricalDrawings FROM Cable"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "


'This is where the search string will be assembled once the BigTable is filled with
'everything:

strSQL = "SELECT * FROM tblBigTable"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "

GoTo SubEnd

ErrBigTable:
strSQL = "DROP TABLE tblBigTable"
Query "L:\GSysMod\Clients\Entergy\SafeShutdown\SafeShutdown.mdb", " " & strSQL & " "
GoTo CreateTable

SubEnd:

End Sub

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 = "MSDataShape.1;Data 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
'columnwidth <= This is not currently functional; see "Private Sub Columnwidth()"
'MSHFlexGrid1.Col = 1
'MSHFlexGrid1.Sort = flexSortNumericAscending
'MSHFlexGrid1.Col = 0
'MSHFlexGrid1.Sort = flexSortNumericAscending
'Dim x As Integer
'For x = 0 To 1
'MSHFlexGrid1.MergeCol(x) = True
'Next x

'The MousePointer is currently changing back too quickly. I'd like it
'to stay an hourglass until the grid is finished, but it doesn't want
'to do that. Hmmmm.
MousePointer = 0

End Sub

[JHausmann]

Couple of things.

1) See if you can convince your bosses to buy SQL Server (if you have the Enterprise edition of Dev Studio, it comes with it. If not, look into the personal edition of 7.0). Reason: it supports views. Views, once created, can be accessed like tables and you don't have to do all the stuff you're doing.

2)FireArea and Firezone have the field fireArea in common. Are there going to be firearea's in Firezone (or vice versa) that are not in Firearea? If not, forget the insert from FireArea.

3) I would only have one "insert" into the bigtable, followed by "updates". Although it's not entirely clear how you want to link the tables...

The following is tagged with code to preserve the spacing:

Table Link Table
FireZone FireArea FireArea
FireZone ComponentNumber Component
FireZone Devicenumber Device
FireZone CableNumber Cable


What you need to do is find a field each table shares in common so that you can link the tables. For instance, each table might have "firearea" as a field in common which makes your life easy.

[Skrmetti]

The tables I'm working with now are strictly hierarchical, and the two that appear (FireArea and FireZone) are relating properly (i.e., if FireArea A contains FireZone 1 and 2 then FireZone 1 and 2 both appear in rows beginning with FireArea A). I will put everything back into one big mammajamma SQL statement and see if that helps. Thanks for the advice.

BTW, I only have VS Professional Edition. I'd love to get my hands on SQL Server, since I constantly read about all the cool things you can do with it, but I'm only here for three more weeks and I doubt my company would support any sort of big outlay beyond the McManus book they bought me a few weeks ago.

Thanks,
Jonathan