470,594 Members | 1,505 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,594 developers. It's quick & easy.

Workarounds to Access update restrictions

Hi all,

Over the years, I have had to keep dealing with the same Access restriction -
that you can't update a table in a statement that joins it to another
non-updateable query or employs a non-updateable subquery. I run across this
problem again and again, and yet I've never come up with a single, universal
work-around.

I'm wondering what other people here are doing.

Options I've used and disliked:
1. Invoke a domain lookup function from within the query - slow.
2. Create a temporary table out of the non-updateable portion - bloats the
database, or requires complex code to employ a temporary database - requires
strict enforcement of only one instance of same copy of front-end unless
temporary database is employed.
3. Loop through one recordset in code, build an In(...) list to query the
other, and employ the same sort order. Loop through the recordsets in
parallel - messy, and slow (though not as slow as using domain functions) and
not good with large updates.
Nov 12 '05 #1
10 9604
On Mon, 17 Nov 2003 17:39:35 GMT, Steve Jorgensen
<no****@nospam.nospam> wrote:

If the query can't be rewritten, I use option 2. Disk space is cheap.
-Tom.

Hi all,

Over the years, I have had to keep dealing with the same Access restriction -
that you can't update a table in a statement that joins it to another
non-updateable query or employs a non-updateable subquery. I run across this
problem again and again, and yet I've never come up with a single, universal
work-around.

I'm wondering what other people here are doing.

Options I've used and disliked:
1. Invoke a domain lookup function from within the query - slow.
2. Create a temporary table out of the non-updateable portion - bloats the
database, or requires complex code to employ a temporary database - requires
strict enforcement of only one instance of same copy of front-end unless
temporary database is employed.
3. Loop through one recordset in code, build an In(...) list to query the
other, and employ the same sort order. Loop through the recordsets in
parallel - messy, and slow (though not as slow as using domain functions) and
not good with large updates.


Nov 12 '05 #2
no****@nospam.nospam (Steve Jorgensen) wrote in
<ei********************************@4ax.com>:
Over the years, I have had to keep dealing with the same Access
restriction - that you can't update a table in a statement that
joins it to another non-updateable query or employs a
non-updateable subquery. I run across this problem again and
again, and yet I've never come up with a single, universal
work-around.

I'm wondering what other people here are doing.

Options I've used and disliked:
1. Invoke a domain lookup function from within the query - slow.
2. Create a temporary table out of the non-updateable portion -
bloats the database, or requires complex code to employ a
temporary database - requires strict enforcement of only one
instance of same copy of front-end unless temporary database is
employed.
3. Loop through one recordset in code, build an In(...)
list to query the other, and employ the same sort order. Loop
through the recordsets in parallel - messy, and slow (though not
as slow as using domain functions) and not good with large
updates.


Can't correlated subqueries sometimes give you the results you
need?

I've hardly ever needed this kind of thing for an ongoing purpose,
and since I work only with Jet data, I have the DISTINCTROW
predicate available to me, which takes care of quite a few of these
circumstances.

I go the temp table route, which isn't that hard to maintain, as I
just put the temp table in a tmp.mdb permanently living in the same
folder as the front end, and never bother to compact it, ever. It
has not been an issue in any app I've ever built like this and I've
been doing this for at least 5 years.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #3
On Tue, 18 Nov 2003 03:23:32 GMT, dX********@bway.net.invalid (David W.
Fenton) wrote:

....

Can't correlated subqueries sometimes give you the results you
need?
Only if the subquery is updateable. The issue usually comes up with
aggregates.
I've hardly ever needed this kind of thing for an ongoing purpose,
and since I work only with Jet data, I have the DISTINCTROW
predicate available to me, which takes care of quite a few of these
circumstances.
Hmm, I thought that was no longer a meaningful option. I'll see if it has any
impact.
I go the temp table route, which isn't that hard to maintain, as I
just put the temp table in a tmp.mdb permanently living in the same
folder as the front end, and never bother to compact it, ever. It
has not been an issue in any app I've ever built like this and I've
been doing this for at least 5 years.


