General discussion

Locked

updatable query demanded by Access

By john_wills ·
UPDATE JWXLSTBL SET Prov_ID = Auto - 1 + (SELECT Next_Prime FROM dbo_SEQUENCE WHERE DATABASE_ = 'provider');
When I run the above query in Access 97 or 2000 I get the message "Operation must use an updatable query". The nested SELECT does yield exactly one value when run by itself. I am able to update the table JWXLSTBL, and it does have a column Prov_ID. I get appropriate results if I use a constant instead of the SELECT subquery.
1. What might I be doing wrong in SQL?
2. Is there a way of running the nested SELECT and putting the result into a VBA variable so that I can construct the UPDATE with a constant? This can be done in Cobol, but I cannot find a way of doing it in VBA.

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

updatable query demanded by Access

by Glen_McLeod In reply to updatable query demanded ...

Access does not consider your sub query to be of a scalar type. Just because it only returns one value now doesn't mean that it will never retrun more than one.

What you need to do is create a function that executes the sub query and returns thevalue to your parent query.

Update JWXLSTBL
Set Prov_ID = Auto - 1 + GetNextPrime()

Public Function GetNextPrime() as Long
Dim rs as ADODB.Recordset
Dim sSQL as String

sSQL = "SELECT Next_Prime FROM dbo_SEQUENCE WHERE DATABASE_ = 'provider'"
Set rs = cnConnection.Execute(sSQL)
GetNextPrime = rs!Next_Prime

End Function

There's a few things missing, like the connection object's creation and error handling, but you get the idea.

You also seem to be missing a Where clause in your parent query.

Glen

Collapse -

updatable query demanded by Access

by john_wills In reply to updatable query demanded ...

This is what I actually did, inspired partly by Glen's suggestion:
SequenceFinder = SequenceFinderBasis & "'provider';"
Set SequenceHolder = CurrentDb().OpenRecordset(SequenceFinder)
SequenceHolder.MoveFirst
SequenceNumber = SequenceHolder.Fields(0)
DoCmd.RunSQL "UPDATE JWXLSTBL " _
& "SET Prov_ID" _
& "=Auto - " & AutobaseNumber & " + " & SequenceNumber & ";"

Collapse -

updatable query demanded by Access

by john_wills In reply to updatable query demanded ...

Glen's Answer 1 seems good, but I do not want to close yet, for something better may arrive. The method I have with a colleague developed, and which works, is this:
SequenceFinderBasis = "SELECT Next_Prime FROM dbo_SEQUENCE WHERE DATABASE_=" SequenceFinder = SequenceFinderBasis & "'provider';"
Set SequenceHolder = CurrentDb().OpenRecordset(SequenceFinder)
SequenceHolder.MoveFirst
SequenceNumber = SequenceHolder.Fields(0)
DoCmd.RunSQL "UPDATE JWXLSTBL " _ & "SET Prov_ID" _
& "=Auto - " & AutobaseNumber & " + " & SequenceNumber & ";"
I now have Autobasenumber rather than 1, for an extraneous reason. I do not need a WHERE in my UPDATE because every Prov_ID is to be calculated fromthe value of Auto in its row.

Collapse -

updatable query demanded by Access

by john_wills In reply to updatable query demanded ...

This question was closed by the author

Back to Web Development Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums