[dba-SQLServer] [AccessD] SQL Server - Query non-updateable

Further to this, I have discovered that if I build a temp table inside of the access fe and do the 
join it is un-updateable.  However if I use the temp table in the IN() clause it is now updateable. 
  So it is something about using the stored procedure in the IN() that causes the query to become 
un-updateable.

John W. Colby
www.ColbyConsulting.com

On 7/7/2011 8:03 AM, jwcolby wrote:
> I have a selection query for a bound form. If I just do a select xyz from tblInmate it works of
> course. I want to select a subset of inmates that reflect those that I work with (specific camps).
>
> I have a tblVolunteerCamps (the camps that a volunteer works with) and I built a stored procedure
> out in SQL Server that selects the IDs of inmates at those camps. I feed the SP the volunteer ID and
> back comes the campIDs and the inmateIDs in those camps.
>
> I had read (on this list) that if I used IN (SELECT ID from QueryXYZ) in the where clause it would
> allow the query to be editable but doing so is turning my query into a non-updatable query.
>
> SELECT TblInmate.* from tblInmate
> WHERE (INM_Active <> 0) AND
> (INM_Location IN (SELECT CMP_LOCCODE FROM qspVolCampIDs))
>
> If I remove the IN clause, the query is updateable.
>
> I really need to filter to just the camps the volunteer works with and I am wondering how to
> accomplish this. In the past I would try to JOIN the main query to the selection filter and that
> caused non-updateable. I was told to use the IN(SELECT) which has worked in most cases in the past.
>
> Any clue why not now and how to go about filtering and keeping it updateable?
>