vaSpread: Can You Populate FarPoint Spread 6.0 from a SQL Query

I ran into a very old but gold situation: I needed to show a SQL Server report exactly as it appears in SSMS, but inside a FarPoint vaSpread 6.0 grid in a VB6 app. I’m sharing the full, working code I used, a common “gotcha” I hit (and fixed), and a set of practical upgrades so you can go from “it loads” to “it looks great and exports too.” I’m writing in plain, first-person English because I wish I’d found a post like this when I started.

I wanted my SSList vaSpread to bind directly to the results of a T-SQL query. No manual cell poking, no row-by-row loops. I also wanted friendly column headers, proper numeric formatting, a totals row, and a one-click Excel export. Most of all, I wanted the grid to match what I saw in Management Studio.

Query The Exact T-SQL

Here’s the cleaned-up query with safe aliases. If your system uses date keys like YYYYMMDD, you’ll be right at home.

SELECT 
    QC.LINE_CD          AS [Line Code],
    QC.LINE_NM          AS [Line Name],
    PN.GUBUN            AS [Gubun],
    WO.WRK_QTY          AS [Work QTY],
    CM.LINE_TARGET      AS [Line Target],
    CM.RETURN_TARGET    AS [Return Target],
    SUM(COALESCE(PN.R_QTY, 0))                                   AS [Rework QTY],
    SUM(COALESCE(PN.S_QTY, 0))                                   AS [Scrap QTY],
    SUM(COALESCE(PN.UPRC_AMT, 0))                                AS [UPRC AMT],
    (SUM(COALESCE(PN.UPRC_AMT, 0) * COALESCE(PN.S_QTY, 0))
     + SUM(COALESCE(PN.R_QTY, 0)) * 3.8)                         AS [Cost]
FROM QC_LINE_MST AS QC
LEFT JOIN (
    SELECT 
        PE.LINE_CD, PE.WRK_YMD, PE.CUST_CD, PE.GUBUN, PE.ITMNO,
        PE.R_QTY, PE.S_QTY, ND.UPRC_AMT
    FROM PROC_ERR AS PE
    INNER JOIN (
        SELECT ITMNO, CUST_CD, UPRC_AMT FROM NOW_DANGA
    ) AS ND
      ON PE.ITMNO = ND.ITMNO AND PE.CUST_CD = ND.CUST_CD
    WHERE PE.WRK_YMD BETWEEN '20161116' AND '20161201'
      AND (PE.R_QTY <> 0 OR PE.S_QTY <> 0)
) AS PN
  ON QC.LINE_CD = PN.LINE_CD
LEFT JOIN (
    SELECT A.CODE, A.DSCP AS LINE_TARGET, B.DSCP AS RETURN_TARGET
    FROM COD_MST AS A
    INNER JOIN (
        SELECT CODE, DSCP FROM COD_MST WHERE GUBN='QC09'
    ) AS B
      ON A.CODE = B.CODE
    WHERE A.GUBN='QC08'
) AS CM
  ON QC.LINE_CD = CM.CODE
LEFT JOIN (
    SELECT LINE_CD, SUM(WRK_QTY) AS WRK_QTY
    FROM WRK_ORD
    WHERE WRK_YMD BETWEEN '20161116' AND '20161201'
    GROUP BY LINE_CD
) AS WO
  ON QC.LINE_CD = WO.LINE_CD
GROUP BY QC.LINE_CD, QC.LINE_NM, WO.WRK_QTY, PN.GUBUN, CM.LINE_TARGET, CM.RETURN_TARGET
ORDER BY QC.LINE_CD;

Binding the VB6 Code that Actually Works

I’m using ADO with a parameterized Command, opening a client-side static Recordset, and binding that to vaSpread’s DataSource. This is the part that makes the magic happen.

Option Explicit

' Assumes:
'  - Form has FarPoint Spread 6.0 control named SSList
'  - You already opened an ADODB.Connection named cn
'  - Project has reference to "Microsoft ActiveX Data Objects 2.x Library"

