insert and union all

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there

Im having a few problems when attempting to execute the following sql:

"
strSQL = "INSERT INTO tblEnquiry_Venue (venueid, enquiryid) SELECT " &
trim(venueArray(0)) & ", " & trim(lastID) & " as query1 "
' can assume that there is an array as its checked before calling this
function
' will have to move to the 2nd item in the array as first inserted above
for i = 1 to UBound(venueArray)
strSQL = strSQL & " UNION ALL SELECT " & trim(venueArray(i)) & ", " &
trim(lastID) & " as n" & i
next
W

it keeps saying:

"
INSERT INTO tblEnquiry_Venue (venueid, enquiryid) SELECT 2, 179 as query1
UNION ALL SELECT 3, 179 as n1 UNION ALL SELECT 5, 179 as n2

form submitted -- Microsoft JET Database Engine: (-2147467259) Query input
must contain at least one table or query. - -531762129
"

ive tried everything from putting brackets - () and [] - around the sub
querys to naming them.

any help kindly appreciated!

regards,
jody
 
Try using the alternate syntax
FOR i = 0 to Ubound(VenueArray)

strSQL = "INSERT INTO tblEnquiry_Venue (venueid, enquiryid) " & _
" Values (" & trim(venueArray(i)) & ", " & trim(lastID) &
" )"

'Execute your query here once for each time through the loop
Next i

You could also just open a recordset and add records to it in the loop.
 
Back
Top