Archive data from joined tables to joined tables

[Guest]

I have an application that prints labels in batches in which each label has a unique serial number. The data (approximately 300,000 labels/year) is saved to an Access 2000 database and I am trying to use ADO exclusively. I need to archive data from two joined tables to two archive tables, but am having problems creating the appropriate action queries. I looked at some similar posts but did not have good results

My source tables are the following (joined on fldSerNo)
tblBatche
-fldBatch (Primary Key
-fldDat
-(some other fields too, but not listed

tblSerNo
-fldSerNo (primary key
-fldBatch

My identically structured destination tables are the following (joined on fldSerNo)
tblBatches_archiv
-fldBatch (Primary Key
-fldDat
-(some other fields too, but not listed

tblSerNos_archiv
-fldSerNo (primary key
-fldBatch

I want to move all of the data from the source tables to the destination tables that is from the previous day or older. That is, none of the records that are archived should exist any longer in the source tables after the archive process is completed. Any help would be very appreciated

Thank you
Eric

 

[Chris2]

I have an application that prints labels in batches in which each label

has a unique serial number. The data (approximately 300,000 labels/year)
is saved to an Access 2000 database and I am trying to use ADO exclusively.
I need to archive data from two joined tables to two archive tables, but am
having problems creating the appropriate action queries. I looked at some
similar posts but did not have good results.

My source tables are the following (joined on fldSerNo):
tblBatches
-fldBatch (Primary Key)
-fldDate
-(some other fields too, but not listed)

tblSerNos
-fldSerNo (primary key)
-fldBatch

My identically structured destination tables are the following (joined on fldSerNo):
tblBatches_archive
-fldBatch (Primary Key)
-fldDate
-(some other fields too, but not listed)

tblSerNos_archive
-fldSerNo (primary key)
-fldBatch

I want to move all of the data from the source tables to the destination

tables that is from the previous day or older. That is, none of the records
that are archived should exist any longer in the source tables after the
archive process is completed. Any help would be very appreciated.



DDL . . .

CREATE TABLE tblBatches
(fldBatch INTEGER
,fldDate DATE
,CONSTRAINT pk_tblBatches PRIMARY KEY (fldBatch)
)

CREATE TABLE tblSerNos
(fldSerNos INTEGER
,fldBatch INTEGER
,CONSTRAINT pk_tblSerNos PRIMARY KEY (fldSerNos)
,CONSTRAINT fk_tblBatches FOREIGN KEY (fldBatch)
REFERENCES tblBatches
(fldBatch)
)

CREATE TABLE tblBatches_archive
(fldBatch INTEGER
,fldDate DATE
,CONSTRAINT pk_tblBatches_A PRIMARY KEY (fldBatch)
)

CREATE TABLE tblSerNos_archive
(fldSerNos INTEGER
,fldBatch INTEGER
,CONSTRAINT pk_tblSerNos_A PRIMARY KEY (fldSerNos)
,CONSTRAINT fk_tblBatches_A FOREIGN KEY (fldBatch)
REFERENCES tblBatches_archive
(fldBatch)
)


TEST DATA . . . .

INSERT INTO tblBatches
(fldBatch
,fldDate)
VALUES (1, #02/23/2004#)

INSERT INTO tblBatches
(fldBatch
,fldDate)
VALUES (2, #02/22/2004#)

INSERT INTO tblSerNos
(fldSerNos
,fldBatch)
VALUES (100, 1)

INSERT INTO tblSerNos
(fldSerNos
,fldBatch)
VALUES (200, 2)



EXPECTATIONS

I expect that fldBatch value 2 will get copied out of tblBatches and
tblSerNos and into the _archive versions of the tables, and then deleted out
of their source.



In a multistep process, controlled from a VBA function, or at worst, a
Macro, run the following QueryDefs:

INSERT INTO tblBatches_archive
(fldBatch
,fldDate)
SELECT B1.fldBatch
,B1.fldDate
FROM tblBatches AS B1
WHERE b1.fldDate < Date();


INSERT INTO tblSerNos_archive
(fldSerNos
,fldBatch)
SELECT B1.fldSerNos
,B1.fldBatch
FROM tblSerNos AS B1
INNER JOIN
(SELECT B02.fldBatch
FROM tblBatches AS B02
WHERE B02.fldDate < Date()) AS B2
ON B1.fldBatch = B2.fldBatch

DELETE B1.*
FROM tblSerNos AS B1
INNER JOIN
(SELECT B02.fldBatch
FROM tblBatches AS B02
WHERE B02.fldDate < Date()) AS B2
ON B1.fldBatch = B2.fldBatch


DELETE B1.*
FROM tblBatches AS B1
WHERE B1.fldDate < Date()


Well, it seemed to work, back up your data before attempting your tests .
.. .

Oh yeah, and I wish there were a way to force Access to open new queries
in SQL view each time you create a new one . . . if someone knows, I'd be
happy to hear how.

 

[Guest]

Chris(2)
Thank you. I am in the process of trying out your code

Thanks
Eric