Private Sub LoadToSpread(ByVal d1 As Date, ByVal d2 As Date)
    Dim rs As ADODB.Recordset
    Dim cmd As ADODB.Command

    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandText = _
        "DECLARE @d1 char(8) = ?, @d2 char(8) = ?;" & vbCrLf & _
        "SELECT " & _
        " QC.LINE_CD AS [Line Code]," & _
        " QC.LINE_NM AS [Line Name]," & _
        " PN.GUBUN  AS [Gubun]," & _
        " WO.WRK_QTY AS [Work QTY]," & _
        " CM.LINE_TARGET AS [Line Target]," & _
        " CM.RETURN_TARGET AS [Return Target]," & _
        " SUM(COALESCE(PN.R_QTY,0)) AS [Rework QTY]," & _
        " SUM(COALESCE(PN.S_QTY,0)) AS [Scrap QTY]," & _
        " SUM(COALESCE(PN.UPRC_AMT,0)) AS [UPRC AMT]," & _
        " (SUM(COALESCE(PN.UPRC_AMT,0) * COALESCE(PN.S_QTY,0)) + SUM(COALESCE(PN.R_QTY,0)) * 3.8) AS [Cost]" & vbCrLf & _
        "FROM QC_LINE_MST AS QC" & vbCrLf & _
        "LEFT JOIN (" & vbCrLf & _
        " SELECT PE.LINE_CD, PE.WRK_YMD, PE.CUST_CD, PE.GUBUN, PE.ITMNO," & vbCrLf & _
        "        PE.R_QTY, PE.S_QTY, ND.UPRC_AMT" & vbCrLf & _
        " FROM PROC_ERR AS PE" & vbCrLf & _
        " INNER JOIN (SELECT ITMNO, CUST_CD, UPRC_AMT FROM NOW_DANGA) AS ND" & vbCrLf & _
        "    ON PE.ITMNO = ND.ITMNO AND PE.CUST_CD = ND.CUST_CD" & vbCrLf & _
        " WHERE PE.WRK_YMD BETWEEN @d1 AND @d2" & vbCrLf & _
        "   AND (PE.R_QTY <> 0 OR PE.S_QTY <> 0)" & vbCrLf & _
        ") AS PN ON QC.LINE_CD = PN.LINE_CD" & vbCrLf & _
        "LEFT JOIN (" & vbCrLf & _
        " SELECT A.CODE, A.DSCP AS LINE_TARGET, B.DSCP AS RETURN_TARGET" & vbCrLf & _
        " FROM COD_MST AS A" & vbCrLf & _
        " INNER JOIN (SELECT CODE, DSCP FROM COD_MST WHERE GUBN='QC09') AS B" & vbCrLf & _
        "    ON A.CODE = B.CODE" & vbCrLf & _
        " WHERE A.GUBN='QC08'" & vbCrLf & _
        ") AS CM ON QC.LINE_CD = CM.CODE" & vbCrLf & _
        "LEFT JOIN (" & vbCrLf & _
        " SELECT LINE_CD, SUM(WRK_QTY) AS WRK_QTY" & vbCrLf & _
        " FROM WRK_ORD" & vbCrLf & _
        " WHERE WRK_YMD BETWEEN @d1 AND @d2" & vbCrLf & _
        " GROUP BY LINE_CD" & vbCrLf & _
        ") AS WO ON QC.LINE_CD = WO.LINE_CD" & vbCrLf & _
        "GROUP BY QC.LINE_CD, QC.LINE_NM, WO.WRK_QTY, PN.GUBUN, CM.LINE_TARGET, CM.RETURN_TARGET" & vbCrLf & _
        "ORDER BY QC.LINE_CD;"

        ' Parameters: pass dates as YYYYMMDD CHAR(8)
        .Parameters.Append .CreateParameter("d1", adChar, adParamInput, 8, Format$(d1, "yyyymmdd"))
        .Parameters.Append .CreateParameter("d2", adChar, adParamInput, 8, Format$(d2, "yyyymmdd"))
    End With

    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient                 ' critical for data binding
    rs.Open cmd, , adOpenStatic, adLockReadOnly     ' static cursor for UI controls

    Set SSList.DataSource = rs                      ' bind to vaSpread

    SSList.ReDraw = False
    AutoSizeSpreadColumns SSList
    FormatNumericColumns SSList
    AppendTotalsRow SSList, Array("Rework QTY", "Scrap QTY", "UPRC AMT", "Cost")
    SSList.ReDraw = True

    ' keep rs alive while bound; don’t set Nothing yet
End Sub

Private Sub AutoSizeSpreadColumns(ByRef sp As vaSpread)
    Dim c As Integer
    For c = 1 To sp.MaxCols
        sp.Col = c
        sp.TypeHAlign = TypeHAlignLeft
        sp.ColWidth(c) = sp.GetTextExtent(c, True) + 2
    Next c
End Sub

Private Sub FormatNumericColumns(ByRef sp As vaSpread)
    Dim intCols As Variant: intCols = Array("Work QTY", "Rework QTY", "Scrap QTY")
    Dim moneyCols As Variant: moneyCols = Array("UPRC AMT", "Cost")

    Dim c As Integer, name As String
    For c = 1 To sp.MaxCols
        name = sp.ColHeader(c)
        If InArray(name, intCols) Then
            sp.Col = c
            sp.CellType = CellTypeNumber
            sp.TypeNumberDecPlaces = 0
        ElseIf InArray(name, moneyCols) Then
            sp.Col = c
            sp.CellType = CellTypeFloat
            sp.TypeFloatDecimalPlaces = 2
        End If
    Next c
End Sub

Private Sub AppendTotalsRow(ByRef sp As vaSpread, ByVal sumCols As Variant)
    Dim r As Long, c As Integer, name As String
    r = sp.MaxRows + 1
    sp.MaxRows = r
    sp.Row = r: sp.Col = 1: sp.Text = "TOTAL"

    For c = 1 To sp.MaxCols
        name = sp.ColHeader(c)
        If InArray(name, sumCols) Then
            sp.Row = r: sp.Col = c
            sp.Formula = "SUM(R2C" & c & ":R" & (r - 1) & "C" & c & ")"
            sp.FontBold = True
        End If
    Next c
