Archiving a table 1

[sunny12]

This is my first Access Database. I have a table with Field names Agency Code (primary Key), Agency Name and No of employees. The Number of Employees Field will be updated every month.

So i would like to create a archive table. Every month the user before changing the number of employees Value in the form would click a button to archive the existing values in the archive table.

How do i tranfer the data from the table to the archive table every month?. please help.

 

[genomon]

Dim strSQL As String
strSQL = "INSERT INTO Your ArchiveTableName "
strSQL = strSQL & "SELECT * FROM YourInputTableName"
DoCmd.RunSQL strSQL

Probably put in the click event of a form button or wherever you initiate the process from.

Always remember that you're unique. Just like everyone else.

 

[JerryKlmns]

I would add a date field to the archive table

Dim strSQL As String
strSQL = "INSERT INTO YourArchiveTableName " & _
"SELECT * FROM YourInputTableName"

CurrentProject.Connection.Execute strSQL,,129

'Assuming you include an Archived Date field
'that should keep that date when this happend
strSQL = "UPDATE YourArchiveTableName " & _
"SET ArchivedDate=Date() " & _
"WHERE ArchivedDate Is Null;"

CurrentProject.Connection.Execute strSQL,,129

 

[sunny12]

Jerry, Than you for your help. I am getting an error in CurrentProject.Connection.Execute strSQL,,129.

The error is "No Value given for one or more required parameters".

When i checked the Archive Table, All the entries are updated except the added DATE Field.

Please Advice,

 

[sunny12]

Actually the code works.

Thanks

 

[sunny12]

hi JerryKlmns

THanks for helping me with the code.
But instead of the system date, i would like to get the date from the user and store it in the table. Can you please help me with this.

Thanks

 

[JerryKlmns]

Use a third party non-ActiveX control to get a valid date from the user

http://www.allenbrowne.com/bin/calendar2k.zip