Insert query does not work in Access

[serviceman]

Hi again,
have a look at this SQL INSERT Statement:

"INSERT ATTENDANCE SELECT 107 as ATT_HDR_ID, STUDENTS.STUDENT_ID AS
STUDENT_ID,2 as EVENT_ID,4 AS LOCATION_ID,'5/10/2006' as EVENT_DAT, '','',0
FROM STUDENTS WHERE STUDENT_ID IN (275,119,135)"

this works great in SQL server, but not at all in Access .mdb...
I've played around with quotes,qoutes, and more #@#$!!!! quotes all morning,
but cannot get this right. The IN values change off listbox values, and they
seem to pass in ok. In SQL server INSERT INTO usually would be for a single
rowset or would need a loop or cursor to advance through each record. Does
Access not use INSERT, or am I missing something stupid?
Andy

 

[Alex Dybenko]

Hi,
I think you have to use Insert Into
you can also use query designer to build append query - access will help you
to create proper SQL

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[serviceman via AccessMonster.com]

Hi Alex,
If INSERT INTO is the way to go, then I assume we need to create a loop to go
through records for each student ID? If that is the case, then what is the
best way to go about it?
Andy

Hi,
I think you have to use Insert Into
you can also use query designer to build append query - access will help you
to create proper SQL

Hi again,
have a look at this SQL INSERT Statement:

[quoted text clipped - 14 lines]

Access not use INSERT, or am I missing something stupid?
Andy

 

[Douglas J. Steele]

There are 2 "flavours" of INSERT INTO. One accepts a Select statement, the
other assumes hard-coded values.

Try:
"INSERT INTO ATTENDANCE SELECT 107 as ATT_HDR_ID, STUDENTS.STUDENT_ID AS
STUDENT_ID,2 as EVENT_ID,4 AS LOCATION_ID,#5/10/2006# as EVENT_DAT, '','',0
FROM STUDENTS WHERE STUDENT_ID IN (275,119,135)"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Alex,
If INSERT INTO is the way to go, then I assume we need to create a loop to
go
through records for each student ID? If that is the case, then what is
the
best way to go about it?
Andy

Hi,
I think you have to use Insert Into
you can also use query designer to build append query - access will help
you
to create proper SQL

Hi again,
have a look at this SQL INSERT Statement:

[quoted text clipped - 14 lines]

Access not use INSERT, or am I missing something stupid?
Andy

 

[serviceman via AccessMonster.com]

Hey Douglas,
Thanks for the input. I played with it for several hours, and FINALLY got the
complete deal:
SQLStr = "INSERT INTO ATTENDANCE (ATT_HDR_ID,STUDENT_ID,EVENT_ID,LOCATION_ID,
EVENT_DAT,COMMENT,PHYSICAL_LIMITS,EVENT_FEE) SELECT " & Forms!ATTENDANCE_HDR.
ATT_HDR_ID & " as ATT_HDR_ID, STUDENTS.STUDENT_ID AS STUDENT_ID," & Forms!
ATTENDANCE_HDR.EVENT_ID & " as EVENT_ID," & Forms!ATTENDANCE_HDR.LOCATION_ID
& " AS LOCATION_ID,'" & Forms!ATTENDANCE_HDR.EVENT_DAT & "' as EVENT_DAT, '',
'',0 FROM STUDENTS WHERE STUDENT_ID IN (" & MySelected2 & ")"

The fix that did the trick is the ( ) around the insert fields and NOT around
the insert VALUES. This really had me screwed up since in SQL when you wrap
the fields you wrap the values....
Thanks again for the help!
Andy

There are 2 "flavours" of INSERT INTO. One accepts a Select statement, the
other assumes hard-coded values.

Try:
"INSERT INTO ATTENDANCE SELECT 107 as ATT_HDR_ID, STUDENTS.STUDENT_ID AS
STUDENT_ID,2 as EVENT_ID,4 AS LOCATION_ID,#5/10/2006# as EVENT_DAT, '','',0
FROM STUDENTS WHERE STUDENT_ID IN (275,119,135)"

Hi Alex,
If INSERT INTO is the way to go, then I assume we need to create a loop to

[quoted text clipped - 15 lines]