SQL Statement not working in VBA

[JPinter]

I tried copying two SQL Statements from an append query and delete query, because I want to move all records into a table and then delete the records I moved. It works fine on the querys, and when they were shorter they worked fine in a macro, but I can not get them to work in VBA. Can you tell what is wrong with this statement that it will not work.
Thanks,
Jeff



Private Sub Command63_Click()

Dim SQL As String

SQL = "INTO tblClosedjobs ( Description, [Bin#], Supplier, [Units of Measure], [Min], [Max], [GL#], [GL2#], Cost ) " & _
"SELECT tblPartlbr.*, tblPart.Description, tblPart.[Bin#], tblPart.Supplier, tblPart.[Units of Measure], tblPart.Min, tblPart.Max, tblPart.[GL#], tblPart.[GL2#], tblPart.Cost " & _
"FROM tblPart INNER JOIN tblPartlbr ON tblPart.[Part-Labor#] = tblPartlbr.[Part-Labor#]; " & _
"DELETE tblPartlbr.* " & _
"FROM tblPartlbr;"

DoCmd.RunSQL SQL

End Sub

 

[KyleS]

Jeff,

I belive the problem might be that you need to seperate your SQL Statements,

So try this:

Private Sub Command63_Click()

Dim SQL As String

SQL = "INTO tblClosedjobs ( Description, [Bin#], Supplier, [Units of Measure], [Min], [Max], [GL#], [GL2#], Cost ) " & _
"SELECT tblPartlbr.*, tblPart.Description, tblPart.[Bin#], tblPart.Supplier, tblPart.[Units of Measure], tblPart.Min, tblPart.Max, tblPart.[GL#], tblPart.[GL2#], tblPart.Cost " & _
"FROM tblPart INNER JOIN tblPartlbr ON tblPart.[Part-Labor#] = tblPartlbr.[Part-Labor#];

DoCmd.RunSQL SQL

SQL = "DELETE tblPartlbr.* " & _
"FROM tblPartlbr;"

DoCmd.RunSQL SQL
End Sub Kyle