Archiving records

Topic

New Reply

I’m trying to setup my database so that the user can manually archive records manually. Right now we have open cases and closed cases all in the same table. When a case closes I would like to be able to use a command button to put the closed case in a separate table. The real problem I’m having is getting all the data for each record to ship to the archive table. In my form there are many controls on the form itself and there are also 2 subforms, where the main form is getting its data from tblMaster, and the subforms getting their data from tblDetails and tblVictims. Code examples and or suggestions would be great!
Thanks
Jols

Reply | Quote

Replies

Hi Jols

Don’t know whether i can help, but i have a similar situation where i have open orders in one table, and once closed, they are moved to another table.

What i do is use two queries which run invisibly to the user. The first runs an append query (to the closed orders table) picking up the order number from the user’s form, and then i close the form, display a “record archived” message, and then run a delete query on the open table.

Hope this helps………………

Peter

Reply | Quote

Hi, I am doing the same thing. It seems to work very well. I have a main table and three other tables all supporting a main form and three subforms. When I archive a record from the main table/main form I run a query which adds it to the archive table. Another query takes the supporting information from the subforms records and adds them to there own archive tables. Finally i run queries which delete the newly archived records from the current tables. I have examples I can email to you if you would like. I also am reversing the archive by reversing the query steps. I perform all of this as the user logs on it picks up the records assigned to them and allows the user to pick and choose the records he/she wants to archive. All the user does is check a box on a form next to the record they want to archive.

Kevin

Reply | Quote

Hi Kevin,
I was hoping I could find someone doing something similar here. I could really use some of your examples to get me going on this.
By the way….what triggers your action queries? When the form closes? or right when the check box is marked?
Thanks a lot for your help!
Jols

Reply | Quote

You have to be very careful using this approach if you use autonumber keys. The reason is that compacting the database resets the seed on autonumber fields to the next highest number, even if that number was previously assigned to one of the records you archived.

Reply | Quote

I was about to post a new question but then found this thread that is real close to my problem. I have an “active” table and “archive” table with identical structures (including autonumber keys indexed with no duplicates). When “archiving” a record, I copy it from the active table to the archive table using an append query something like:

INSERT INTO ArchiveTable SELECT ActiveTable.* FROM ActiveTable WHERE (condition that defines a record that needs to be archived);

and then delete those same records from the active table.

This keeps the active table smaller (better performance, presumably). When I need to query all records (e.g., searches on both active and archive records), I query a union of the two tables (hence, the primary keys need to remain unique for the relationships to be properly maintained).

The problem is (as has been pointed out in this thread), the deletion step leaves autonumber “holes” in the active table. When new records are added, the autonumber key assigned may, in fact, duplicate one of the keys of the records that was previously moved over to the archive table. Later, when I try to archive one of these “new” records, a conflict arises because of duplicate key values (this could also occur if I tried to “unarchive” a record; i.e. move it back to the active table).

So… the question is: What is a better way (i.e., a way that actually works!) for maintaining an active-archive table pair while maintaining autonumber uniqueness across BOTH tables?

Tom Bushaw
Access 2000 (9.0.4402 SR-1)

Reply | Quote

To all on this particular thread:
Charlotte is right about the compacting issue and potential holes in the autonumber sequence so here’s some ideas:
1 Compact before you archive. This may be impractical so;
2 Don’t take the Autonumber key with the record when you archive it (or even return it to live). This way, Access will automatically generate a new, unique key everytime a record is added to a table. This does mean that the record changes key when you archive it but since the autonumber’s purpose is purely to maintain uniqueness, this shouldn’t be a problem. If it is;
3 Don’t use Autonumbers for your key fields. Use a manually entered number or text field. You can write a bit of VBA to automatically generate the next number in a data entry form to save having to constantly try to remember what the last number was but you can change it to any unique number. This last method means your key fields are kept intact no matter how many times the record changes tables.

Reply | Quote

Jon –

Good ideas. Thanks.

Regarding Option 2: If I’m using link tables that use the key values as the linking field this option could present problems. I suppose VBA code could be written to update link table references when records get moved from table to table (and new autonumber keys get assigned), but that seems like it would be a bit of a headache.

Option 3 seems like the way to go, but I need to be sure to check both tables when establishing a new unique number (e.g. DMAX+1 on the union query result). Right?

