Is it possible to do a select withing update in Access2000

[DuncanIdaho]

Hi

Apologies if this is similar to a (very) recent post.

I was wondering if it is possible to execute an update query that
contains a select statement on an MS access 2000 database.
I have included a detaled example at the bottom of this post

I have the following update query that works as I expect it to on a
MySQL database

update events set events.event1 = (select happenings.id from happenings
where happenings.happening = events.event1);

If I try to run this query on an access database with exactly the same
tables I get the following message

"Operation must use an updateable query"

Now I have had a look around and others who have been experiencing this
seem to be having permissions problems, however if I change the query as
follows update events set events.event1 = (1); it works fine although
obviously this is not what I need.

Is it possible to execute an update query that contains a select
statement on an MS access 2000 database ?

Thanks in advance

Idaho

========= Detailed example follows ==========

Say we have the tables events and happenings

//events
+----+-----------+------------+------------+------------+------------
| id | somefield | event1 | event2 | event3 | event4
+----+-----------+------------+------------+------------+------------
| 1 | whatever | happening1 | happening2 | happening5 | happening3
| 2 | another | happening5 | happening3 | happening4 | happening1
+----+-----------+------------+------------+------------+------------

//happenings
+----+------------+
| id | happening |
+----+------------+
| 1 | happening1 |
| 2 | happening2 |
| 3 | happening3 |
| 4 | happening4 |
| 5 | happening5 |
+----+------------+

to update the event1 field in the events table with the key for
happening1 I simply run this query

update events set events.event1 = (select happenings.id from happenings
where happenings.happening = events.event1);

This gives the following result

+----+-----------+--------+------------+------------+------------
| id | somefield | event1 | event2 | event3 | event4
+----+-----------+--------+------------+------------+------------
| 1 | whatever | 1 | happening2 | happening5 | happening3
| 2 | another | 5 | happening3 | happening4 | happening1
+----+-----------+--------+------------+------------+------------

Which is what I want, but Access 2000 barfs with the almost
incompehensible message
"Operation must use an updateable query"

[DuncanIdaho]

DuncanIdaho wrote:

Hi

Apologies if this is similar to a (very) recent post.

Hmm, this seems to work
update events happenings set events.event1 = happenings.id where
happenings.happening = events.event1
er ... well anyway, it works,

Thanks anyway

Idaho

>
I was wondering if it is possible to execute an update query that
contains a select statement on an MS access 2000 database.
I have included a detaled example at the bottom of this post

I have the following update query that works as I expect it to on a
MySQL database

update events set events.event1 = (select happenings.id from happenings
where happenings.happening = events.event1);

If I try to run this query on an access database with exactly the same
tables I get the following message

"Operation must use an updateable query"

Now I have had a look around and others who have been experiencing this
seem to be having permissions problems, however if I change the query as
follows update events set events.event1 = (1); it works fine although
obviously this is not what I need.

Is it possible to execute an update query that contains a select
statement on an MS access 2000 database ?

Thanks in advance

Idaho

========= Detailed example follows ==========

Say we have the tables events and happenings

//events
+----+-----------+------------+------------+------------+------------
| id | somefield | event1 | event2 | event3 | event4
+----+-----------+------------+------------+------------+------------
| 1 | whatever | happening1 | happening2 | happening5 | happening3
| 2 | another | happening5 | happening3 | happening4 | happening1
+----+-----------+------------+------------+------------+------------

//happenings
+----+------------+
| id | happening |
+----+------------+
| 1 | happening1 |
| 2 | happening2 |
| 3 | happening3 |
| 4 | happening4 |
| 5 | happening5 |
+----+------------+

to update the event1 field in the events table with the key for
happening1 I simply run this query

update events set events.event1 = (select happenings.id from happenings
where happenings.happening = events.event1);

This gives the following result

+----+-----------+--------+------------+------------+------------
| id | somefield | event1 | event2 | event3 | event4
+----+-----------+--------+------------+------------+------------
| 1 | whatever | 1 | happening2 | happening5 | happening3
| 2 | another | 5 | happening3 | happening4 | happening1
+----+-----------+--------+------------+------------+------------

Which is what I want, but Access 2000 barfs with the almost
incompehensible message
"Operation must use an updateable query"

[bcap]

Or, perhaps:

update events INNER JOIN happenings ON happenings.happening = events.event1
set events.event1 = happenings.id
"DuncanIdaho" <Du**************@googlemail.comwrote in message
news:kJ******************************@bt.com...

DuncanIdaho wrote:

>Hi

Apologies if this is similar to a (very) recent post.

Hmm, this seems to work
update events happenings set events.event1 = happenings.id where
happenings.happening = events.event1
er ... well anyway, it works,

Thanks anyway

Idaho

>>
I was wondering if it is possible to execute an update query that
contains a select statement on an MS access 2000 database.
I have included a detaled example at the bottom of this post

I have the following update query that works as I expect it to on a MySQL
database

update events set events.event1 = (select happenings.id from happenings
where happenings.happening = events.event1);

If I try to run this query on an access database with exactly the same
tables I get the following message

"Operation must use an updateable query"

Now I have had a look around and others who have been experiencing this
seem to be having permissions problems, however if I change the query as
follows update events set events.event1 = (1); it works fine although
obviously this is not what I need.

Is it possible to execute an update query that contains a select
statement on an MS access 2000 database ?

Thanks in advance

Idaho

========= Detailed example follows ==========

Say we have the tables events and happenings

//events
+----+-----------+------------+------------+------------+------------
| id | somefield | event1 | event2 | event3 | event4
+----+-----------+------------+------------+------------+------------
| 1 | whatever | happening1 | happening2 | happening5 | happening3
| 2 | another | happening5 | happening3 | happening4 | happening1
+----+-----------+------------+------------+------------+------------

//happenings
+----+------------+
| id | happening |
+----+------------+
| 1 | happening1 |
| 2 | happening2 |
| 3 | happening3 |
| 4 | happening4 |
| 5 | happening5 |
+----+------------+

to update the event1 field in the events table with the key for
happening1 I simply run this query

update events set events.event1 = (select happenings.id from happenings
where happenings.happening = events.event1);

This gives the following result

+----+-----------+--------+------------+------------+------------
| id | somefield | event1 | event2 | event3 | event4
+----+-----------+--------+------------+------------+------------
| 1 | whatever | 1 | happening2 | happening5 | happening3
| 2 | another | 5 | happening3 | happening4 | happening1
+----+-----------+--------+------------+------------+------------

Which is what I want, but Access 2000 barfs with the almost
incompehensible message
"Operation must use an updateable query"