The compacting may not be necessary, but if I'm going to go to the trouble of
a temporary database, I usually go all the way and create it from a template
each time using a random name in the system Temp folder. This way, if the
user opens 2 copies or if a sysadmin installs it incorrectly on a terminal
server, it won't barf.
Nov 12 '05 #4
no****@nospam.nospam (Steve Jorgensen) wrote in
<hb********************************@4ax.com>:
On Tue, 18 Nov 2003 03:23:32 GMT, dX********@bway.net.invalid
(David W. Fenton) wrote:

...

Can't correlated subqueries sometimes give you the results you
need?


Only if the subquery is updateable. The issue usually comes up
with aggregates.


???

Can't you use a subquery to return a field in the resultset? That
certainly doesn't make the query unupdatable, does it? I mean "$365
As Total" for a field in a recordset is not, in fact, updatable,
but it doesn't make the whole query updatable. It's only when you
have joins that the non-updatability cascades.
I've hardly ever needed this kind of thing for an ongoing
purpose, and since I work only with Jet data, I have the
DISTINCTROW predicate available to me, which takes care of quite
a few of these circumstances.


Hmm, I thought that was no longer a meaningful option. I'll see
if it has any impact.


Well, if your data are stored in something other than Jet, it
probably isn't.
I go the temp table route, which isn't that hard to maintain, as
I just put the temp table in a tmp.mdb permanently living in the
same folder as the front end, and never bother to compact it,
ever. It has not been an issue in any app I've ever built like
this and I've been doing this for at least 5 years.


The compacting may not be necessary, but if I'm going to go to the
trouble of a temporary database, I usually go all the way and
create it from a template each time using a random name in the
system Temp folder. This way, if the user opens 2 copies or if a
sysadmin installs it incorrectly on a terminal server, it won't
barf.


But it's a black box solution, right? Once implemented, it's not
hard to maintain, and you can re-use it in multiple applications.

I was thinking about this while dropping off to sleep last night
(so the thinking may be kind of fuzzy) but is there any way at all
to do this with transactions? I believe there isn't. I was thinking
in terms of doing the calculations in the inner transaction, using
that result to update the outer transaction and then rolling back
only the inner transaction. But I'm not sure this is doable or that
it would get you anything beyond the temp table route, as all you
really avoid is doing the temp table creation on your own, leaving
it up to Jet (which does precisely the same thing).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #5
On Tue, 18 Nov 2003 20:45:40 GMT, dX********@bway.net.invalid (David W.
Fenton) wrote:

....

Can't you use a subquery to return a field in the resultset? That
certainly doesn't make the query unupdatable, does it? I mean "$365
As Total" for a field in a recordset is not, in fact, updatable,
but it doesn't make the whole query updatable. It's only when you
have joins that the non-updatability cascades.
If only it were true - it is in every other SQL implementation I've ever heard
of, and it's used as an example in pretty much every beginning book on SQL
I've ever seen. In Access, however, if I execute the following...

UPDATE tblUpdateMaster SET tblUpdateMaster.UpdateMasterTot = (SELECT
Sum(tblUpdateDetail.DetailValue) FROM tblUpdateDetail WHERE
tblUpdateDetail.UpdateMasterID=tblUpdateMaster.Upd ateMasterID);
I get "Operation must use an updateable query."

I tried this too, but it didn't help...

UPDATE DISTINCTROW tblUpdateMaster SET tblUpdateMaster.UpdateMasterTot =
(SELECT Sum(tblUpdateDetail.DetailValue) FROM tblUpdateDetail WHERE
tblUpdateDetail.UpdateMasterID=tblUpdateMaster.Upd ateMasterID);
I've hardly ever needed this kind of thing for an ongoing
purpose, and since I work only with Jet data, I have the
DISTINCTROW predicate available to me, which takes care of quite
a few of these circumstances.
Hmm, I thought that was no longer a meaningful option. I'll see
if it has any impact.


Well, if your data are stored in something other than Jet, it
probably isn't.


Well, I'm usually talking about JET.
I go the temp table route, which isn't that hard to maintain, as
I just put the temp table in a tmp.mdb permanently living in the
same folder as the front end, and never bother to compact it,
ever. It has not been an issue in any app I've ever built like
this and I've been doing this for at least 5 years.


The compacting may not be necessary, but if I'm going to go to the
trouble of a temporary database, I usually go all the way and
create it from a template each time using a random name in the
system Temp folder. This way, if the user opens 2 copies or if a
sysadmin installs it incorrectly on a terminal server, it won't
barf.


