Archiving Data 5

[Only1Abhi]

Hi ppl.
I have 3 tables and I want archives of them plz.

my tables are:

tblCustomer
tblCustomerContact
tblTransaction

tblCustomer has a primary key "Customer ID" and foreign keys of this field in the other 2 tables. There is also a Transaction ID for each transaction the customer carries out.

If a certain customer doesn't make a transaction within (say 6 months) then I would like their details from tblCustomer, tblCustomerContact and tbLTransaction to go to 3 other tables called:

tblArchiveCustomer
tbLArchiveCustomerContact
tblArchiveTransaction.

these 3 new tables all have the same fields and data types to their corrisponding table.

Does any1 know how to do this? Or is there an easier solution?

Many thanks in advance.

 

[Only1Abhi]

thanks for the help dan.
really appreciate it.

 

[jpa]

I'm having a very similar issue as Abhi was having in setting up the delete query. I've imported an Excel sheet into Access and Access has renamed all of the fields to generic Field1, Field2, etc. There is another table in my database with exactly the same structure, but different field names. I want to delete records from the formatted table where there are matching keys in the imported table (so I want to delete duplicated data from the existing table). My delete query is as follows:

DELETE Imported_NSD_Data.*
FROM Imported_NSD_Data INNER JOIN Raw_NSD_Data ON (Imported_NSD_Data.Candidate_Code = Raw_NSD_Data.Field3) AND (Imported_NSD_Data.Cascade_ID = Raw_NSD_Data.Field2);

When I preview the matching records, it show the correct records I want to delete. However, when I run the delete query I get the error: "Could not delete from specified tables." (I'm using the default Access security settings--i.e. I'm Admin so that shouldn't be the issue)

Candidate_Code and Cascade_ID form a compound primary key in table Imported_NSD_Data and there is no referential integrity set on the joins from that to one other table.

Any idea why this delete doesn't work? Should I take a different approach?

 

[danvlas]

If you enforce the referential integrity, you will be able to delete the records all right.

Otherwise, the query should be based on first table only, and the records to be deleted should be identified through a Dlookup function:

DELETE Imported_NSD_Data.*
FROM Imported_NSD_Data INNER JOIN Raw_NSD_Data ON (Imported_NSD_Data.Candidate_Code = Raw_NSD_Data.Field3) AND (Imported_NSD_Data.Cascade_ID = Raw_NSD_Data.Field2);
Where Candidate_Code = DLookup("Field3","Raw_NSD_Data","Field3 = " & [Candidate_Code] & " And Field2 = " & [Cascade_ID]) And Cascade_ID = DLookup("Field2","Raw_NSD_Data","Field3 = " & [Candidate_Code] & " And Field2 = " & [Cascade_ID])

It will run slower in this case...


HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[THWatson]

After reading this post, I did some checking on the Microsoft Knowledge Base. I see that they acknowledge the problem that causes the last Autonumber to be re-used following the compacting of data. There is an updated version of the Jet engine that corrects this problem.

Tom

 

[JeremyNYC]

THWatson,

Could you tell us the article number for that? It sounds quite different from what danvlas was talking about, but maybe I'm just misunderstanding something.

Thanks.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[THWatson]

Well, it's possible I'm misunderstanding too. But have a look at Dan's February 28 post where he says...
"If you have an autonumber set as primary key and delete any other record except the last one and you compact the db, nothing happens, everything is OK. But suppose you move the last record to the archive, delete it from the 'live' database and compact the database, then the disaster is there: the number is allocated once again, and when you'll try archiving that record you'll get the dreadful message 'Duplicate value in index or primary key'.
Compact the db on a regular basis, but never immediately after archiving. Wait until a new record is created so that the 'archived' number is not generated again, and you're free as a bird.
This has only to do with auto-incrementing AutoNumbers (which are used on a large scale, whether i like it or not...)"

After reading that, I checked the Microsoft Knowledge Base and found Article 257408 which says there is an updated version of Jet which corrected this problem.

Tom

 

[JeremyNYC]

Tom,


Dan's message does not point out a bug, it points out documented behaviour of the autonumber field--if you compact a database, all tables with autonumbers will use the number one above the highest number in the field for the next record--whether or not that would have been the case before compacting (it wouldn't have been the case if you had added and then deleted records before compacting).

The reason it's problematic in the situation Dan was discussing is because there might have been records that were archived out of the table (and related tables) that had already been assigned a number that would then be reused later.

