• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

Resetting Database

I have a database Office with 7 tables. At the end of each year I need to empty all the tables for the new  year. Ideally I would like, with one click, to
- have a copy of the year ending database created for archive
- replace the year end database with an empty version (same structure, no data)

The point is, it will be an non technical person resetting the database for the next year - so that is why I need one click simplicity

thanks for any strategy on how to do thhis

james
0
jameskane
Asked:
jameskane
  • 3
  • 2
  • 2
  • +1
5 Solutions
 
gdemariaCommented:
What type of archive do you want?  Do you want to make a full backup of the entire database and put it on CD and then put it on a shelf?  

Which database is it?   SQL Server 2005?  SQL Server 2000, My SQL ?

-> which ever database it is, you should add that zone to your question

0
 
jameskaneAuthor Commented:
Hi, its a msaccess 2003.

I would like a copy of the year ending database 'OFFICE.mdb' to be made - called something like Office_2008.mdb - this would stay in the database folder.

Then the current OFFICE.mdb should be deleted

And finally,  a copy  of a ZERO version of the database 'Office_Zero.mdb' should replace the year ending OFFICE.mdbversion.

Thanks, for the note on flagging the database people, will do;

james





 in the database folder (like 2008Office.mdb). The folder contains a zero data version called OfficeZERO.mdb  and I would like a copy of that 'Office.mdb' to be made and to replace the current
0
 
gdemariaCommented:
Sorry, I don't know access.   In Coldfusion, you can easily loop through the tables and clear them out - once the database files have been backed-up - but I'm not sure how to do that part.

You've added MSaccess as a tag, you should add it to your zones, that way people who read the ms access zone can pitch in...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
SidFishesCommented:
access is easy as you can simply make a copy of the file rename it and move it

You can pretty much do it all simply with cffile operations

the only issue you would have is making sure everyone is out of the db when the procedure is run

pseudo code

cffile copy existing to newpath\newfilename.mdb
cffile copy zerofile.mdb to workingpath\workingfilename.mdb with overwrite

and that's about it

if you need specific help with the cffile operations just let me know





0
 
Jones911Commented:
I don't think that will work as the Datasource locks the file for deletion often till you restart Coldfusion.

This is how I would do it.

Have 2 database.

One for the current year and the other thats for archived data.  At the end of the year write a query to select all the data out of the current DB and then insert it into the archive db.  Then delete from the original db and the current db starts freash again.  This would only require 2 Access databases.
0
 
SidFishesCommented:
"locks the file for deletion"

It's been several years since I used an access.mdb for a back end for anything but i seem to remember if you uncheck Maintain Connections  --  Maintain connections across client requests, (in advanced section of the datasource) this will not be an issue.

way back in dim past (1998?, 1999? a long time ago anyways) I used to do updates on my access web backend by just ftp-ing over top of the existing db...but would only work with the this setting unchecked
0
 
Jones911Commented:
OK that might be the case.  I have only used MS access DB with CF once or twice and I know I wasnt able to replace it due to the issue I mentioned but I didnt look deply into it.
0
 
jameskaneAuthor Commented:
Thanks very much everyone for your inputs. Boy, do I feel out of touch using this msaccess thing !! Really need to move on to mysql or something .  I am going to try both approaches proposed and am  sure one of them will work.  Just a little clarification Jones911, you are proposing I do this query for each of the 9 tables and then insert each into the archive database - or is there a way to do this for all tables in one shot ?

Thanks again to you all for taking the time.

James
0
 
jameskaneAuthor Commented:
THank you all for your help - SidFishes - got it sorted with the cffile approach !

tks again all

james
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now