Archiving tables with Access frontend and linked SQL Server

Question

I'm working on a project where the user has an Access 2013 MDB database with forms, reports, and queries. We're converting it to use linked tables to SQL Server 2008 R2. All the linked tables seem to be working well.

The user needs the ability to archive some tables annually, and be able to read the data. These are not particularly large tables (the whole MDB is 27MB). Currently, the users make a copy of the MDB file every year and use that as their archive. They need the old data for comparison with the current information.

How do you archive linked tables via Access? It's not feasible to copy the entire SQL Server schema every year. Maybe if we just back up the tables and link again? What's the recommended approach to this? Thanks.

Answer 1

Try creating a separate historical file with nothing but the archived data table. In the example below you can see there is a linked table. I imagine your linked SQL table appears similar but pictured as a blue globe and is connected. Right click the linked table and select Convert to Local Table. This will sever the link and create a local table. Name it [tblYourTable_2016] for example.

OR

You could create a Make Table Query and recreate it that way as well. Either way I would keep the history files in a separate and secure file.

[]