Update Rows Automatically Based on another Table

Hi Everyone!

Here's the scenario.  I have an existing SQL SERVER 2005 Database using a C#.Net App Frontend. The system has been running 24/7 for 2 years now.  But the development team has been halted and there's no way to update the source code.  Now, there's a change we want to make on the database so we decided to make an app in VB6, which is I'm comfortable with.

We want to add Character without space columns in one table because we need it.  The column on the existing database is Character with space only.

Now what I did was create another table and put the IDs and the CharWithoutSpace, then by joining the two tables we can get what we want.

Here's the code of the Linecout Updater:

Private Sub Command1_Click()
Dim stm As ADODB.Stream
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim rs3 As ADODB.Recordset
Dim objWord As Word.Application
Dim VBC As String

Dim SQL As String
Dim SQL2 As String
Dim SQL3 As String
Dim x As Long

    
    Set objWord = New Word.Application
    Call Open_Connection
 
    SQL = "SELECT DocumentDetailID FROM DocumentDetail"
       
    Set rs = New ADODB.Recordset
    rs.Open SQL, con, adOpenKeyset, adLockOptimistic

    Set stm = New ADODB.Stream
    stm.Type = adTypeBinary
    stm.Open
        
    For x = 1 To rs.RecordCount

        SQL3 = "SELECT DocumentDetailID, DocumentID, FinalDocument FROM DocumentDetail " & _
               "WHERE DocumentDetailID = " & rs.Fields("DocumentDetailID")
        
        Set rs3 = New ADODB.Recordset
        rs3.Open SQL3, con, adOpenKeyset, adLockOptimistic
          
        With stm
            .Write rs3.Fields("FinalDocument").Value
            .SaveToFile App.Path & "\Temp.doc", adSaveCreateOverWrite
        End With
        
        objWord.Documents.Open App.Path & "\Temp.doc"
        VBC = objWord.ActiveDocument.ComputeStatistics(wdStatisticCharacters)
        
        SQL2 = "INSERT INTO VBCTable (DocumentDetailID, DocumentID, VBC) VALUES ('" & rs3.Fields("DocumentDetailID").Value & "', '" & rs3.Fields("DocumentID").Value & "', '" & VBC & "')"
        
        Set rs2 = con.Execute(SQL2)
        Set rs2 = Nothing
        objWord.ActiveDocument.Close False
        
        Set rs3 = Nothing
        
        Label1.Caption = x & " of " & rs.RecordCount
toMoveNext:
        rs.MoveNext
    Next
    
    objWord.Quit
    rs.Close
    Set rs = Nothing
    con.Close
End Sub

Select all Open in new window

NOTE:
DocumentDetailID = Primary Key
DocumentID = int
FinalDocument = Binary (Word File)

The code simply loop all rows for DocumentDetailID then open corresponding Binary file using word object, getting the correct char with no spaces linecount then inserting it on VBCTable.

I called it VBCTable since VBC stands for Visible Black Characters.  It's also synonymous to Character without spaces.

Now my problem is I want to create an app that will automatically update all values in real time.
Can somebody help me.  I already started the code can someone modify it so that it will update values automatically. Use like a timer like update every 30 minutes something like that.  In the end this VB APP will run 24/7 also.

Is this the right approach to this or is there any other simple way to do this like script or something.

Any help is greatly appreciated.