Transfer data between databases

[Rashers]

Hi all,
Is there a quick way to transfer data from a table in one database to a table in another. I.e. transfer the results of a query to another database,

What I have is one active database called In_Process and one called Archive. The In_Process is accessed by several automated test stands and may have 30 - 40 thousand records entered on any given week. This slows down the automated process as the results become huge after time.
What I want to do is transfer all PASSED units to an archive database which would eliminate 95%+ of the records and, hence, speed up the response times of the automated stations.

I have all the required SQL queries worked out but my theoretical knowledge of Access is limited and I don't know how to append these results to the corresponding table in Archive.

Any thoughts?

Andrew

 

[bat17]

link the archive table to the In Process database then use An append Query to add the Passed Data To the archive follwed by a delete query to delete the same data from the original table.

HTh

Peter

 

[Rashers]

Just what I was looking for. Thank you peter.

 

[mdmilner]

You can also do it the VB way (slower in general but sometimes what you have to do requires VB steps)

Basically, open a new workspace & DBEngine then open it precisely the same way you might open a recordset in the currently open mdb file.

Dim ws As Workspace
strDB = "pathlocation"
Set dbsArchive = DBEngine.Workspaces(0).OpenDatabase(strDB)

Set rsd = dbsArchive.OpenRecordset(strSQL, dbOpenDynaset)