But it's a black box solution, right? Once implemented, it's not
hard to maintain, and you can re-use it in multiple applications.


That's true, but I still try find solutions that don't require that kind of
thing if I can. Also, there are so many cases where I get brought in to
implement a simple feature into someone else's existing app, and it feels like
overengineering to leave whole new subsystems in an existing app that I'm only
working on for a week.
I was thinking about this while dropping off to sleep last night
(so the thinking may be kind of fuzzy) but is there any way at all
to do this with transactions? I believe there isn't. I was thinking
in terms of doing the calculations in the inner transaction, using
that result to update the outer transaction and then rolling back
only the inner transaction. But I'm not sure this is doable or that
it would get you anything beyond the temp table route, as all you
really avoid is doing the temp table creation on your own, leaving
it up to Jet (which does precisely the same thing).


I'm not certain, but I don't believe that CREATE TABLE is something that can
be done transactionally. Also, if the temporary table was in the transaction,
then the update would be, too, so rolling back the transaction would mean that
nothing was accomplished. As for having JET do temporary tables instead of
me, if it could be done, that's a good thing because it's me not duplicating
functionality that's already in JET.
Nov 12 '05 #6
I usually convert the non-updateable query to a 'make table' query
into a temporary d/b that I create in c:\temp

then I create a link to the external table..

the only problem I have is in creating an index for the primary key,
ie.

CurrentDb.Execute "CREATE INDEX primaryKey ON tblRegion(item);"

gives me error - 3611 Can't execute data definition statements on
linked
data sources.

does anyone know how to create an index for a table in an external mdb
?

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<b2********************************@4ax.com>. ..
On Tue, 18 Nov 2003 20:45:40 GMT, dX********@bway.net.invalid (David W.
Fenton) wrote:

...

Can't you use a subquery to return a field in the resultset? That
certainly doesn't make the query unupdatable, does it? I mean "$365
As Total" for a field in a recordset is not, in fact, updatable,
but it doesn't make the whole query updatable. It's only when you
have joins that the non-updatability cascades.


If only it were true - it is in every other SQL implementation I've ever heard
of, and it's used as an example in pretty much every beginning book on SQL
I've ever seen. In Access, however, if I execute the following...

UPDATE tblUpdateMaster SET tblUpdateMaster.UpdateMasterTot = (SELECT
Sum(tblUpdateDetail.DetailValue) FROM tblUpdateDetail WHERE
tblUpdateDetail.UpdateMasterID=tblUpdateMaster.Upd ateMasterID);
I get "Operation must use an updateable query."

I tried this too, but it didn't help...

UPDATE DISTINCTROW tblUpdateMaster SET tblUpdateMaster.UpdateMasterTot =
(SELECT Sum(tblUpdateDetail.DetailValue) FROM tblUpdateDetail WHERE
tblUpdateDetail.UpdateMasterID=tblUpdateMaster.Upd ateMasterID);
I've hardly ever needed this kind of thing for an ongoing
purpose, and since I work only with Jet data, I have the
DISTINCTROW predicate available to me, which takes care of quite
a few of these circumstances.

Hmm, I thought that was no longer a meaningful option. I'll see
if it has any impact.


Well, if your data are stored in something other than Jet, it
probably isn't.


Well, I'm usually talking about JET.
I go the temp table route, which isn't that hard to maintain, as
I just put the temp table in a tmp.mdb permanently living in the
same folder as the front end, and never bother to compact it,
ever. It has not been an issue in any app I've ever built like
this and I've been doing this for at least 5 years.

The compacting may not be necessary, but if I'm going to go to the
trouble of a temporary database, I usually go all the way and
create it from a template each time using a random name in the
system Temp folder. This way, if the user opens 2 copies or if a
sysadmin installs it incorrectly on a terminal server, it won't
barf.


But it's a black box solution, right? Once implemented, it's not
hard to maintain, and you can re-use it in multiple applications.


