How to backup Access tables

I am using Access 2013 that connects to a cloud based SQL server. The tables that populate are set to only retain 13 months worth of data. I am looking for a way to backup these tables so I do not loose this older data. In 2018 I would like to have the option to query data from 2016 so I would need a way to store this locally.
Cole100IT Systems ManagerAsked:
Who is Participating?
 
Helen FeddemaConnect With a Mentor Commented:
See my Access Archon articles on archiving data, using two different methods:

accarch207.zipaccarch209.zip
0
 
PatHartmanConnect With a Mentor Commented:
You need to determine what the purge schedule is.  Then you would run an append query that selects the data about to be purged and append it to your local history table.

It is far preferable that the archiving be handled by the server so you are not in jeopardy of missing your window or of missing updates if you archive too early.

Once you are archived it, you have a whole other set of problems trying to use it since it will not be in the table with the current data.  It will require separate queries to union the two sets of data at a minimum.
0
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
to make it easier to do as Pat suggested, add tracking fields to all your tables:

dtmAdd, date/time -- default value = Now()
dtmEdit, date/time -- default value = Now() -- update this on BeforeUpdate event of forms
0
 
Gustav BrockConnect With a Mentor CIOCommented:
As the data in your main table are supposed to be updated, you must run a combined append/update query to maintain your backup table. This can be done at any time.

Here is how to create such a query:
Combined update or append query

As it doesn't delete anything, it will leave the old records in your backup table untouched.

/gustav
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
poster didn't come back
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.