Adding a Recordset to a Table??

[mrdj1002]

Hi guys
Well I have two tables in two distinct dbs(of course the tables’ stracture are the same. I mean the same fields &…). And I want to move some records of one to another. The simplest and the slowest way to this is to:
1.read on record form A
2.add the record to B
3.remove the record from A
and do it for each record to the end. But it is toooooo slow. I’m looking for a way that I can create a recordset of records I want to move. And just add the recordset to the table but I don’t know how to do this. I mean adding a recordset to a table.
Can anybody help or give me some other solution to this?
thanks alot

[Gaffer]

Sure: What database are you using?

[mrdj1002]

Originally posted by Gaffer
Sure: What database are you using?

I'm using an access db and DAO for codeing

[Gaffer]

You only need to use SQL.

1. Create a query, based on the following SQL:

INSERT INTO myTargetTable
SELECT * FROM mySourceTable IN 'c:\SourceDB.mdb';

2. Remove the records from your source table
DELETE * FROM mySourceTable IN 'c:\SourceDB.mdb';

[mrdj1002]

Originally posted by Gaffer
You only need to use SQL.

1. Create a query, based on the following SQL:

INSERT INTO myTargetTable
SELECT * FROM mySourceTable IN 'c:\SourceDB.mdb';

2. Remove the records from your source table
DELETE * FROM mySourceTable IN 'c:\SourceDB.mdb';

I don't see any TargetTable in these SQL statement. can u give me a simple code? I write the first part & u do the other

dim db1 as DAO.DataBase
dim db2 as DAO.DataBase

Set db1 = DBEngine.OpenDatabase("c:\source.mdb")
Set db2 = DBEngine.OpenDatabase("c:\target.mdb")

ok now what sould i do? open tables whit those SQL statements?

[peet]

db2.execute "INSERT INTO myTargetTable 
SELECT * FROM mySourceTable IN 'c:\SourceDB.mdb'"
 
db2.execute "DELETE * FROM mySourceTable IN 'c:\SourceDB.mdb'"

[mrdj1002]

thanks alot i got it

[chander]

hi guys ... i also got same kind of problem , i want to just copy some records from one recordset to a access table . but prob is a bit different in way that i have tow databse . the source database is Oracle from which i made one recordset . and this recordset i want to add in my access table . Here we gave Insert in to table name and select query . but how i can do it if my source database or table is in oracle and destination table is in Access .

any clues ..