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.

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