asked on

DB error message

Hi,

error message: operation must be an updateable query. (ms access)

pointing at ---> Call cnForecast.Execute(RSforecast.Fields("command").Value)

rsforcast.Fields("command").value contains...

UPDATE forecastsched SET Relate = Iif(IsNull((SELECT MAX(Relate) FROM forecastsched)), 1, (SELECT MAX(Relate) FROM forecastsched) + 1) WHERE forecastID IN (49083, 49229);

__________________________________________________code
SQL="select...."
RSforecast.Open SQL, cnForecast, adOpenForwardOnly, adLockBatchOptimistic
Set RSforecast.ActiveConnection = Nothing
Do While Not RSforecast.EOF
Call cnForecast.Execute(RSforecast.Fields("command").Value)
RSforecast.MoveNext
DoEvents
Loop
Set RSforecast = Nothing

how to make this work?

Thanks,
Lynnton

hongjun

make sure the mdb file is writeable (not read-only)

hongjun

make sure you have the permission to change the mdb.

lynnton

ASKER

hongjun,

It's an access db on the local machine, i'm able to add/delete records with no problems.

something with running Call cnForecast.Execute(RSforecast.Fields("command").Value)

Thanks,
Lynnton

hongjun

>>add/delete records
using codes? or manually?

lynnton

ASKER

hongjun,

Using sql query, when the form loads it "DELETE * from forecastsched"

hope this helps,

Thanks,
Lynnton

mmusante

I tried with a table on a my DB and I got the same error ...

If I replace the "Iif(IsNull((SELECT MAX(..." with a constant value all works fine ...

It seems as this subquery locks the table so you can't update it

Can't you do this operation in two steps? First you get the result for "Iif(IsNull((SELECT MAX(..." and then you update the table

lynnton

ASKER

mmusante,

What do you mean?
Please write the code.

Thanks,
Lynnton

muzzy2003

Hi Lynnton

Me again. One thing to try. Before you open the connection, try:

cnForecast.Mode = adModeReadWrite

ASKER CERTIFIED SOLUTION

mmusante

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EDDYKT

IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True.

lynnton

ASKER

EDDYKT,

I see, what do you recommend? I've heard of NZ ?
how do we go about this?

Thanks,
Lynnton

EDDYKT

do

if
else
end if

instead of iif

lynnton

ASKER

EDDYKT,

Gotcha, do you know NZ and how to use it?

Thanks,
Lynnton

EDDYKT

No idea what is NZ is

?->

PePi

<code snip>
RSforecast.Open SQL, cnForecast, adOpenForwardOnly, adLockBatchOptimistic
</code snip>

you opened your recordset as openforwardonly, change it to adOpenDynamic and your lock to adLockPessimistic

since openforwardonly sets your recordset to be read only.

HTH!!!

SOLUTION

Bob Lamberson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mmusante

Try using Access DMax function change your query to:

UPDATE forecastsched SET Relate = Nz(Dmax("Relate","forecastsched",""),0)+1 WHERE forecastID IN (49083, 49229);