Microsoft Access - Archiving old data

Hi, 

I attached an Excel File name "Test Run" and an Access file named "Test Run" 
I have the files link. When I update columns AH Through BA in Excel to Access I want the new info to update, but I want the old info archive to retrieve and review in a query. Columns AH Through BA should be link to Column "A" in Excel (Group ID) 

Desperate help needed. Thanks. Test Run Database.zip

Some years ago, Microsoft lost a lawsuit and as a result had to remove the ability of Access to directly update linked spreadsheets as you can update other linked data sources.  You can still update spreadsheets but now you need to do it with OLE automation code in VBA.

When you link a spreadsheet to Access, there is only a single copy of the data and that is the spreadsheet.  Access is merely looking at it.

If you are doing the update in Excel and you want the Excel changes to drive what happens in Access, then you need to write update or append queries in Excel and run them to update the Access table which you haven't defined.  You'll also need to create macros in the workbook to make sure this happens when it should.  I don't work with Excel so I don't know how to make a macro run but you need to make the macro run whenever a change happens to the columns you are keeping history for.

Hi Pat, 

I truly appreciate the feedback response. After researching, I did determine that Access could not be link to Excel for continuous update, therefore in turn I created a separate database with the appropriate fields. I also included macros to run with the database to update the data and a few other tricks I added in. I'm a Registered Nurse Inspector for the state of Pennsylvania and I was a Consultant for a major Insurance company. I have a thorough backgroung in Excel and Access but sometimes I still come across a brick wall and I'm glad there are wonderful people such as yourself to reach out to just in case. I thank you again. 

Thank you.  I'm happy you worked it out.