Any better ideas out there?

Tom Bushaw
Access 2000 (9.0.4402 SR-1)

Reply | Quote

Maintaining non-automatic keys in a multiuser database can present some serious problems, and I don’t recommend it. One other way you can handle this is by subclassing the records.

To do that, you create a table that holds nothing but an autonumber key and possibly a field to tell you whether this record is active or archived. That table is the center of your design, and it has a one-to-one connection to your active and archive tables, with referential integrity and cascading updates enabled. When you create a new record, you create it in that first table and then create it in the Active table. Referential integrity and cascading updates can be used to see to it that the new active record inherits the PK from the first table as its primary key. When you move the record to an archive table, the key moves with it, along with the connection to the first table. All that changes is the location of the record. Since the key is generated in the first table, you never should have orphaned records.

Reply | Quote

What serious problems would they be? I use non-automatic keys quite often and if I’m heading for trouble I’d like to know about it.

Reply | Quote

Jon,

The “serious problems” Charlotte warns of might be related to her “multiuser” qualification. Envision User 1 getting ready to add a record. The new manual key has been calculated (e.g., DMAX+1) but the record has not yet been added. User 2, meanwhile, is doing the same thing. User 2’s manual key gets calculated to the same value as User 1’s (since User 1’s record hasn’t been added yet). Now, whoever adds their record first “wins”; the other runs into problems…

It doesn’t appear that this particular problem would arise in a single user environment but I wonder if there are other ones that would?

Tom

Reply | Quote

I am makeing a purchase order/inventory program where they wanted either to manually enter a po number or have the system assign one. I have a table called LabelFile and used this code on my PurchaseOrder entry form to assign a number. So far it works, haven’t tested in a multi-user situation, though. Here is the code:

Private Sub PONumber_Exit(Cancel As Integer)
If Me!PONumber = 0 Then
Dim intPONumber As Integer
Dim db As DAO.Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset(“Labelfile”)
intPONumber = rst!PONumber + 1

With rst
.Edit
!PONumber = intPONumber
.Update
End With

I suppose the problem will be when a PO number is entered that is already in the system, or the system tries to assign one that is already on file.

rst.Close

Me!PONumber = intPONumber
End If

Reply | Quote

Do NOT make your PO the primary key. Go ahead and make it a unique key (indexed, no duplicates) but don’t make it the primary key. Make your primary key an autonumber that the user never sees. When they enter a new record and assign it a PO, the system will automatically generate an autonumber key for the record. Then when they decide that their current PO numbering system isn’t adequate and they have to change it, you’re a hero because it won’t break your application. Don’t believe anyone who tells you it won’t change. Years and years of experience tells me it will.

It looks like you’re holding a single value in LabelFile and looking it up to create the next number, and this is a fairly common approach to this kind of key. One thing to remember, though, is that you’re working with the version of the database that’s in memory on your machine. Other users are working with the copy that’s in memory on their machines. The various copies don’t get synced constantly, even with a split front-end/back-end, so you may need to force a refresh to be sure that the number you just added turns up in the back-end sooner rather than later. This becomes especially critical if users are actually keying in POs, with you playing catchup to see to it that the number they entered gets saved as the latest PO number.

To handle the problem of the PO’s, your code needs to do two things, test to see if the value they enter or you created already exists in an order record, and then attempt to create the new record and trap any errors. If you get a duplicate key or index error, increment the PO number using your code or something like it and try again. Usually, you handle this kind of thing in a Do…Until loop, looping until you get a PO number you can use without a duplicate key or index error.

Your code updates your LabelFile table, but it doesn’t indicate how you actually create your records using the PO number. The table where the PO number is actually assigned to an order transaction is the one is the one that will get you into trouble, and this is where you need an autonumber. If you used an autonumber in the records as the PK but display the associated PO on the screen and for searches, you wouldn’t be plagued with the problem of a user incorrectly entering the PO and then wanting to go back and change it. If it isn’t the primary key, changing it isn’t such a problem.

Reply | Quote

They require programmatic creation and maintenance, and they break down as primary keys in a replicated application. Plus, since they are usually alphanumeric, they are much slower to sort or seek on than straight numbers.

Reply | Quote