Merging data in multiple linked excel tables into a master Access table

Question

I have multiple Excel sheets with exactly the same headers that I have linked into Access. I am having an issue with merging all the data from the excel files into one master Access table while keeping the master Access table updated when people edit the information in the Excel sheets. Normally a query will be used, but can the Access database update the data by itself?

Answer 1

The easiest method I can suggest you is Update Query.

Since your Excel sheets are identical to Access Database Table then Update Query will work faster and your Excel sheet will appear like any attached table in Access.

I do hope you don't have blank row between the headers and the data in Excel.

Other solution is VBA Code, you can try this.

Private Sub UpdateAccess()

Dim db As Database
Dim rs As RecordSet
Dim sql As String
Dim dbLocation As String

dbLocation = Workbook.Path & "\database\data.accdb"
Set db = OpenDatabase(dbLocation)
sql = "Select * FROM [Main Table]"
Set rs = db.OpenRecordSet(sql, dbOpenSnapshot)

If Not rs.EOF Then
   Worksheets("Sheet1").Range("A1").CopyFromRecordset rs
End If

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

NB: This VBA code will work successfully when merging is unconditional.