Copy some values from one table into another one??

[rebellious]

Here is the VBA code.
I want to copy some data from "tEtap" named table into "tSicil" named table.
Where is the problem?

Sub isle()
Dim Conn As ADODB.Connection
Dim rstEtap As ADODB.Recordset
Dim rstSicil As ADODB.Recordset
Dim strConn As String

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Datasource=" & CurrentProject.Path & "\Etap.mdb"

Set rstEtap = New ADODB.Recordset
Set rstSicil = New ADODB.Recordset
rstEtap.Open "Select * from tEtap", strConn, adOpenForwardOnly, adLockReadOnly
rstSicil.Open "Select * from tSicil", strConn, adOpenForwardOnly, adLockOptimistic

Do While Not rstEtap.EOF
rstSicil.Fields("Hakem").Value = rstEtap.Fields("Hakem").Value
rstSicil.Fields("Tarih").Value = rstEtap.Fields("Tarih").Value
rstSicil.Fields("Lig").Value = rstEtap.Fields("Lig").Value
rstSicil.Fields("EvSahibi").Value = rstEtap.Fields("EvSahibi").Value
rstSicil.Fields("Misafir").Value = rstEtap.Fields("Misafir").Value
rstSicil.Fields("H") = True
rstSicil.Fields("Gozlemci") = rstEtap.Fields("Gozlemci").Value
rstEtap.MoveNext
rstSicil.MoveNext
Loop
Conn.Close

Set rstEtap = Nothing
Set rstSicil = Nothing
End Sub

 

[WayneRyan]

Rebellious,

If the two tables are REALLY related, you don't have to store a copy of the
data, just link the tables on the Primary Key.

Your recordsets are probably not "in synch", they are just random retrievals
from your tables, and you must explicitly ".Update" the rstSicil recordset.

Sub isle()
Dim Conn As ADODB.Connection
Dim rstEtap As ADODB.Recordset
Dim rstSicil As ADODB.Recordset
Dim strConn As String

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Datasource=" & CurrentProject.Path & "\Etap.mdb"

Set rstEtap = New ADODB.Recordset
Set rstSicil = New ADODB.Recordset ' Remove
'
' The following DOES open up two recordsets, but they are in RANDOM order
' meaning that the 1st record of rstEtap probably WILL NOT corresond to 
' its counterpart in rstSicil!
'
rstEtap.Open "Select * from tEtap", strConn, adOpenForwardOnly, adLockReadOnly
rstSicil.Open "Select * from tSicil", strConn, adOpenForwardOnly, adLockOptimistic ' Remove, open for
                                                                                   ' each row of rstEtap
'
' You can definitely traverse rstEtap, but for each record, you must find the
' appropriate record in rstSicil
'
Do While Not rstEtap.EOF
   '
   ' Here is where you should open rstSicil, based on rstEtap
   ' Don't know your primary keys, but the following is the general idea
   '
   Set rstEtap = New ADODB.Recordset
   rstSicil.Open "Select * from tSicil Where [ThePrimaryKey] = " & rstEtap.Fields("ThePrimaryKey"), strConn
   rstSicil.Fields("Hakem") = rstEtap.Fields("Hakem") ' <-- ".Value" is the default, not needed
   rstSicil.Fields("Tarih") = rstEtap.Fields("Tarih")
   rstSicil.Fields("Lig") = rstEtap.Fields("Lig")
   rstSicil.Fields("EvSahibi") = rstEtap.Fields("EvSahibi")
   rstSicil.Fields("Misafir") = rstEtap.Fields("Misafir")
   rstSicil.Fields("H") = True
   rstSicil.Fields("Gozlemci") = rstEtap.Fields("Gozlemci")
   rstSicil.Update ' <-- Must update the data
   rstSicil.Close
   Set rstSicil = Nothing
   rstEtap.MoveNext
   rstSicil.MoveNext ' <-- Not needed
   Loop
Conn.Close

Set rstEtap = Nothing
Set rstSicil = Nothing ' Remove
End Sub

hth,
Wayne

 

[rebellious]

Actually, the error is,
Runtime error '-2147467259 (80004005)
ISAM can not be found.

 

[KeithG]

why not use an append query?