I have an Access database which I have inherited from a previous colleague. My issue is with transferring data from an Excel Spreadsheet into the access database (Local Table).
Limitations / issues I have are as follows
1) The Excel spreadsheet (Source Data) is read-only and cannot be modified by me.
2) The top two rows in the Excel spreadsheet contains merged cells, thus column headers can be contained in either rows 1 or 2
3) Owner of the spreadsheet constantly modifies the structure i.e. deleting and adding columns
I have tried a few different options
1) Creating a linked table and then running an update Query to update a Local Table (Structure of data i.e. columns changing presents issues.)
2) My colleague setup a VBA option which utilizes the DoCmd.TransferSpreadsheet option which unfortunately suffers from the same issues as the above method.
I am thinking that a solution may be to match the local Headers in my local table to the column headers (Rows 1 & 2) in the Source spreadsheet, however I am unsure how to implement this.
I am open to any suggestions / solutions that you guys may have.
Any help is greatly appreaciated