SQL not working in VBA

[JPinter]

I am putting out this new thread, because the response May make a difference, but it is not why it's not working. I only had the first SQL statement in VBA when it didn't work. I Just threw the second in there to make sure if there were something wrong with both that I would get both answers in one.


JPinter (TechnicalUser) Aug 15, 2002
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 (Programmer) Aug 15, 2002
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

 

[jebry]

Hi Jeff!

Maybe I'm missing something, but I thought an append query needs to start with the keyword INSERT. Is this just part of the code or is insert unnecessary?

hth
Jeff Bridgham
bridgham@purdue.edu

 

[JPinter]

I think INSERT did not get copied, but I found way qround using the SQL statement in VB i just made 2 strings one for each query in VB, so the the actual queries run one after the other.