a_ud (4/11/2013)
The odd thing (from an Access point of view) is that the row, to me, is identified by the condition.Essentially what I was doing was used some VBA on Access to run this SQL on one of the tables:
"DELETE from tblScores WHERE VolunteerID='A1' AND NoWeek=3"
on a table tblScores having a structure like this:
VolunteerID | NoWeek | ScoreA1 1 3
A1 2 5
A1 3 0
A2 1 11
........... etc
so the condition "WHERE VolunteerID='A1' AND NoWeek=3" only identifies one record in Access (but, oddly enough, SSIS rejects this). That's odd to me, but after adding the ID column it worked perfectly.
Access has now way of knowing there is only 1 row "WHERE VolunteerID='A1' AND NoWeek=3" because there is no primary key. If you want to update or delete that row how can the engine know which row it is? It can't use your where clause because that can return more than 1 row and you obviously wouldn't want it to make those changes to all rows that meet the condition. You want that to happen ONLY to the specific row and since there is nothing to uniquely identify the row (primary key or unique index) it is impossible. If the combination of VolunteerID and NoWeek makes a unique combination you could make that the primary key.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/