He's not saying there's an error, but that there's something Abhi would have to work around.

I'm not intimately familiar with the lineage of that particular KB article, but I'm just about positive that it describes a different phenomenon, which is that the autonumber would create new records with the same autonumber as records that are currently in your table. That's a bug, and a big one. It was, as you mentioned, fixed with an update to Jet. But, even though they sound similar, I'm pretty sure they're very different animals.

Jeremy


==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[THWatson]

Jeremy

Even though I didn't read it as such, what I responded to might have been a slightly different issue.

I had read this post earlier because I am working on an Archive process myself, and I was interested in what Dan had to say about Autonumber fields, compacting the database, etc. Append and Delete queries are a new thing for me and I wasn't aware of the issue he raised.

I am assuming that downloading the newer version of Jet solves some problems. However, is it still the case that it's dangerous to delete the last record in a database, because that record number gets repeated after compacting?

Tom

 

[JeremyNYC]

Tom,

Yes. Access allocates an autonumber number each time you create a record. If you make a simple database with one table with an autonumber field and a text field in it, you can check this out for yourself.

Add a few records (by typing something in the text field and hitting the down arrow, the tab key or the return key). The number goes up by one each time. Add a few more and delete them. Then add another one. You'll see that the number used is not the next highest number in the series of records that still exist, it's the next highest number in the series of records that were created. Now add a record and before moving away from this record, hit the escape key a couple of times. The record disappears. But the next time you add a record, the autonumber will still think that last number was already used, and so will not use it, but will skip to the next highest number. Now add a few more records and delete them. Add one more record and you'll see a gap. Now delete that record. Compact the database. Add a record. No gap.

This is all documented behaviour, and is what dan was describing. You will have to be careful if you're using the process outlined above, but it shouldn't b that hard to have your code check when the last time a compacting was done and when the last time a record was added, if you set it all up to track those things.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[THWatson]

Jeremy

How interesting! I knew all about autonumber fields, but I didn't know the problem having to do with "compacting and deleting".

I have my system set to automatically compact upon close.

I don't have a clue how to have code check for when the last time compacting was done, and when last a record was added. I'll have to fish around for this.

Thanks for the clarifications.
Tom

 

[JeremyNYC]

Tom,

The whole point is that it's NOT a problem--unless you are archiving records out of your tables.

You'd have to create a field for each record that was time-stamped when it was created. And create a table in which to record the last time the back end was compacted. Then compare the two.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[THWatson]

Jeremy

Maybe the revised Jet takes care of this problem. I followed precisely the example you gave, and the expected gap was maintained no matter what I did about deleting records, compacting the database, etc.

Tom

 

[danvlas]

Tom,

Did you try deleting the last record, compacting the database and entering a new record? I'd be surprised if the deleted autonumber is not re-generated...

I don't know how a random autonumber behaves. Since it is not generated as a continuous sequence, would it be possible to reuse numbers that have been used once, but were deleted from the table, even from the middle?

And...what's the KB article you referred to?




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[JeremyNYC]

Tom,

I just started to post that what you said could not be possible. And then I tested it. And then I did some more research on it.

I am absolutely wrong and stand corrected. Thank you for pointing out the real behaviour. This is all new in Jet 4, as you say.

Here's how it works: if you delete records from a table, the autonumber is _not_ reset to max(existing autonum values) + 1, as it was in Jet 3. If you delete all the records, the first autonum value _will_ be 1. If you delete the last record(s) and then import the table into another database, the autonumber _is_ reset to max(existing autonum values) + 1.

Very interesting. It certainly makes the archiving process a little safer, as long as you're not, for some wacky reason, deporting and re-importing your tables to your live back end.

I learned all this from

http://groups.google.com/groups?hl=...TF-8&group=comp.databases.ms-access&scoring=d

It might be easier to go to groups.google.com and search for "Repair and Compact?" "Peter Oxley" comp.databases.ms-access. The thread was started on 2000/04/07. I did test it all out myself afterwards.

And I do credit it you with showing me the light on this. Thanks, Tom.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[THWatson]

Dan
The Microsoft KB article is 257408. And yes, I deleted the last record, compacted and then entered a new record, and the expected gap was maintained. As I said, perhaps the latest version of Jet 4 corrects the former problem.

Jeremy
I'm going to do a bit more testing, and will look at the Google post you mentioned.

Tom