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
LVL 1
lynntonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hongjunCommented:
make sure the mdb file is writeable (not read-only)
0
hongjunCommented:
make sure you have the permission to change the mdb.
0
lynntonAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

hongjunCommented:
>>add/delete records
using codes? or manually?
0
lynntonAuthor Commented:
hongjun,

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

hope this helps,

Thanks,
Lynnton

0
mmusanteCommented:
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
0
lynntonAuthor Commented:
mmusante,

What do you mean?
Please write the code.

Thanks,
Lynnton
0
muzzy2003Commented:
Hi Lynnton

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

cnForecast.Mode = adModeReadWrite
0
mmusanteCommented:
I mean something like this ...

rsCount.Open "(SELECT MAX(Relate) AS Cnt FROM forecastsched)", cnForecast, adOpenStatic, adLockReadOnly
NextRelate = iif(isnull(rsCount!Cnt),1,rsCount!Cnt+1)
Command = "UPDATE forecastsched SET Relate = " & NextRelate & " WHERE forecastID IN (49083, 49229);"
cnForecast.Execute Command
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EDDYKTCommented:
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.

0
lynntonAuthor Commented:
EDDYKT,

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

Thanks,
Lynnton
0
EDDYKTCommented:
do

if
else
end if


instead of iif
0
lynntonAuthor Commented:
EDDYKT,

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

Thanks,
Lynnton

0
EDDYKTCommented:
No idea what is NZ is

?->
0
PePiCommented:

<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!!!

0
Bob LambersonSoftware EngineerCommented:
This is from the Access help file and haven't tested it in vb6, but it would help with the iif problem it it will work.

Public Sub CheckValue()

    Dim frm As Form
    Dim ctl As Control
    Dim varResult As Variant

    ' Return Form object variable pointing to Orders form.
    Set frm = Forms!Orders

    ' Return Control object variable pointing to ShipRegion.
    Set ctl = frm!ShipRegion

    ' Choose result based on value of control.
    varResult = IIf(Nz(ctl.Value) = vbNullString, _
        "No value.", "Value is " & ctl.Value & ".")

    ' Display result.
    MsgBox varResult, vbExclamation

End Sub


Bob
0
mmusanteCommented:
Try using Access DMax function change your query to:

UPDATE forecastsched SET Relate =  Nz(Dmax("Relate","forecastsched",""),0)+1 WHERE forecastID IN (49083, 49229);
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.