programmingly remove records from one database and copy them to another database

[Andra]

hallo

I have the following task:
Two Access databases with similar structure. The second database is for
archiving records from the first database. I should make the interface for
archiving process.

There is a third party application that actually uses the database (and
should be able to use the archive database, too).

Till now I have made two tools for the database: in both cases it is an
Access application (mdb file), with linked tables from the working database,
and the form with a code performing a task: in first case it makes changes
to the database records, in second case it allows to choose records
according to some parameter and generates reports in text files. The code
uses recordsets and sql statements.

If I follow the same way now I make a new Access application, link tables
from both databases, make a form to choose criterion, and ...what? I'm not
sure it is sp easy to remove some records from one table, then append to
another table, and what's more - for some records it is more complicated
because I should go through one table, take one field, according to that
field search another table for all records that correspond to that field,
remove them and copy to the archive, and then remove the field in the first
table.

Maybe there is a simpler way? I'm not much advanced in Access programming.


please help

 

[Guest]

I am not sure if this will work in your case but try this:

Make a Make Table query with the fields of the tables that you want where
the Date field has the criteria that you want, ie: <="10/01/2006". Run it.

Change it to an Append table.

Make a Delete query to delete the records you just moved to the new table.
Run it.


This will allow you to take the old records out of the table but still save
them.

 

[John Vinson]

Make a Make Table query with the fields of the tables that you want where
the Date field has the criteria that you want, ie: <="10/01/2006". Run it.

Nitpick: <= #10/01/2006#

The " delimiter is for Strings, the # delimiter for dates. Note that
the String "9/30/2006" is GREATER than the string "10/01/2006" so
you'll possibly get incorrect results.

John W. Vinson[MVP]

 

[Andra]

is it possible to program (vba) it?

Joseph wrote

 

[Guest]

is it possible to program (vba) it?

Yes. This is code after making a form and putting a command/toggle button (
does not matter ) and a unbound text box [Set_Date}.

Private Sub AppendQuery_Click()
On Error GoTo Err_AppendQuery_Click

DoCmd.RunSQL "INSERT INTO newtablenamehere
(withthecolumnshereinparanthises) SELECT yourtable.columns FROM oldtablename
WHERE (((oldtablename.Date)<=[Set_Date]));"
DoCmd.RunSQL "Delete oldtablename.columns WHERE
(((oldtabledname.Date)<=[Set_Date]));"


Exit_AppendQuery_Click:
Exit Sub

Err_AppendQuery_Click:
MsgBox Err.Description
Resume Exit_AppendQuery_Click

End Sub


But you have to make the table first seperately, so you do not recreate it
each time.

 

[Andra]

yes, thank you, did it with sql statements.

And did manage somehow with that complicated case I mentioned. It would be
easier if it didn't turn out that recordsets were true sets of table
records - if a record is deleted from the table, it is also deleted from the
recordset. It was a bit of surprise to me. So I encountered the error
message "Record is deleted." on MoveNext. I stayed with a solution that
seems suit me now but is not really good:
I keep a boolean value that shows whether a record has been deleted and then
use
If dele Then rsFrom.Requery Else rsFrom.MoveNext
As far as I understand the recordset is looked through once again from the
beginning.


Joseph wrote

is it possible to program (vba) it?

Yes. This is code after making a form and putting a command/toggle button (
does not matter ) and a unbound text box [Set_Date}.

Private Sub AppendQuery_Click()
On Error GoTo Err_AppendQuery_Click

DoCmd.RunSQL "INSERT INTO newtablenamehere
(withthecolumnshereinparanthises) SELECT yourtable.columns FROM oldtablename
WHERE (((oldtablename.Date)<=[Set_Date]));"
DoCmd.RunSQL "Delete oldtablename.columns WHERE
(((oldtabledname.Date)<=[Set_Date]));"


Exit_AppendQuery_Click:
Exit Sub

Err_AppendQuery_Click:
MsgBox Err.Description
Resume Exit_AppendQuery_Click

End Sub


But you have to make the table first seperately, so you do not recreate it
each time.