Archiving Tables

[TJBernard]

I have an MS Access database set up with a FE .mdb and a BE .mdb file. I want to add the ability to archive a table in the BE to a seperate database by the users clicking on a button on a form in the FE .mdb.

Here is what I would like to accomplish....

User clicks a button on the FE .mdb file
A copy of table [tblName] is pasted on a seperate .mdb file (Database2)

I am not sure of the best way to tackle this problem, I have been looking at a few different options but I was not sure.

I was thinking about creating the table on code (on the FE button click) using the CreateTableDef function (creating the table on Database2) but I have not gotten this to work.

After this I would simply append the records in the table to the newly created table.

If anyone has any ideas, let me know.

Thank you for your time,

T.J.

 

[Fornatian]

Use a MakeTable query

You can use a make table query to make a table in a different database, the SQL I tested it with was:

SELECT Fornat_Tmp.dept_id, Fornat_Tmp.Deptname INTO TestOutPut IN 'D:\My Documents\DesignNotes\Workdays.mdb'
FROM Fornat_Tmp;

This is better in a saved(and therefore preoptimised) query, when it has been run once you can then prevent the error messages(table already exists etc...) from appearing using SetWarnings as follows:

Docmd.SetWarnings False
Docmd.OpenQuery "MakeTableQueryName"
Docmd.SetWarnings True

Is this just for backup purposes because if not you should use a linked table in your second database referencing the initial data in the first database.

 

[KevinM]

The follwing will create a NEW archive table each time it's run (based on yearly freq)

To fully automate the procedure it's a good idea to have the names of the tables prefixed or suffixed with the year of the records (if doing anually) or change the format type to the frequency of your archive.

Basically you need it to automatically create a descriptive, yet unique table name each time it's run. The date is the best thing to use for this.

RUN this from the on click Event and add a msgbox so the user can cancel

'THIS IS DAO....
'--------------------
'Code start....

Dim strArchiveTable As String
Dim strArchiveDBPath As String
Dim StrYear As String
Dim SQLstr As String
Dim db As dao.Database

Set db =CurrentDB

'Current Year
strYear=Format(Date,"yyyy")

'DESTINATION TABLE
strArchiveTable ="MyTableName" & StrYear

'DESTINATION DB PATH
strArchiveDBPath = "c:\My Folder\MyDatabse.mdb"

DoCmd.Hourglass True
DoCmd.SetWarnings False

SQLstr = "SELECT * FROM MyTableName INTO " _
& strArchiveTable & " IN " & " ' " & strArchiveDBPath & " ' " _
& " FROM MyTableName"

db.Execute SQLstr

DoCmd.Hourglass False
DoCmd.SetWarnings True

msgbox "Archive complete"

'Code End
'------------------

"MyTableName" is the name of the table you want to archive
The table 'MyTableName2003' should appear in your dest db

You will get an error if the table name already exists so you may want to trap this, however you shouldn't need to run it more than once in a year