CODEWITHSUNDEEP
sqlms-accessvbscript
user3407371
user3407371

Reputation: 1

Update tables, not overwrite them when using the DoCmd.Transferdatabase command

Okay for this I am tasked with creating an Archive database for the Primary database. They want this done to keep the one they use clean. Basically a database for a database. I have been able to set up the queries for the tables that they want to get the information from to be archived out. Then once the data is pushed to the archive they want some method to go back in to deleted the data that was just pushed from the tables, making them smaller, which I have working. Everything works fine until I run it a second time. Because The code goes back in to query from the deleted fields the queries that are run and then the information pushed no longer has information(blank table) coupled with the way the DoCmd.transferdatabase works. It is just overriding the tables of information with a blank table in the archive database. This would not be a problem except for them having to implement the archiving again later down the road with new parameters for the queries for the tables. It would then just over ride the table instead of combining them. I need a way to get the tables to combine. They are the same table just need to add different information periodically that does not delete the existing table in the Archive.

I am fairly new at this and nothing I have tried has made it work. I am wondering if this is even possible to combine table information from two separate DB's?

Upvotes: 0

Views: 254

Answers (1)

Tom Collins
Tom Collins

Reputation: 4069

Instead of moving archived data back & forth, just link to the archive table instead. The command will begin like this: DoCmd.TransferDatabase acLink

The you can run queries against the production or archive tables whenever you want.

Upvotes: 1

Related Questions