CODEWITHSUNDEEP
sqlexcelvbams-accessadodb
Edwin
Edwin

Reputation: 13

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

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

Upvotes: 1

Views: 1354

Answers (1)

Heinzi
Heinzi

Reputation: 172270

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.

Upvotes: 1

Related Questions