How to write SQL code in VBA

[stildawn]

Hi All



If I have some SQL code like this:

INSERT INTO tbl_Changes ( HB, Finished )
SELECT         tbl_Main.HB, tbl_Main.Finished
FROM         tbl_Main RIGHT JOIN tbl_Import_TEMP ON tbl_Main.HB = tbl_Import_TEMP.HB
WHERE         tbl_Main.Finished<>tbl_Import_TEMP.Finished

How do I write this into VBA in a Access Module?

Like I tried this but I think I have messed it up:

Sub test()

Dim strSQL As String
strSQL = "INSERT INTO ChangesTB ( HB, Status, ETA) SELECT Main.HB, Main.Status FROM Main RIGHT JOIN UpdateTB ON Main.HB = UpdateTB.HB WHERE Main.Status<>UpdateTB.Status"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Exit_Command2_Click:
Exit Sub


End Sub

I've edited the code a bit to match actual tables and fields, though I might have messed that up also.

Thanks

[stildawn]

Ok sorry I think I have it by having this code instead:

Sub test()

Dim strSQL As String
strSQL = "INSERT INTO ChangesTB ( HB, Status) SELECT Main.HB, Main.Status FROM Main RIGHT JOIN UpdateTB ON Main.HB = UpdateTB.HB WHERE Main.Status<>UpdateTB.Status"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Exit_Command2_Click:
Exit Sub


End Sub

I'm not entirely sure what "Main.Status FROM Main RIGHT JOIN UpdateTB ON Main.HB = UpdateTB.HB" does exactly?

Basically I'm trying to record changes made to the Main table from the update table?

[orange]

For a good set of tutorials and the mix of SQL and VBA see Martin Green's site.

[ranman256]

The easiest way...let Access do it via visual object sql. No need to 'write' sql when you can drop tables & fields into the grid then click the SQL button.