[AccessD] Using IN (SELECT SQL STATEMENT)

John,

<<I have had this issue (a join causes a non-updateable query) in the past
and
never really solved it.  I think this is going to solve that problem for
me.>>

  Just bumped into that myself yesterday as a matter of fact.  Only thin I
could figure is that it was a problem because the join was between a local
table and a table in a SQL Server database.  I know JET, because of it's
key-set driven cursor model will often have problems with a table in a BE
other then JET.

  Workaround I used was IN as well.  Performance seemed fine.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
Sent: Wednesday, December 21, 2005 12:00 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Using IN (SELECT SQL STATEMENT)


I have a situation where I need to be able to update just a subset of
records in a table - where the PK of those records appear in another table.
I created a query to pull a DISTINCT set of those IDs, saved the query and
then joined my existing query to that new query.  Doing this made the query
non-updatable. By using an IN() in the where clause under the ID that I was
trying to join on, the query became updateable again.

In other words, instead of an inner join between DOC_ID and the matching
field in qselDocIDIntblSystemDocument, I used a where clause:

WHERE (((tblDocuments.DOC_ID) In (SELECT SD_IDDOC FROM
qselDocIDIntblSystemDocument)));

I have had this issue (a join causes a non-updateable query) in the past and
never really solved it.  I think this is going to solve that problem for me.

John W. Colby
www.ColbyConsulting.com

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com