Using SQL to write in one table

[mfaqueiroz]

Hello!

I recently discovered that using SQL in vba is much more efficient.
I'm searching for a way to transfer information to other table using SQL as well as running some simple cycles.

So, giving an simple example that everyone could understand:
I have two tables one with colours and code of object and other only with objects, I want to complete the second table with the right colour and code of object.


Set Tab1= dbs.OpernRecordset("Tabcolour", DB_OPEN_TABLE)
Set Tab2= dbs.OpernRecordset("TabObjects", DB_OPEN_TABLE)


Tab1.MoveFirst
While not Tab1.EOF
Colour= tab1.Fields(1).Value
Object=tab1.Fields(2).Value
Code=tab1.Fields(3).Value

Tab2.MoveFrist
Whil not Tab2.EOF
Object2=tab2.Fields(1).Value

If Object2=Object1 Then
Tab2.Edit
Tab2.Fields(2).Value= Colour
Tab2.Fields(3).Value = "Check"
Tab2.Fields(4).Value= Code
Tab2.Update

End If
Tab2.MoveNext
Loop
Tab1.Movenext
loop
End Sub


Do you have any suggestion how can i do that on Sql?

I'm vere grateful for any help that you can give me.
I wish a good start of the year to all

 

[Minty]

I think you could simply write a query to do this. Have you tried?
And do you need to store the data twice ? Can't you link the tables to give you the desired results?

 

[mfaqueiroz]

I think you could simply write a query to do this. Have you tried?
And do you need to store the data twice ? Can't you link the tables to give you the desired results?

Hi ! this was an hypothetical example, maybe wasn't the better....
I only want to understand how can i write this kind of code using SQL..things like that:

If Object2=Object1 Then
Tab2.Edit
Tab2.Fields(2).Value= Colour
Tab2.Fields(3).Value = "Check"
Tab2.Fields(4).Value= Code
Tab2.Update

 

[Minty]

You would use an Update Query - so something like (Purely Air Code )

sSql = "UPDATE Table1 SET Field2 = 'Red' WHERE Field3 = 'NotRed'"
Currentdb.Execute sSql

I don't think this is any more efficient than using a recordset though. Others may know better?