Several years ago, Mike M’s company decided that they needed to build an in-house Content Management System portal application. Well, actually, the lead developer made the decision… and the recommendation… largely because writing applications are boring. But writing platforms upon which you can write applications… now that’s exciting!

Two years and several failed “proof of concepts” later, and the company had portal driven by a VB6 application with a SQL Server database behind it. If one were to create a list of adjectives to describe this monstrosity, “stable,” “reliable,” and “accurate” would be strikingly absent. However “stillborn” would probably make the top of the list. When Mike took over, the application had 200 forms, one auxiliary code module, and zero classes.

As for the SQL Server database, it had nearly 200 tables (mostly imported from Microsoft Access) and not a single stored procedure. Boolean-type values were represented as a CHAR(5) field, holding possible values of 'true', 'false', or NULL. Let me repeat that, because it bears repeating. Boolean-type values in the database were rendered as a CHAR(5) field holding possible values of 'true', 'false', or NULL.

There were also no indexes. Nor any primary key columns. Or even any auto-incrementing IDENTITY columns for that matter. For inserts, an ID was assigned via in-line SQL by requesting the Max(ID) field of any particular table and adding 1. "Duplications?" you ask. Why yes. Yes, there were.

The code module contained such gems as this function:

Function FileExists(f As String) As Boolean

Dim ff As Long
    On Error GoTo BadFile
    ff = FreeFile
    Open f For Input As #ff
    FileExists = True
    Close #ff
    On Error GoTo 0
    Exit Function
BadFile:
    FileExists = False
    On Error GoTo 0

End Function

There were also several dozen Crystal Reports, each pointing to a specific table in the database. The database table for any particular report was erased and repopulated via in-line SQL statements each time the report was run. As for how that was actually accomplished, following is the basic report code. It was essentially repeated, line-for-line, on each report in the application.

Function GenerateSomeReport(sdate As String, EDate As String) As Boolean
    
Dim st As String, lt As String
Dim fn As String, ln As String
Dim counter As Long
Dim itmx As ListItem, Started As Boolean Dim dt As New Recordset, dt2 As New Recordset

    GenerateSomeReport= True
    Started = False
    Load frmProcess
    frmProcess.Caption = "Generate Some Report"
    frmProcess.Show
    frmProcess.Imsg = "Deleting old Report..."
    frmProcess.pMin = 0
    frmProcess.cCancel.Enabled = False
    DoEvents
    dt.Open "SOME_REPORT_TABLE", cn, adOpenKeyset, adLockOptimistic
    If Not dt.EOF Then
        dt.MoveLast
        frmProcess.pMax = dt.RecordCount
        dt.MoveFirst
        Do While Not dt.EOF
            dt.Delete
            dt.MoveNext
            frmProcess.pVal = frmProcess.pVal + 1
            DoEvents
        Loop
    End If
    frmProcess.cCancel.Enabled = True
    frmProcess.Imsg = "Generating..."
    frmProcess.pMin = 0
    DoEvents
    
    st = "SELECT * FROM TARGET_TABLE WHERE "
    st = st & "SOMEDATE >= ('" & sdate & " 00:00:00') AND SOMEDATE <= ('" & EDate & " 23:59:59') "
    st = st & "ORDER BY ID;"
    dt2.Open st, cn, adOpenKeyset, adLockOptimistic
    If dt2.EOF Then
        msg = "No records match the report criteria!"
        MsgBox msg, 48, frmProcess.Caption
        GenerateSomeReport = False
        GoTo GetOut
    End If
    
    Started = True
    frmProcess.pMax = dt2.RecordCount

ContGen:
    Do While Not dt2.EOF And frmProcess.tag <> "C"
        dt.AddNew
        For X = 0 To dt.Fields.COUNT - 1
            dt(X) = dt2(dt(X).Name)
        Next
        dt.Update
        dt2.MoveNext
        frmProcess.pVal = frmProcess.pVal + 1
        DoEvents
    Loop
    
GetOut:
    If frmProcess.tag = "C" Then
        msg = "Are you sure you want to cancel generating the report?"
        rsp = MsgBox(msg, vbYesNo + vbDefaultButton2 + vbQuestion, frmProcess.Caption)
        If rsp = vbNo Then
            frmProcess.tag = ""
            GoTo ContGen
        End If
        GenerateSomeReport = False
        GoTo CancelGen
    End If

CancelGen:
    frmProcess.Hide
    Unload frmProcess
    dt.Close
    If Started Then
        dt2.Close
    End If

End Function

Few interesting things to note...

  • frmProcess was a simple form with a progress/status bar and a cancel button. Pressing the button set its tag value to "C," which canceled the report.
  • Each leftover row in the table (from the last time the report was run) is deleted one at a time, and repopulated one at a time. 'DELETE|TRUNCATE TABLENAME' anyone?
  • Each row in the selection recordset was added to the report table not just row by row, but field by field. Apparently 'SELECT ... INTO' was too new-fangled to be trusted.
  • Imagine what happens should two people run the same report at the same time.

Amazingly enough, Mike lasted several years there, rewriting the application as best he could. He added a few classes to the application, pointed the reports to stored procedures (using gasp! "parameters"), deleted in-line SQL where ever he found it, and generally kept the whole thing limping along. All the while, Mike maintained a constant plea for the resources to rewrite the whole thing from scratch. Obviously, his pleas were never granted, and he eventually left the monstrosity to some unfortunate new developer.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!