Excel VBA to: APPEND only modified records from Excel to Access

Question

I have an excel spreadsheet which downloads the most up-to-date record set from a database via ADODB connection. Most up-to-date records are base on the import date/time. Once the information has been loaded onto the Excel Spreadsheet, I want the analyst/end-user to update any record as they see fit. When the analyst click the submit button, I only want the modified record(s) to be APPEND into the same table in MS Access 2007 database, which sits on a share drive. The database will automatically add an import date/time for each record.

My problem: I want to only APPEND the newly modified records. Thus I will have a historical log of what was updated and when. Currently I am appending the entire record set to MS Access 2007 while including today’s date/time stamp, this is causing redundant records with different timestamps.

The code below represents my current process. Please note I have public variables.

Private Sub db_CodeAmendment_Upload

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim i, j, rw, co As Long
    Dim sh As Worksheet
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset

    Sheets("sCodeMatrix").Visible = True

    ThisWorkbook.Worksheets("sCodeMatrix").AutoFilter.Sort.SortFields.Clear

        For Each sh In Worksheets
            If sh.Name <> "sCodeMatrix" Then sh.Visible = xlSheetHidden
        Next

    Sheets("sCodeMatrix").Select
    Sheets("sCodeMatrix").Activate

    rw = Range("A500").End(xlUp).Row
    co = Range("CZ1").End(xlToLeft).Column


    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open TARGET_DB
    End With

    rst.CursorLocation = adUseServer
    rst.Open Source:="tblCodeMatrix", ActiveConnection:=cnn, CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdTable

    'Load all records from Excel to Access.'
    For i = 2 To rw
        rst.AddNew
        For j = 1 To co
            rst(Cells(1, j).Value) = Cells(i, j).Value
        Next j
        rst.Update
    Next i

    ' Close the connection '
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

End Sub

Heinzi · Accepted Answer · 2014-07-09 16:54:14Z

1

You don't have a specific question in your question, so I assume that you are asking for general guidance on how to do this. I would suggest the following algorithm:

Loop through your spreadsheet rows
- Detect if the the record has been changed.
- If it has been changed:
  - Add it as a new record to Access.

A few options come to mind for determining whether a record has changed:

Query the record from the Access database and compare it field by field, or
when inserting the data to the Excel sheet, import it twice (once into the visible area and once into some hidden columns further out to the right). This allows you to compare Excel cell to determine whether something was changed.

answered Jul 9, 2014 at 16:54

Heinzi

173k61 gold badges386 silver badges555 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fionnuala Over a year ago

It is easy enough to link an Excel spreadsheet to Access, as long as the data is organized. Comparing fields / columns then becomes a matter of simple queries.

Edwin Over a year ago

@Heinzi , thanks for the insight… can you provide a generic code how to do the loop and detect the changes? Something high-level so I can work off of. I was thinking concatenating all the columns on both end and do a dcount; when the count is zero, I would append that record. But I don’t know how to even start coding that.

Edwin Over a year ago

@Remou, you bring a valid point… but this spreadsheet is distributed to multiple users. When a spreadsheet is open with a link, it will lock database. Therefore the other users are unable to push updates.

Collectives™ on Stack Overflow

Excel VBA to: APPEND only modified records from Excel to Access

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related