UPDATE Query

[laurajayne.cozens]

Help! I am trying to write a query in access VBA. The query is doing
a count and inserting the figures into lots of different sub tables.
After all the counts are done, I want an update statement to update
one big table with all the figures.

The current code is:

DoCmd.RunSQL "UPDATE S_OFSTED_A SET ALL_ASD = (SELECT CountOfUNIQUE_ID
FROM S_OFSTED_ALL, S_OFSTED_A WHERE S_OFSTED_ALL.Eth_id =
S_OFSTED_A.Eth_id) "

S_Ofsted_A is my main table. It has a list of different ethnicitys
which each have an id. The table S_Ofsted_ALL contains all the count
firgures for the ofsted code 'ASD'. I want to populate the collum
ALL_ASD with the figures from S_OFSTED_ALL where the ethnicity fields
equal each other - so that for each ethnicity I am getting several
different figures.

Am I going about this completely the wrong way? Currently, when i run
this code i get runtime error 3073: Operation must use an updateable
query!!

What am i doing wrong?

Thanks for your help!

Laura

 

[Michel Walsh]

S_OFSTED_A is probably not updateable itself, and even if you don't update
it, Jet marks the whole query using it as not-updateable.


A possible solution is to push the data from S_OFSTED_A into a (temp) table,
and use that table, in your update query.


Hoping it may help,
Vanderghast, Access MVP

 

[laurajayne.cozens]

Hi Michel

Thanks for your help but that didnt seem to work. Is it because of
the INSERT INTO command rather than creating a temp table??

However, I have just built an update query in access doing what i
needed and copied the sql code - it worked first time! God only knows
where i was going wrong but its working fine now!

Thanks for your reply though...

Laura

 

[Michel Walsh]

INSERT INTO should work, even if the data you inserted come from a
not-updateable query. If it does not work there is probable either a problem
with the insert into query syntax, or a compatibility problem, such as
appending a text value to an existing column that can only hold numerical
data (or a data relation integrity rule is not respected, etc.). It is NOT
required to be a query that creates a table.


Fine to see you did get a solution anyhow.


Vanderghast, Access MVP