Track changes and update them to databank

Question

I have an Excel Workbook. There are 2 sheets at the moment. 1 sheet sends rows to an Access DB, the other tab displays all rows from this DB. I would like to be able to update the DB according to changes in this last sheet.

I've done some digging and seen an option to track changes by making it a shared workbook and enabling the built-in feature of tracking changes. I would want to keep the workbook only able to edit by 1 user at a time. (Other users might not get the most recent rows otherwise)

Updating the entire worksheet is not an option, since cells might be edited without realising it. I was thinking of only updating a certain column ,but the problem stays the same, only smaller.

My most positive scenario would be that excel would track the changes, only update those and maybe even highlight the changes before sending them to the DB.

Is this a possibility to do in VBA?

I would either add the worksheet to the database as a linked table or use the Worksheet_Change(ByVal Target As Range) event to update the database using an ADO query. — user6432984
– user6432984, Commented Jul 19, 2016 at 7:10

KyloRen · Accepted Answer · 2016-07-19 07:10:22Z

My approach to something like this would be to have an event fire each time you change data. Then have that data inserted into the database. If the database is located locally, performance will not be an issue.

Something like below, you would have to amend the range to suit yours and also have to deal with different data types via some if statements. This is just some sample code as I have no idea what kind of data you are trying to update and it only deals with two data types.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strSQL As String

        If Not Intersect(Target, Range("A1:Z100")) Is Nothing Then
           If IsNumeric(Target.Value) Then
              strSQL = "INSERT INTO MyTable (Field_1) VALUES (" & Target.Value & ")"
           Else
              strSQL = "INSERT INTO MyTable (Field_1) VALUES ('" & Target.Value & "')"
           End If
        End If


End Sub

Collectives™ on Stack Overflow

Track changes and update them to databank

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related