Please help with INSERT INTO using VBA

[Guest]

Basically what I am trying to do is use VBA to generate an SQL statement/query based on a table called (LossTable) and then take the result of this select statement and place it into a temporary table (tempdata). The temporary table already exists and contains the same data structure as the (LossTable) table, just no data

I know that the Select part of my SQL worked prior to adding the INSERT INTO portion. It seems that the whole SQL segment, including the INSERT INTO, compiles, but when it gets to the execute step it blows up. I get "syntax error in INSERT INTO statement" error 3134.

Can anyone help me figure out how to fix this problem? Also, once I get that problem fixed, is there an easy way to use VBA to delete all of the data in a table without deleting the structure and field settings?

thanks in advance for any help
ti

This is all of the pertinent code to my question. The arrays and other variables are defined and are assigned values, but since that's not part of the problem, it's not included

Dim db As Databas

Set db = CurrentD

strSQL = "INSERT into tempdata (Plant,Area,SubArea,AreaID,Date,Breakdown,Changeover,Tool,Startup,"
& "MinorStoppage,Speed,Defect,Rework,ScheduledDownTime,"
& "Management,Motion,LineBalance,MonitoringAdjust,Scrap,"
& "DieJigFixture,Energy)"
& " SELECT Plant,Area,SubArea,AreaID,Date,Breakdown,Changeover,Tool,Startup,"
& "MinorStoppage,Speed,Defect,Rework,ScheduledDownTime,"
& "Management,Motion,LineBalance,MonitoringAdjust,Scrap,"
& "DieJigFixture,Energy"
& "FROM LossTable "
& "WHERE AREA IN ('" & areaarray(1) & "',"
& " '" & areaarray(2) & "',"
& " '" & areaarray(3) & "',"
& " '" & areaarray(4) & "',"
& " '" & areaarray(5) & "',"
& " '" & areaarray(6) & "')"
& "AND PLANT IN ('" & plantarray(1) & "','" & plantarray(2) & "')"
& "AND DATE = " & msg & "

db.Execute strSQL, dbFailOnError

 

[Marshall Barton]

Basically what I am trying to do is use VBA to generate an SQL statement/query based on a table called (LossTable) and then take the result of this select statement and place it into a temporary table (tempdata). The temporary table already exists and contains the same data structure as the (LossTable) table, just no data.

I know that the Select part of my SQL worked prior to adding the INSERT INTO portion. It seems that the whole SQL segment, including the INSERT INTO, compiles, but when it gets to the execute step it blows up. I get "syntax error in INSERT INTO statement" error 3134.

Can anyone help me figure out how to fix this problem? Also, once I get that problem fixed, is there an easy way to use VBA to delete all of the data in a table without deleting the structure and field settings?

thanks in advance for any help,
tim

This is all of the pertinent code to my question. The arrays and other variables are defined and are assigned values, but since that's not part of the problem, it's not included.


Dim db As Database

Set db = CurrentDb

strSQL = "INSERT into tempdata (Plant,Area,SubArea,AreaID,Date,Breakdown,Changeover,Tool,Startup," _
& "MinorStoppage,Speed,Defect,Rework,ScheduledDownTime," _
& "Management,Motion,LineBalance,MonitoringAdjust,Scrap," _
& "DieJigFixture,Energy)" _
& " SELECT Plant,Area,SubArea,AreaID,Date,Breakdown,Changeover,Tool,Startup," _
& "MinorStoppage,Speed,Defect,Rework,ScheduledDownTime," _
& "Management,Motion,LineBalance,MonitoringAdjust,Scrap," _
& "DieJigFixture,Energy" _
& "FROM LossTable " _
& "WHERE AREA IN ('" & areaarray(1) & "'," _
& " '" & areaarray(2) & "'," _
& " '" & areaarray(3) & "'," _
& " '" & areaarray(4) & "'," _
& " '" & areaarray(5) & "'," _
& " '" & areaarray(6) & "')" _
& "AND PLANT IN ('" & plantarray(1) & "','" & plantarray(2) & "')" _
& "AND DATE = " & msg & ""

db.Execute strSQL, dbFailOnError

You're missing a space at the end of (or start of) some
lines.

This kind of issue is usually pretty obvious when you use
Debug,Print strSQL before executing the query.

 

[Guest]

I tried using the Debug statement that you suggested, but it seems to have been ignored. I still get the error 3134.

I'm not sure what spaces you were talking about, but I checked to make sure that all the lines were the same, and that also seemed to have no effect

Any other ideas, or am I doing something wrong

thank
Ti

When I run the Select statement in this format, it sends the results to the list box with no difficulty, so what is different when you use the "insert into" command?

checkedareas = "SELECT Plant,Area,SubArea,AreaID,Date,Breakdown,Changeover,Tool,Startup,"
& "MinorStoppage,Speed,Defect,Rework,ScheduledDownTime,"
& "Management,Motion,LineBalance,MonitoringAdjust,Scrap,"
& "DieJigFixture,Energy "
& "FROM LossTable "
& "WHERE DATE = " & msg & ""
& "AND AREA IN ('" & areaarray(1) & "',"
& " '" & areaarray(2) & "',"
& " '" & areaarray(3) & "',"
& " '" & areaarray(4) & "',"
& " '" & areaarray(5) & "',"
& " '" & areaarray(6) & "')"
& "AND PLANT IN ('" & plantarray(1) & "','" & plantarray(2) & "')

Me.losslist.RowSource = checkedareas

 

[Marshall Barton]

I tried using the Debug statement that you suggested, but it seems to have been ignored. I still get the error 3134.

Ignored? Did you use it like this?

strSQL = "INSERT . . .
Debug.Print strSQL
dn.Execute . . .

Was the Debug Window open (Ctrl+g)?

I'm not sure what spaces you were talking about, but I checked to make sure that all the lines were the same, and that also seemed to have no effect.

The query below has a space after Energy, your Insert query
doesn't hve a space after Energy or before From. There are
a couple places where it will end up with )AND but a space
is probably not required there.