archive access records (move records to another access database).

[sigmab]

I'm trying to setup a database in access 2000 whereby user choosen records in a table can be archived or moved to another table. How would I do this?? I was looking to see if there was a move access or VB command that I could incorporate but I couldn't find anything. Any ideas???

 

[sigmab]

sorry guys the title is a bit misleading I want to move records from one TABLE to another within the same database.

 

[FancyPrairie]

If you are going to move a range of records, use the SQL Insert Into command to copy the records to the archive table and use the SQL Delete command to delete the records from the original table. Use transactions to do this so that you can roll back the changes if necessary (i.e. BeginTrans, CommitTrans, Rollback).

Idea (if you haven't already thought about this):
I usually have one main form from which the user can edit the data. A menubar (assigned to this form) contains an option by which the user can select which table he/she wants to edit/view. If the user wants to view the archived table, I simply set the recordsource of the form to the archived table, unhide a label that indicates the user is viewing archived data, and set the form to readonly. I also give the user the ability to transfer items back from archival to production (in case they screwed up).
This technique also works on reports. So the user can (using the same report) print production data or archived data.

 

[sigmab]

Thanks, I'll try that and let you know how it works.

 

[FancyPrairie]

Here's some code I grabbed and stripped. May not work completly, but will give you some ideas:

Sub Archive()

    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command 
    Dim bolBeginTrans as Boolean

    On Error GoTo ErrHandler

    bolBeginTrans = False

    Set cnn = New ADODB.Connection
    cnn.Open "yourConnectionString"

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdText

    cnn.BeginTrans
    bolBeginTrans = True

    cmd.CommandText = "INSERT INTO tblArchive Select * from tblProduction Where ...;"
    cmd.Execute Options:=adExecuteNoRecords
    cmd.CommandText = "DELETE FROM tblProduction WHERE ...;"
    cmd.Execute Options:=adExecuteNoRecords
    cnn.CommitTrans
    bolBeginTrans = False

ExitProcedure:
    Exit Sub

ErrHandler:
    if (bolBeginTrans) then
        cnn.RollbackTrans
        ...
    end if

    Resume ExitProcedure
End Sub

 

[sigmab]

Thanks FancyPrairie I was wonder how to get started