How to update existing data in Access database using Excel front-end?

Frontend
1

Hey everyone! I’m working on a project where I’m using Excel as a front-end for an Access database to manage shifts and activities. I’ve got the data import working fine, but now I’m stuck on how to update existing records in the Access table.

Here’s a snippet of my current VBA code for importing data:

Sub ImportNewData()
  Dim db As New ADODB.Connection
  Dim sql As String
  Dim dataSheet As Worksheet
  
  Set dataSheet = Worksheets("Schedule")
  
  For row = 2 To dataSheet.UsedRange.Rows.Count
    For col = 2 To dataSheet.UsedRange.Columns.Count
      date = dataSheet.Range("A1").Value
      activity = dataSheet.Cells(1, col).Value
      employee = dataSheet.Cells(row, 1).Value
      slot = dataSheet.Cells(row, col).Value
      
      If slot <> "" Then
        sql = "INSERT INTO schedule_table (Date, Employee, Slot, Activity) VALUES ('" & date & "', '" & employee & "', '" & slot & "', '" & activity & "')"
        db.Execute sql
      End If
    Next col
  Next row
End Sub

Any tips on how I can modify this to update existing records instead of just inserting new ones? Thanks in advance for your help!

2

hey, have u tried checking if the record exists before inserting? sometimes using a dlookup in access can help decide whether to update or insert. what kind of shifts are u managing? thanks for sharing ur project!

3

To update existing records, you need to modify your approach away from simply inserting new rows. Begin by checking if a record exists based on a unique combination of fields, for example Date, Employee, and Activity. If a record is found, perform an update; otherwise, insert the new record. This method helps to prevent duplicate entries and improves data consistency. Implementing this logic within a stored procedure or similar mechanism in Access may also increase efficiency and overall system performance.

4

yo, have u considered using an UPSERT approach? basically, u try to update first, and if no rows are affected, u do an insert. might need to tweak ur sql a bit, but it could solve ur problem. wat kinda activities r u tracking btw?