Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

DB error message

Posted on 2004-11-24
17
Medium Priority
?
163 Views
Last Modified: 2010-05-02
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
0
Comment
Question by:lynnton
  • 5
  • 3
  • 3
  • +4
17 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 12672446
make sure the mdb file is writeable (not read-only)
0
 
LVL 33

Expert Comment

by:hongjun
ID: 12672450
make sure you have the permission to change the mdb.
0
 
LVL 1

Author Comment

by:lynnton
ID: 12672451
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 33

Expert Comment

by:hongjun
ID: 12672458
>>add/delete records
using codes? or manually?
0
 
LVL 1

Author Comment

by:lynnton
ID: 12672465
hongjun,

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

hope this helps,

Thanks,
Lynnton

0
 
LVL 6

Expert Comment

by:mmusante
ID: 12672774
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
 
LVL 1

Author Comment

by:lynnton
ID: 12672786
mmusante,

What do you mean?
Please write the code.

Thanks,
Lynnton
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12672791
Hi Lynnton

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

cnForecast.Mode = adModeReadWrite
0
 
LVL 6

Accepted Solution

by:
mmusante earned 1920 total points
ID: 12672864
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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 12674145
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
 
LVL 1

Author Comment

by:lynnton
ID: 12675487
EDDYKT,

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

Thanks,
Lynnton
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 12675612
do

if
else
end if


instead of iif
0
 
LVL 1

Author Comment

by:lynnton
ID: 12675626
EDDYKT,

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

Thanks,
Lynnton

0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 12675670
No idea what is NZ is

?->
0
 
LVL 6

Expert Comment

by:PePi
ID: 12676602

<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
 
LVL 12

Assisted Solution

by:Bob Lamberson
Bob Lamberson earned 80 total points
ID: 12678114
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
 
LVL 6

Expert Comment

by:mmusante
ID: 12679253
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month11 days, 16 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question