Solved

How to backup Access tables

Posted on 2016-09-09
5
47 Views
Last Modified: 2016-10-07
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.
0
Comment
Question by:Cole100
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 37

Assisted Solution

by:PatHartman
PatHartman earned 83 total points (awarded by participants)
ID: 41792028
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
 
LVL 20

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 83 total points (awarded by participants)
ID: 41792051
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
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 83 total points (awarded by participants)
ID: 41792296
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
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 251 total points (awarded by participants)
ID: 41792620
See my Access Archon articles on archiving data, using two different methods:

accarch207.zipaccarch209.zip
0
 
LVL 20
ID: 41833325
poster didn't come back
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question