Update table based on another table

[JPaulo]

Hi all;
I have 2 tables with 96 fields each, tabelaA and tabelaB
I need to update the tabelaB where some 96 fields of data are different from tabelaA

Is possible?

can help please?

 

[JPaulo]

I can not make this sub work

Please help me

Public Sub UpdateTable()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rs = CurrentDb.OpenRecordset("Select * from auxBancoScheda order by [SK Nº] ASC")
Set rst = CurrentDb.OpenRecordset("Select * from BancoScheda order by [SK Nº] ASC")

For nIndex = 0 To 95
rst.Edit
If rst.Fields(nIndex).Value <> rs.Fields(nIndex).Value Then
rst.Update
Else
Exit Sub
End If
Next
rst.MoveNext
rs.MoveNext
Set rst = Nothing
Set rs = Nothing
End Sub

 

[FoFa]

Well you need to move the values from one recordset to the recordset you are going to update.
Not having done this, but don't see why it should not work, try:
rst.Fields(nIndex).Value = rs.Fields(nIndex).Value

Put your update after the loop.

 

[jdraw]

I can not make this sub work

Please help me

Public Sub UpdateTable()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rs = CurrentDb.OpenRecordset("Select * from auxBancoScheda order by [SK Nº] ASC")
Set rst = CurrentDb.OpenRecordset("Select * from BancoScheda order by [SK Nº] ASC")

For nIndex = 0 To 95
rst.Edit
If rst.Fields(nIndex).Value <> rs.Fields(nIndex).Value Then
rst.Update
Else
Exit Sub
End If
Next
rst.MoveNext
rs.MoveNext
Set rst = Nothing
Set rs = Nothing
End Sub

jpaulo,

For what its worth, here are a few thoughts.
I would set it up in 2 stages and you may want to backup your data, and add some Onerror code.

1) Create a test to make sure your procedure would change the values as it should(as you have programmed). Just debug.print a few fields in a couple of records before going to a real Update.

2) Do the actual update once you have confirmed the process is doing what you intend. And adjust the routine as FoFa said (see the red lines)

As I see the code,
-there is nothing setting the value in the rst field
-there is no code to return to the For Loop to process the next records
- do the Update at the record level (after the For Loop)
- do the rst.edit at the record level (outside the For Loop)

Public Sub UpdateTable()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rs = CurrentDb.OpenRecordset("Select * from auxBancoScheda order by [SK Nº] ASC")
Set rst = CurrentDb.OpenRecordset("Select * from BancoScheda order by [SK Nº] ASC")
[COLOR="Red"]
Do while not rst.eof
rst.Edit[/COLOR]
For nIndex = 0 To 95
If rst.Fields(nIndex).Value <> rs.Fields(nIndex).Value Then
[COLOR="Red"]'
'fields aren't equal so, replace the field value in rst 
'with the field value in rs 
rst.Fields(nIndex).Value =  rs.Fields(nIndex).Value
[/COLOR]
Else
End If
Next
[COLOR="Red"]rst.Update[/COLOR]
rst.MoveNext
rs.MoveNext
[COLOR="Red"]Loop
rs.close
rst.close[/COLOR]
Set rst = Nothing
Set rs = Nothing
End Sub