End Sub

Private Function InArray(ByVal item As String, ByVal arr As Variant) As Boolean
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        If StrComp(CStr(arr(i)), item, vbTextCompare) = 0 Then InArray = True: Exit Function
    Next i
End Function

Private Sub Form_Load()
    ' Assume you opened cn earlier
    LoadToSpread #11/16/2016#, #12/01/2016#
End Sub

The Intentional Mistake I Made

I first used a forward-only server-side cursor because it’s the ADO default. It “worked” until the grid tried to repaint or count rows. Then it blew up with messages like “Rowset does not support fetching backward” or it just showed nothing. The fix is simple: set rs.CursorLocation = adUseClient and open with adOpenStatic. vaSpread expects a client-side static (or keyset) recordset so it can move around freely.

Make Dates User Selectable

I didn’t want to recompile just to change the date range. I added a small handler that reads two text boxes or DTPickers and reloads the grid.

Private Sub cmdRefresh_Click()
    Dim d1 As Date, d2 As Date
    d1 = CDate(txtFrom.Text)   ' or DTPickerFrom.Value
    d2 = CDate(txtTo.Text)     ' or DTPickerTo.Value
    LoadToSpread d1, d2
End Sub

Export the Spread to Excel

Users always ask for Excel. If your vaSpread build exposes an Excel exporter, this one-liner pays for itself.

Private Sub cmdExport_Click()
    Dim ok As Integer
    ok = SSList.ExportToExcel(App.Path & "\Report.xlsx", True)
    If ok = 0 Then
        MsgBox "Exported to " & App.Path & "\Report.xlsx"
    Else
        MsgBox "Export failed. Code: " & ok
    End If
End Sub

Color High Cost Rows Automatically

A little conditional formatting makes “spiky” data pop without a single extra query.

Private Sub ShadeHighCostRows(ByRef sp As vaSpread, ByVal threshold As Double)
    Dim r As Long, cCost As Integer, costVal As Double

    cCost = FindColumn(sp, "Cost")
    If cCost = 0 Then Exit Sub

    For r = 2 To sp.MaxRows
        sp.Row = r: sp.Col = cCost
        costVal = Val(sp.Text)
        If costVal > threshold Then
            sp.Row = r
            sp.BlockMode = True
            sp.Col = 1: sp.Col2 = sp.MaxCols
            sp.BackColor = RGB(255, 240, 240)
            sp.BlockMode = False
        End If
    Next r
End Sub

Private Function FindColumn(ByRef sp As vaSpread, ByVal header As String) As Integer
    Dim c As Integer
    For c = 1 To sp.MaxCols
        If StrComp(sp.ColHeader(c), header, vbTextCompare) = 0 Then FindColumn = c: Exit Function
    Next c
End Function

This tiny handler makes the grid feel less “VB6” and more like a modern table.

Private Sub SSList_KeyDown(KeyCode As Integer, Shift As Integer)
    If (Shift And vbCtrlMask) <> 0 And KeyCode = vbKeyC Then
        Clipboard.Clear
        Clipboard.SetText SSList.Text
    End If
End Sub

A Few Small SQL Wins I Applied

I swapped single-quoted aliases for bracketed aliases because single quotes are string literals, not names. I wrapped every arithmetic input in COALESCE to avoid nulls wrecking totals. And I checked indexes so the joins didn’t crawl. If you can, add or confirm these: PROC_ERR(WRK_YMD, LINE_CD) INCLUDE(R_QTY,S_QTY,CUST_CD,ITMNO,GUBUN), NOW_DANGA(ITMNO, CUST_CD) INCLUDE(UPRC_AMT), WRK_ORD(WRK_YMD, LINE_CD) INCLUDE(WRK_QTY), and COD_MST(GUBN, CODE) INCLUDE(DSCP).

Small Touches I’m Glad I Added:

• Autosized columns with GetTextExtent so headers don’t get chopped.
• Formatted quantities as whole numbers and money as two decimals.
• Calculated a totals row using Spread formulas (not VB code) so totals stay correct after sorting/filtering.
• Set left alignment for text columns to improve readability.
• Kept UI snappy by wrapping changes with SSList.ReDraw = False/True.

Troubleshooting Things That Bit:

• Blank grid? Ensure the recordset is open, client-side (adUseClient), and static (adOpenStatic).
• Seeing (No Rowset) in headers? Verify the query actually returns rows for the chosen dates.
• Using real DATE columns? Pass typed DATE parameters—don’t force CHAR(8) unless your schema stores YYYYMMDD.
• Mixed nulls in math? Wrap numeric fields in COALESCE(...) to prevent null-propagation.
• Nothing appears after binding? Don’t Set rs = Nothing while the grid is still bound.

Final Thought

Yes, you can populate a FarPoint vaSpread 6.0 control straight from a SQL query in VB6. The keys are a parameterized ADO command, a client-side static recordset, and a few tiny UI helpers. I’ve shown you the working code, the trap to avoid, and a handful of upgrades that make the result feel modern. If you were stuck like I was, I hope this saves you a few hours and at least one coffee.