Vba+sql = ack!

[mgmirvine]

Hi All,



I am trying to perform an INSERT from a query into a table and am having no luck. Now, I am no pro, so forgive me in advance. I've tried searching online and just can't seem to figure it out. I have other INSERT instances from one table to another working just fine. Here's my code. Any pointers or help you can provide is greatly appreciated!


Private Sub TestButton_Click()

Dim db As Database
Set db = CurrentDb()

db.Execute "INSERT INTO TestTable ( Column1, Column2, Column3, Column4, Column5 ) SELECT MyTestQuery.Column1, MyTestQuery.Column2, [TempVars]![TempTest3] AS Column3, 'TestText' AS Column4, Date() AS Column5 FROM MyTestQuery WHERE (MyTestQuery.Column2='TestQualifier') AND MyTestQuery.Column6>=[Forms]![TestForm]![DateQualifier]) "

Set db = Nothing


End Sub


Thank you!!!

[June7]

Concatenate the variables. There is also an unpaired paren.

db.Execute "INSERT INTO TestTable (Column1, Column2, Column3, Column4, Column5) SELECT Column1, Column2, " & [TempVars]![TempTest3] & " AS Column3, 'TestText' AS Column4, #" & Date() & "# AS Column5 FROM MyTestQuery WHERE Column2='TestQualifier' AND Column6>=#" & [Forms]![TestForm]![DateQualifier] & "#"

I am not positive the date delimiters (#) are required.

[mgmirvine]

I've tried this with no luck! I've even tried to simplify the code just to debug (as noted below), with no luck.

Private Sub TestButton_Click()

Dim db As Database
Set db = CurrentDb()

db.Execute "INSERT INTO TestTable (Column1, Column2 ) SELECT MyTestQuery.Column1, MyTestQuery.Column2 FROM MyTestQuery WHERE MyTestQuery.Column2='TestQualifier' "

Set db = Nothing


End Sub

[jzwp11]

I typically assign the SQL text of a query to a variable and then use the debug.print statement to display the text in the immediate window for troubleshooting purposes. The main thing I see with your SQL text is that you are not delimiting your variables (date values by # signs, text values by single quotes). Variables cannot be within the double quotes of the string, they must be concatenated to the string.

"INSERT INTO TestTable ... SELECT MyTestQuery.Column1, MyTestQuery.Column2, [TempVars]![TempTest3] AS Column3, 'TestText' AS Column4,#" & Date() & "# AS Column5 FROM MyTestQuery WHERE...

It looks like [TempVars]![TempTest3] is a variable, but I do not know what type. If it is numeric, then this would be appropriate:

"INSERT INTO TestTable ... SELECT MyTestQuery.Column1, MyTestQuery.Column2," & [TempVars]![TempTest3] & " AS Column3, 'TestText' AS Column4,#" & Date() & "# AS Column5 FROM MyTestQuery WHERE...

[June7]

Don't know why that won't work. Would have to analyse project.

[CraigDolphin]

Maybe try the following:

Private Sub TestButton_Click()
On Error GoTo Err_TestButton_Click 
  Dim strSQL as string
 
  strSQL="INSERT INTO TestTable ( Column1, Column2 ) " & _
             "SELECT MyTestQuery.Column1 AS Expr1, MyTestQuery.Column2 AS Expr2 " & _
             "FROM MyTestQuery " & _
             "WHERE ((([MyTestQuery].[Column2])='TestQualifier'));"
  Docmd.Setwarnings False 
  Docmd.Runsql strSQL
  Docmd.Setwarnings True
 
Exit_TestButton_Click:
    Exit Sub
Err_TestButton_Click:
    Docmd.Setwarnings True
    MsgBox Err.Number & " (" & Err.Description & ") " & vbcrlf & "Your SQL:" & vbcrlf & strSQL, vbinformation
    Resume Exit_TestButton_Click 
End Sub

[mgmirvine]

All, thank you greatly for the help and advice.

Craig, your method worked perfectly. I truly appreciate your help!