Archive MSaccess data into a different Access DB

[usavguy]

I have a database (myDB1.mdb) that contains related data in seven tables. Using vb6 existing in a separate application, I would like to find a way to archive this data into a new database (myArchive1.mdb) located in the same location as the main database.

I understand how to use the INSERT INTO method, but I don't know how to get specific data (SELECT * from Table1 WHERE myField = 'XYZ') from one file and put it into another file.

Do I need two connection objects, one for each db?
Is it possible under one SQL statement?
Do I need to have pre-existing tables with the same structure in the destination database?

Any and all help is appreciated.

[sunga_sunga]

Hi everybody, i created a database with one OLE object as data type using MS Access 2000 and want to display it in VB6 form. There are at least 20 records and i can connect and display only the text in the form but not the pictures. I used ADOdc. The error message is "No compatible source was found for this control, Pls. add an intrinsic DAta control or remote data control to the form". What do ypu mean by this. Any help will be very much appreciated.

I tried also the data control instead of ADO but it always display an error "unrecognized database format"... I also tried the example from microsoft published under Q205635 and displayed the same error.
Help please....
Thank you.

[cybuster]

try to use adodc control...

for usa... if you using ADODC, yes its possible to use one on SQL statement but if want to input another SQL statement, use the recordsource command in adodc codes...

[usavguy]

Thanks to all for your help -

I couldn't get anywhere with the adodc so I pushed forward with the ADO.Command and here's what I came up with:

Private Sub cmdArchive_Click()
Set myConnection = DE1.conTest
myConnection.Open
Dim myCommand As New ADODB.Command
myCommand.ActiveConnection = myConnection
Dim mysql As String
mysql = "INSERT INTO tblFGPlates " & _
"IN 'G:\COMMON\QC\AlphaLIMSArchive.mdb' " & _
"SELECT * FROM tblFGPlates"
myCommand.CommandText = mysql
myCommand.Execute
myConnection.Close
Set myConnection = Nothing
Set myCommand = Nothing

End Sub