SQL Update Scripts

SQL Update Scripts

In the absence of a UI for a given table, updates to records on that table need to be executed as SQL Update Scripts. This process would usually involve some development resource to create the script under the instruction of a given user.

The obvious solution would be to add a UI to allow the users to maintain the data and add any four eye approval process required. But in this particular case that was deemed a low priority and the process of identifying the records and data to be updated reverted to the users.

This was a very arduous process which needed simplification. This involved the automating the following steps into a single access application to allow the user to get the data from oracle change the fields in any given record and create an SQL script to update the database.

  • Create the table definition as defined in Oracle
  • Populate the current Oracle data into that table
  • Add a UI to allow the user to navigate the records
  • Allow the user to change a record and generate a script based on the changes

This process has been broken down into three posts

The table structures and data population is covered in this post.

The adding of multiple UI’s is covered by this post

The details below cover the generation of an SQL Script to execute on the Oracle DB.

SQL Update Scripts

On each user interface we have a event which is called “on Current”. This event call a function SetTagValue , which save the current value for the selected record to the tag value for the field.

Public Sub SetTagValue(frm As Form)
On Error Resume Next
Dim ctl As control, setColour, rst As Recordset, tmpTag
tmpTag = Split(frm.Tag, ";")
setColour = 12713215
Set rst = CurrentDb.OpenRecordset("select * from tblEnabledControls where formname='" & frm.Name & "'")
If rst.RecordCount = 0 Then
    GoTo SkipCheck
End If
rst.MoveFirst
Do While Not rst.EOF
    For Each ctl In frm
            If ctl.Name = rst!FieldNameonForm Then '
                If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
                    ctl.Tag = ctl.Value
                End If
            End If
    Next ctl
    rst.MoveNext
Loop
SkipCheck:

Set rst = Nothing
Set ctl = Nothing
End Sub

Any control on the form which wasn’t enabled could not be changed by the user. The option to define enabled controls was set at the time the form was designed by the developer.

I used the forms events (Update) to call the following function which saves the changes to the given field in a table. The code will first examine the table associated with the form and ensure we have a defined key field and then reviews each of the values for the form against the controls tag value. If the value had changed the change was written to the update script table.

For a given form I also added the table name to the tag value of the form. While using Peter’s software i found that the tag was also used to record form information and had to add split the tag to extract the form name – tmpTag = Split(frm.Tag, “;”).

SQL Update Scripts – Record Changes
Public Sub WriteRecordChanges(frm As Form)
On Error Resume Next
'SQl update scripts
Dim ctl As control, rst As Recordset, tmpKeyField As String, tmpTag
'key the key Field
tmpKeyField = DLookup("[FormKeyField]", "tblForms", "[Formname]='" & frm.Name & "'")
If Nz(tmpKeyField, "") = "" Then
    MsgBox "Key Column not defined in the Forms table"
    Exit Sub
End If
Set rst = CurrentDb.OpenRecordset("select * from tblEnabledControls where formname='" & frm.Name & "'")
If rst.RecordCount = 0 Then
    Exit Sub
End If
rst.MoveFirst
tmpTag = Split(frm.Tag, ";")
Do While Not rst.EOF
    For Each ctl In frm
            If ctl.Name = rst!FieldNameonForm Then '
                If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
                    If InStr(1, ctl.Controls(0).Caption, "(dbl)") > 0 Then
                        If CDbl(ctl.Tag) <> ctl.Value Then ' it has changed
                            Call WriteChange(tmpTag(0), ctl.Name, ctl.Tag, ctl.Value, frm(tmpKeyField).Value, tmpKeyField)
                        End If
                    ElseIf InStr(1, ctl.Controls(0).Caption, "(lng)") Then
                        If CLng(ctl.Tag) <> ctl.Value Then ' it has changed
                            Call WriteChange(tmpTag(0), ctl.Name, ctl.Tag, ctl.Value, frm(tmpKeyField).Value, tmpKeyField)
                        End If
                    Else
                        If ctl.Tag <> ctl.Value Then ' it has changed
                            Call WriteChange(tmpTag(0), ctl.Name, ctl.Tag, ctl.Value, frm(tmpKeyField).Value, tmpKeyField)
                        End If
                    End If
                End If
                GoTo Skip
            End If
    Next ctl
Skip:
    rst.MoveNext
Loop
Set rst = Nothing
Set ctl = Nothing
End Sub

Creating the SQL Update Scripts

When the user had finished making any required changes to any tables. They could run the Script function to extract the update scripts to a single file. Assign a JIRA ticket and approval before passing the ticket to Development to execute.

Private Sub cmdExport_Click()
'exports sql update scripts
Dim fso As FileSystemObject
Dim textFile As TextStream, rst As Recordset
Set fso = New FileSystemObject
If Dir(Me.txtExportPath, vbDirectory) = "" Then
    MsgBox "Export path does not exist"
    Exit Sub
End If
Set textFile = fso.CreateTextFile(Me.txtExportPath & "SQL_Updates_" & Format(Now(), "YYYY_MM_DD_HHNNSS") & ".sql", True)

If Nz(Me.cboTable, "") <> "" Then
    Set rst = CurrentDb.OpenRecordset("Select * from tblScripts where S_Exported=0 and S_TableID='" & Me.cboTable & "' order by S_ID ASC")
Else
    Set rst = CurrentDb.OpenRecordset("Select * from tblScripts where S_Exported=0 order by S_ID ASC")
End If
If rst.RecordCount = 0 Then
    textFile.WriteLine "-- No records to write for this file ?"
    GoTo SkipWrite
Else
    rst.MoveLast
    textFile.WriteLine "/* SQL Update Statement"
    textFile.WriteLine "   Environment :" & IIf(Me.selEnviron = 1, "Production", "UAT")
    textFile.WriteLine "   " & rst.RecordCount & " Changes to Execute "
    textFile.WriteLine "   User:" & Environ("Username")
    textFile.WriteLine "   Exported from PC:" & Environ("Computername")
    textFile.WriteLine "   Exported Date/Time :" & Format(Now(), "YYYY-MMM-DD HH:NN:SS")
    textFile.WriteLine "   -----------------------------------------------------------"
    textFile.WriteLine "   Approved   By:"
    textFile.WriteLine "   Approved Date:"
    textFile.WriteLine "   -----------------------------------------------------------"
    textFile.WriteLine "*/"
    rst.MoveFirst
End If

Do While Not rst.EOF
    textFile.WriteLine "Update " & rst!S_TableID & " Set " & rst!S_Field & "='" & rst!S_ToValue & "' Where " & rst!S_KeyFieldName & "=" & rst!S_Keyid & ";"
    rst.Edit
    rst!S_Exported = -1
    rst!S_ExportDate = Date
    rst.Update
    rst.MoveNext
Loop
textFile.WriteLine "commit;"
SkipWrite:
textFile.Close

MsgBox "Update Scripts Created"
Me.lstScripts.Requery


Set rst = Nothing
Set textFile = Nothing
Set fso = Nothing
End Sub

,

Leave a Reply

Your email address will not be published. Required fields are marked *