Backing up Tables

[Guest]

I need to backup a table with its name and the current date attatched to the
name so it might look like <Cust Code 1/18/06>. How would I do that.

Follow up question: I also need the tables that have been created 10 days
past to be deleted. How do I do that?

 

[Guest]

Alternately, is there a way to get it to export to excel using the same
naming criteria?

 

[Ron2005]

Are you really trying to back up tables or rather trying to backup data
within the tables.
Tables do not have a creation date as such unless you add a field to
the data to indicate the date you created the table and added the data.

If you can write a query to see the data, then that query can be used
as the source for exporting the data to a spreadsheet with a name of
your choosing.

Ron

 

[Guest]

I am really trying to back up tables (or all the data in if for a day). I
need the table to be backed up because it will be deleted, and repopulated
with new data at the beginning of each day. I need to keep a historical
record of at least a week before the data gets removed permamently. Due to
the existing design I can not just populate the new data into the existing
records as each are unique.

If I use a Query I still need it to have a name and attach the date to it
because it will run automatically. Whomever is on the database is not
supposed to name tables or spreadsheets. That is why it is to be automatic.

 

[Ron2005]

Is the table name(s) that you are backing up always going to be "Cust
Code". My guess is yes.

Trying to really put the date on the file can be done, BUT then trying
to guess the name for a file 10 days (which may be 11 because of a
holiday etc) will get tricky and error prown.

If you name the backups simply as "Cust Code 1" and "Cust Code 2" on
back to 10 it will be cleaner.

If you can live with this then one possibility is:

Create a "Cust Code Base" file that has right structure but is empty.
Create a macro that will
1) delete table Cust Code 10
2) rename Cust Code 9 to Cust Code 10.
3) rename Cust Code 8 to Cust Code 9
etc
then
Copy cust Code Base to Cust Code


If you you want to try to keep a record of the dates one option is to
create a file with 10 fields and whenever this macro runs move the 1st
9 each down the line 1 and put todays date into the 1 field. This will
give you a history of dates to files each time it is run even if you
skip a week or have a 5 day holiday or whatever.

You will have a history of dates corresponding with the last 10 backups
no matter how many days.

Another would be simply to have
Create a single archive table with all data the same with an added
Date.
Then run query to append current Cust Code data into it with todays
date in that extra field.
Then run query to empty current cust code data
Then run query to delete anything from the archive greater than today
less 30 days.
That way you have an entire months worth of backups, one table and data
can be restored with queries to empty current file (or back it up) and
append in whatever date you want)
Result is 4 queries that handle up to 30 days (or whatever of backups)

I would go with this approach. and perhaps have this (these) archive
tables in a separate backend archive mdb.

Ron

 

[Guest]

Your 30 Day backup is what I used.
I also had to set up a query to retrieve info based on date last recorded
just incase no new information came in.
It works. Thanks.