How to copy recordsets from one table to another

[fish_8888]

I have 2 data objects:

Data1 (These records are temporary)
Master_db (These records are permanent)

What I want to do is take all the records in Data1 and add them to Master_db via a comand button. Is there a way to specify a record number
Like:

Master_db.recordset.addnew
Data1.recordset (1) = Master_db.recordset


Thanks in advance for any help!

[RITIK]

I have 2 data objects:

Data1 (These records are temporary)
Master_db (These records are permanent)

What I want to do is take all the records in Data1 and add them to Master_db via a comand button. Is there a way to specify a record number
Like:

Master_db.recordset.addnew
Data1.recordset (1) = Master_db.recordset


Thanks in advance for any help!

To make a true copy of a recordset, I used the Stream object from ADO, and transferred the recordset in memory, severing the link between the two recordsets. If anyone comes up against this problem, this is the code. It requires a reference to ActiveX Data Objects version 2.x


Code:Public Function Clone(ByVal oRS As ADODB.Recordset, Optional ByVal eLockType As ADODB.LockTypeEnum = adLockUnspecified) As ADODB.Recordset

Dim oStream As ADODB.Stream: Set oStream = New ADODB.Stream
Dim oRsClone As ADODB.Recordset: Set oRsClone = New ADODB.Recordset

oRS.Save oStream
oRsClone.Open oStream, , , eLockType
Set Clone = oRsClone

Set oRsClone = Nothing

End Function

[sweet_babylhyn]

I'd never try the adodb.stream

Id do this by this by adodb.recordset

dim rs s adodb.recorset
dim rsTemp as adodb.recordset

set rs= new adodb.recorset
rs.open "Select * ......",cnn,adopenstatic, adlockreadonly
while not rs.eof
set rstemp= new adodb.recordset
rstemp.open "Select * From temptable where 1=0",cnn,adopenkeyset,adlockkeyset
rstemp.addnew
rstemp!field1=rs!field1
rstemp.update
rstemp.close
set rstemp= nothing
rs.movenext
wend
rs.close
set rs= nothing

[dglienna]

when does 0=1 ?

btw - you can't just 'add a record' from one table to another, unless the fields match, and are in the same order and type.

Take a look at the example in my signature. It shows how to read and write recordsets the correct way. Source included along with the article

[sweet_babylhyn]

I used Where 1=0 to open the table with blank record. Its very useful if the table has more than 100,000 records. It open the table very fast.

[dglienna]

OIC. That makes sense. I didn't catch that you were adding a record.

I've seen WHERE 1=1 which is all records. I guess that's the opposite

[sweet_babylhyn]

when does 0=1 ?

btw - you can't just 'add a record' from one table to another, unless the fields match, and are in the same order and type.

Take a look at the example in my signature. It shows how to read and write recordsets the correct way. Source included along with the article

How about in adodb.recordset
rs!Field1= rstemp!Field2

[George1111]

Thanks Sweet_Babylhyn for your

Select ... where 1=0

This is just so simple - As you say, there is no delay when you want to add a record to a table

I have been battling with the old Select * from ... which of course is a disaster when your file has many records in it

Thanks Again

[George1111]

Here's an example which would allow copying of one recordset to another, but it requires that the Field Definitions already exist in the destination file.

This is also useful when the destination file has more fields than the source file, and also when copying Tables from one database to another.

           Set RecordSetIn = DatabaseIn.OpenRecordset(TableNameIn, dbOpenTable)
            Set RecordSetOut = DatabaseOut.OpenRecordset(TableNameOut, dbOpenTable)
 
'Optional Clear the Destination Table           
            StrSql = "Delete * From " & TableNameOut
            DatabaseOut.Execute StrSql
 
'Now Copy and Insert Table1 to Table2           
            RecordSetIn.MoveFirst
            While RecordSetIn.EOF = False
                DoEvents
                 RecordSetOut.AddNew
                For Each Field In DatabaseIn.TableDefs(tIndex).Fields
                    RecordSetOut(Field.Name) = RecordSetIn(Field.Name)
                Next Field
                RecordSetOut.Update
                RecordSetIn.MoveNext
            Wend
            RecordSetIn.Close
            RecordSetOut.Close

[dglienna]

btw - you can't just 'add a record' from one table to another, unless the fields match, and are in the same order and type.

You miss that?

[DataMiser]

How about a simple insert into statement?

Something like...

 
 
db.execute "insert into Master_Db ([field list]) select [Fieldlist] from Data1"

Where [fieldlist] is a comma seperated list of fieldnames to be selected and inserted. If the tables are identical * may work as well.

more info

[DataMiser]

Not sure what you mean by specifing a record number.

The insert into... select from does support the where clause so you can limit the data that is inserted with the proper where statement if that is what you need to do.

[dglienna]

Just need a

Insert Into MasterTable (field1,field2,field3...)

if you supply all of the fields in the correct order