First of all, the Execute method of the ADODB.Connection object, (with a
'select ...' statement), returns, by default a ForwardOnly cursor. In this
context your AddNew method is illegal and is correctly throwing the error.
Secondly, lets talk about the type of database, (you only mention that you
are working on 2 databases and give no details of the database types).
If you are using SQL Server and the 2 databases are parented in the same
server instance then you can simply do an 'select * into
<db2_name>.<table_name> from <db1_name>.<table_name>'.
If you are using 2 Jet databases then there is a similar mechanism and you
will be able to find the syntax in the documentation.
If the target database is in SQL Server and the source database is in Jet,
(or Oracle or any other database that has an OLEDB Provider) then you can
link the source database and then use the 'select * into
<db2_name>.<table_name> from <db1_name>.[<catalog_name>]..<table_name>'
style syntax.
If the target database is in Jet then the chances are pretty good that you
will be able to link the source table via ODBC and simply do a 'select *
into <table1> from <table2>'
I think you find that this approach will solve your problem.
Note: The fact that the mechanism 'select ... into ... from ...' is part of
the SQL standard should have prompted you to investigate it's use in your
environment.
> Also, can you verify my code for me?
> Thanx
> the error message is
> ------------------------------------------------------------------
> Run-time error '3251':
> Current Recordset does not support updating. This may be a limition of
the
> provider, or of the selected locktype.
> -------------------------------------------------------------------
> Dim cnReal As New ADODB.Connection
> Dim rsReal As New ADODB.Recordset
> Dim cnGold As New ADODB.Connection
> Dim rsGold As New ADODB.Recordset
> Dim sqlReal As String
> Dim sqlGold As String
> Dim i As Integer
> cnReal.Open "dsn=realConnection"
> cnGold.Open "dsn=goldConnection"
> sqlReal = "select * from tb1"
> sqlGold = "select * from tb2"
> Set rsReal = cnReal.Execute(sqlReal)
> Set rsGold = cnGold.Execute(sqlGold)
> rsGold.AddNew <------------- Error in here
> For i = 0 To rsReal.Fields.Count
> rsGold(i) = rsReal(i)
> Next
> rsGold.Update
> > >Hi,
> > >I am currently working on two databases.
> > >Using VB, I am able to get recordset from one database and I need to
> > >transfer that recordset to the other database.
> > >I know I can get those recordset and insert them into the other using
> loops
> > >and insert one row by another.
> > >It just takes too much time, TOO MUCH.
> > >If this is the only way, I have to stick with it but is there any other
> way
> > >to insert several rows at onece???
> > >also, this table has too many fields( about 30 ~ 50 ) and I don't want
to
> > >write all the insert statement if avoidable.
> > >thanx
> > Forget about Insert statements
> > Assuming that you have a Recordset for the database with the data and
> > a recordset for receiving the data and both tables have the same
> > structure, all you need is a for next loop.
> > NewRecordset.addnew
> > For a = 0 to Oldrecordset.fields.count
> > NewRecordset(a) = oldRecordset(a)
> > next
> > NewRecordset.update
> > Stuart Sutherland
by