Automatically Moving all records from table1 to table2... every end of the Month

[joelo]

I have two indentical tables and I want to Automatically Move all the records from table1 to table2 at the end of every Month leaving table1 blank. please could anyone help me out on how to do it.


Thanx in advance.

[Ribeyed]

hi,
you can code an ASP page to move records from one table to another thats the easy part. Doing this at the end of every month not so easy. Sure you can code an ASP to only run the code if it is the end of the month but you need the page to be run by anyone i.e. you need a trigger, what happens if the page isn't run? the data will not be copied over. Depending on the database you are using you maybe better looking at configuring your database to do that type of task.

[simflex]

joelo,
hope this response is not too late.
what type of database are you using?
if you are using sql server and you understand how to use stored procedure, you can do this rather easily.
all you do is write the stored procedure and either use a cronjob or just use sql EM to schedule a job to run a specified date.
Let me know if i can help you here.

[joelo]

I am using Access Database right now...Pls kindly help me whichever way U can...Pls Pls Pls

Thanx

[simflex]

then there are 2 ways I believe you can accomplish this with access.
One is to use a macro with a TransferDatabase function.
After this macro is set up, use a .bat file to run the macros you set up every month to automatically move data from db to another.
The other option is to use a vb module to accomplish same.

I have only used the macro with the TransferSpreadsheet function to automate moving data from access to excel but the concept is profound.
If you want to go the macro route, and need assistance, please advise.

[joelo]

Let's give it a try

[simflex]

ok, here are the steps.
1, open the database that contains your source table.
on the database window, click on Macros, then click New.
Then use the following:

Action: TransferDatabase
Transfer Type: Export
Database Type: Microsoft Access
Database Name: YourDB.mdb
Object Type: Table or Form (Depending on what you want to do)
Source: TableName you want to move data from
Destination: TableName you want to move data to Structure Only: No (because you are moving both data and table structure)

Then save it as some macro name like ArchivedTable or something.

Schedule the macro by first going to administrative services, services, make sure that Task Scheduler is started.
Then go to accessory, system tools, scheduled Tasks, follow instruction to schedule the macro, in your case, monthly.
Finally, use the following .bat file to run the macro.
With this file, your file will automatically run on the date you schedule it to run.


START C:\"Program Files"\"Microsoft Office"\Office10\Msaccess.exe C:\emp.mdb /excl /x macroName

The only issue here is how do you delete the file from old table after you have move the data to new table.

So I am enclosing the code I use for archiving data by moving them from one table to another and deleting them.
You can use this code as your access stored query and then following the macro example I gave you, select macro action called OpenQuery, you can set this up to move data and delete that data.
I have not used this code in access because I didn't need to but it works fine in sql server.
Here is the code, good luck and let me know how I can help further.