Updateable Excel Frontend for SQL View

I am currently working on a Web UI: https://tickett.wordpress.com/2014/12/11/excel-like-web-frontend-to-sql-viewtablestored-procedure/

We often have a need to view/manipulate/analyse data from systems in Excel (because, let’s face it- it’s quick & easy). However, it is sometimes the case that we need to add data (it may be commentary, budget figures or any manner of data that for one reason or another cannot be keyed into the underlying system(s)). I hoped Excel’s built in functionality could cater for this… Unfortunately, while it’s very easy to set a datasource to pull data from SQL it doesn’t appear to have any functionality for pushing data back into the underlying database.

So this is what I came up with…

Step 1: The data is retrieved using VBA (I have a master sheet which is pre-formatted). The “template sheet” is then copied and used as a starting point each time the data is retrieved:

Sub RetreiveSheet()
    On Error GoTo Error
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stSQL As String

    Const stADO As String = "Provider=SQLOLEDB.1;User ID=User;Password=Password;Persist Security Info=False;" & _
    "Initial Catalog=EBMS1920;Data Source=sql-01"

    stSQL = "SELECT * FROM FORECAST_SPREADSHEET WHERE CRITERIA = 1"
    stSQL = stSQL & vbCrLf & "ORDER BY [Date] ASC"
    Set cnt = New ADODB.Connection

    With cnt
        .CursorLocation = adUseClient
        .Open stADO
        .CommandTimeout = 0
        Set rst = .Execute(stSQL)
    End With

    Application.EnableEvents = False

    For i = 0 To rst.Fields.Count - 1
        ActiveSheet.Cells(1, i + 1) = rst.Fields(i).Name
    Next i

    Application.ScreenUpdating = False
    For r = 1 To rst.RecordCount
     For i = 0 To rst.Fields.Count - 1
        ActiveSheet.Cells(r + 1, i + 1) = rst.Fields(i).Value
     Next i
     rst.MoveNext
    Next r

    Application.ScreenUpdating = True

    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing

    ActiveSheet.PivotTables(1).RefreshTable
    ActiveSheet.Range("B1000").Value = "Last Retreived: " & DateTime.Now()

    Application.EnableEvents = True
    ActiveSheet.Protect UserInterfaceOnly:=True

    Exit Sub

Error:
    MsgBox ("An error occurred")
End Sub

Here is an example with some made up data:

The gray cells indicate cells that are pulling from the system and CANNOT be updated. The pink cells are pulling from my custom table and CAN be updated, and the yellow cells are summary fields (a pivottable). All cells are locked except those in pink and the sheet is protected.

The SQL query is pulling from FORECAST_SPREADSHEET which is a view joining tables from the underlying system with my custom table FORECAST_DATA.

Now the clever bit… allowing updates to write back to the database!

Step 2: We create a trigger on our view:

ALTER TRIGGER [dbo].[tr_UPDATE_FORECAST]
   ON [dbo].[FORECAST_SPREADSHEET]
   INSTEAD OF UPDATE
AS
BEGIN
      SET NOCOUNT ON;
      DELETE A FROM FORECAST_DATA A INNER JOIN INSERTED B ON B.ORG_CODE = A.ORG_CODE AND B.ID = A.EVENT_ID
      INSERT INTO FORECAST_DATA SELECT
       ORG_CODE
      ,ID
      ,ADD_SERVICE_INCOME
      ,TOTAL_CATERING_EXPENSES
      FROM INSERTED
END

Step 3: We add a routine to the “worksheet_change” event:

Private Sub Worksheet_Change(ByVal Target As Range)

If Application.ScreenUpdating = False Then Exit Sub
If Target.Value <> "" And IsNumeric(Target.Value) = False Then
Target.Value = ""
MsgBox ("Value must be numeric!")
Exit Sub
End If

Dim updateSQL As String
Dim orgCode As String
Dim eventID As String
Dim columnName As String
Dim newValue As String

orgCode = Range("A" & Target.Row).Value
If orgCode = "" Then Exit Sub
eventID = Range("D" & Target.Row).Value
columnName = Cells("1", Target.Column).Value
newValue = IIf(Target.Value = "", "NULL", Target.Value)

updateSQL = "UPDATE FORECAST_SPREADSHEET SET " & columnName & " = " & newValue & " WHERE ORG_CODE = '" & orgCode & "' AND ID = " & eventID

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset

Const stADO As String = "Provider=SQLOLEDB.1;User ID=User;Password=Password;Persist Security Info=False;" & _
"Initial Catalog=EBMS1920;Data Source=sql-01"

Set cnt = New ADODB.Connection

With cnt
    .CursorLocation = adUseClient
    .Open stADO
    .CommandTimeout = 0
    Set rst = .Execute(updateSQL)
End With

selectSQL = "SELECT * FROM FORECAST_SPREADSHEET WHERE ORG_CODE = '" & orgCode & "' AND ID = " & eventID

Set rst = cnt.Execute(selectSQL)
Application.EnableEvents = False
For i = 0 To rst.Fields.Count - 1
ActiveSheet.Cells(Target.Row, i + 1) = rst.Fields(i).Value
Next i
Application.EnableEvents = True

cnt.Close
Set rst = Nothing
Set cnt = Nothing

ActiveSheet.PivotTables(1).RefreshTable

End Sub

You will notice in the code used to retrieve the data earlier that I set Application.EnableEvents = False to prevent this event being fired when my code is executing (I only want it to fire when the user is keying in data).

We’re done! RetreiveSheet() can be executed to retrieve the data and keying into the pink cells will update the custom SQL table in real-time! I chose to retrieve the latest copy of an entire row everytime an update is committed to the database.

Sidenote: One trick I learnt whilst building this solution was how to avoid cell formula references being automatically updated (broken) when the sheet to which they refer is no longer valid (deleted). We do this using the INDIRECT() function. So =SUM(’26_201109′!covers) becomes =SUM(INDIRECT(“’26_201109′!covers”))

L