Updating table in Access from Excel VBA

Question

I have two tables in an Excel file which I need to import to Access and I have found a way that works perfectly for that, the problem is that it only works the first time (to import the table when this one does not exist in Access) but when I make changes to the Excel file and try this method again, it does not update the records and basically does nothing.

Both the table in Excel and in Access are called "Messungen" and "Grundinformation", respectively. This is the implemented code for the module called ExcelImport:

Public Sub ImportExcelSpreadsheet(fileName As String, tableName As String)

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Messungen", _
     fileName, True, "Messung!"
     
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Grundinformation", _
     fileName, True, "Grundinformation!"
     
    Exit Sub

End Sub

I have made also the following objects. The first one to browse and select the file from a folder:

Private Sub btnBrowse_Click()
    Dim diag As Office.FileDialog
    Dim item As Variant
    
    Set diag = Application.FileDialog(msoFileDialogFilePicker)
    diag.AllowMultiSelect = False
    diag.Title = "Bitte die Excel Datei 'DB_Access_Daten' wählen "
    diag.Filters.Clear
    diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, *.xlsm"
    
    If diag.Show Then
        
        For Each item In diag.SelectedItems
            Me.ExcelDatei = item
        
        Next
    End If
    
End Sub

And this one to import the table using a button:

Private Sub btnImportTabelle_Click()
    Dim FSO As New FileSystemObject
    
    If Nz(Me.ExcelDatei, "") = "" Then
        MsgBox "Bitte eine Datei auswählen"
        Exit Sub
        End If
        
    
    If FSO.FileExists(Nz(Me.ExcelDatei, "")) Then
        ExcelImport.ImportExcelSpreadsheet Me.ExcelDatei, FSO.GetFileName(Me.ExcelDatei)
        
    Else
        MsgBox "File not found"
    
    End If
    
End Sub

As mentioned before, this unfortunately is not working to update the values, just to import the table for the first time. I have thought of just linking the Database to the table in Excel but had some trouble with that since the excel file is in a shared network and my disk drive letter varies from the ones from my colleagues. I do not know then if maybe something is wrong with the code and how could I fix it.

Thank you for any help in advance!

Answer 1

You should link the spreadsheets. Then run an update/append query as shown here:

Update and Append Records with One Query