Adding a Recordset to a Table??

Printable View

Dec 27th, 2002, 08:02 AM
mrdj1002

Adding a Recordset to a Table??

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
Dec 27th, 2002, 08:04 AM
Gaffer

Sure: What database are you using?
Dec 27th, 2002, 08:08 AM
mrdj1002

Quote:

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

I'm using an access db and DAO for codeing
Dec 27th, 2002, 08:15 AM
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';
Dec 27th, 2002, 08:34 AM
mrdj1002

Quote:

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?

VB Code:

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

Dec 27th, 2002, 08:54 AM
mrdj1002

thanks alot i got it
Mar 17th, 2003, 01:07 AM
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 ..