That's true, but I still try find solutions that don't require that kind of
thing if I can. Also, there are so many cases where I get brought in to
implement a simple feature into someone else's existing app, and it feels like
overengineering to leave whole new subsystems in an existing app that I'm only
working on for a week.
I was thinking about this while dropping off to sleep last night
(so the thinking may be kind of fuzzy) but is there any way at all
to do this with transactions? I believe there isn't. I was thinking
in terms of doing the calculations in the inner transaction, using
that result to update the outer transaction and then rolling back
only the inner transaction. But I'm not sure this is doable or that
it would get you anything beyond the temp table route, as all you
really avoid is doing the temp table creation on your own, leaving
it up to Jet (which does precisely the same thing).


I'm not certain, but I don't believe that CREATE TABLE is something that can
be done transactionally. Also, if the temporary table was in the transaction,
then the update would be, too, so rolling back the transaction would mean that
nothing was accomplished. As for having JET do temporary tables instead of
me, if it could be done, that's a good thing because it's me not duplicating
functionality that's already in JET.

Nov 12 '05 #7
On 19 Nov 2003 08:17:06 -0800, le*********@natpro.com (Roger) wrote:
I usually convert the non-updateable query to a 'make table' query
into a temporary d/b that I create in c:\temp
That was one of the alternatives listed in my original post.
then I create a link to the external table..

the only problem I have is in creating an index for the primary key,
ie.

CurrentDb.Execute "CREATE INDEX primaryKey ON tblRegion(item);"

gives me error - 3611 Can't execute data definition statements on
linked
data sources.

does anyone know how to create an index for a table in an external mdb
?


Sure - you just need to open a reference to the temporary database using the
OpenDatabase method of the Workspace object.

Nov 12 '05 #8
thanks...

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<8t********************************@4ax.com>. ..
On 19 Nov 2003 08:17:06 -0800, le*********@natpro.com (Roger) wrote:
I usually convert the non-updateable query to a 'make table' query
into a temporary d/b that I create in c:\temp


That was one of the alternatives listed in my original post.
then I create a link to the external table..

the only problem I have is in creating an index for the primary key,
ie.

CurrentDb.Execute "CREATE INDEX primaryKey ON tblRegion(item);"

gives me error - 3611 Can't execute data definition statements on
linked
data sources.

does anyone know how to create an index for a table in an external mdb
?


Sure - you just need to open a reference to the temporary database using the
OpenDatabase method of the Workspace object.

Nov 12 '05 #9
Steve Jorgensen writes:
In Access, however, if I execute the following...
UPDATE tblUpdateMaster SET tblUpdateMaster.UpdateMasterTot
= (SELECT
Sum(tblUpdateDetail.DetailValue) FROM tblUpdateDetail WHERE
tblUpdateDetail.UpdateMasterID=tblUpdateMaster.Upd ateMasterID);
I get "Operation must use an updateable query."

Did it ever occur to you to that since Jet can update
a row/column multiple times that you can therefore
simulate many subqueries that have an aggregate instead
of using a domain function?
I think I talked about this decades ago:).
Anyway check out this thread with special emphasis
on the 'Zeppo' replies:)

http://tinyurl.com/w3vh

Check out MS Sql Server utilities @
www.rac4sql.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #10
On 22 Nov 2003 16:11:12 GMT, Zeppo <no****@aol.com> wrote:
Steve Jorgensen writes:
In Access, however, if I execute the following...
UPDATE tblUpdateMaster SET tblUpdateMaster.UpdateMasterTot
= (SELECT
Sum(tblUpdateDetail.DetailValue) FROM tblUpdateDetail WHERE
tblUpdateDetail.UpdateMasterID=tblUpdateMaster.Upd ateMasterID);
I get "Operation must use an updateable query."

Did it ever occur to you to that since Jet can update
a row/column multiple times that you can therefore
simulate many subqueries that have an aggregate instead
of using a domain function?
I think I talked about this decades ago:).
Anyway check out this thread with special emphasis
on the 'Zeppo' replies:)

http://tinyurl.com/w3vh

Check out MS Sql Server utilities @
www.rac4sql.net


If I understand your uggestion correctly, this will not work because when you
update a row multiple times in the same UPDATE statement, the original value
seen by the calculation each time the row is original value before any updates
occurred. This means that the effect will be that only the last update
processed will appear to have happened, and none of the others will have made
any contribution to the result.
Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Chris Fulstow | last post: by
17 posts views Thread by rdemyan via AccessMonster.com | last post: by
1 post views Thread by Chris | last post: by
9 posts views Thread by Lee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.