Checking value in another database

[hgus393]

Hi all,
I am running a database on a weekly basis and I want to store the historical data in a seperate database. Transferring the data to the historical database is no problem, however there is no control if the data already exist in the historical database. Is there a way I can say check if the report date in the weekly database already exist in the historical database?

 

[DCrake]

Is there a reason why you need to archive on a weekly basis?

You could link to the archive database and run append queries only copying unique records.

 

[Kafrin]

If you have primary keys being copied across it should only be able to tak enew records as old ones can't be duplicated due to key violations.

DCrake's suggestion is the most straightforward, so I'd recommend that first.

Alternatively, you could put a Yes/No field on the tables and tick it on records that have been archived - you should be able to set this up automatically as part of your archive routine.

Or you could set up a database property to hold the date of the last archive and only look at records with a date later than this; again this would be updated as part of your archive routine.

Or you can use the DBEngine to open up the archive database from your working database and access the data in the tables. This is very like what you asked for, but much more complicated to actually do. I'd guess that if you're not already linking the tables into your working database then you're probably already using the DBEngine to archive your data, so just remeber that you can both read and write data in databases you open this way.

 

[hgus393]

Is there a reason why you need to archive on a weekly basis?

You could link to the archive database and run append queries only copying unique records.

I guess I was not clear about the problem. The weekly database deletes all the tables that are moved over to the historical database as soon as user enters a new date in the weekly database. Each week approximately 4500 records are added to the historical database. I did not make this database, I only inherited it.