Populating table from ADO recordset

[Reid Kell]

I have a VB 6 app that queries one Access 2000 database and populates an ADO
recordset. I then need to transfer this data into an identical, empty table
in another Access 2000 database. What is easiest means of accomplishing
this?

I currently do something like the following, but it just seems there has to
be an easier way. Thanks.

Do Until rsSource.EOF
For x = 0 to 50 '50 columns in source recordset
rsDest.AddNew
rsDest.Fields(0) = rsSource.Fields(0)
Next x
rsSource.MoveNext
Loop

rsDest.UpdateBatch

 

[Dan Artuso]

Hi,
Maybe you can use Select Into .....
SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
FROM source

and bypass recordsets altogether.
Look it up in Help.

 

[Dan Artuso]

Hi,
The way I saw it, you don't need two connections.
You execute the statement against your existing connection.
There's no linked tables involved. externaldatabase is simply
the full path to the other db.
Now, this may not work with ADO, but I'm fairly certain it would
work with DAO.
In any case, why not try it?

--
HTH
Dan Artuso, Access MVP

Not sure what I'm missing here. I'm in a VB project where there are no
linked tables. I can't reference two connections in the same SQL command.

Hi,
Maybe you can use Select Into .....
SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
FROM source

and bypass recordsets altogether.
Look it up in Help.

--
HTH
Dan Artuso, Access MVP

I have a VB 6 app that queries one Access 2000 database and populates

an
ADO

recordset. I then need to transfer this data into an identical, empty table
in another Access 2000 database. What is easiest means of accomplishing
this?

I currently do something like the following, but it just seems there

has
to

be an easier way. Thanks.

Do Until rsSource.EOF
For x = 0 to 50 '50 columns in source recordset
rsDest.AddNew
rsDest.Fields(0) = rsSource.Fields(0)
Next x
rsSource.MoveNext
Loop

rsDest.UpdateBatch

 

[Reid Kell]

Thanks, Dan. I got it to work using the following code! It's interesting
that the destination database path had to take the form
FullPath\DatabaseName.NewTableName. If I used, say, "...INTO
H:\Access\Database2.MDB" the new table is created with the name "MDB."
Weird. I'm not complaining, though. For transferring records between two
Access databases, this beats iterating throuth ADO recordsets any day.
Thanks again.

Public Function Test()
Dim wrkJet As Workspace
Dim Database1 As DAO.Database
Dim strSQL As String

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set Database1 = wrkJet.OpenDatabase("H:\Access\Database1.mdb", False)
strSQL = "SELECT UserID, UserFName INTO H:\Access\Database2.tblUser FROM
tblUser"

Database1.Execute strSQL

Database1.Close
wrkJet.Close

End Function

--
Reid Kell
Lockheed-Martin
(e-mail address removed)

Hi,
The way I saw it, you don't need two connections.
You execute the statement against your existing connection.
There's no linked tables involved. externaldatabase is simply
the full path to the other db.
Now, this may not work with ADO, but I'm fairly certain it would
work with DAO.
In any case, why not try it?

--
HTH
Dan Artuso, Access MVP

Not sure what I'm missing here. I'm in a VB project where there are no
linked tables. I can't reference two connections in the same SQL command.

Hi,
Maybe you can use Select Into .....
SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
FROM source

and bypass recordsets altogether.
Look it up in Help.

--
HTH
Dan Artuso, Access MVP


I have a VB 6 app that queries one Access 2000 database and

populates

an