ORDER BY in SQL Statement prevents updating recordset?! 1

[mincefish]

Hello,

I hope someone can help me, as I'm confused, and I think there may be too many variables that could be affecting this issue to truly discern what is going wrong.

I'm using Access 2000 (with ADO 2.1) as a front end to an MSDE back end.

I'm writing some code to calculate quintiles, and so I need to open up a portion of a table into a recordset, and loop through it, assigning 5 to the top 20% of the records (value-wise), 4 to the next 20% , 3 to the next 20% etc...etc. I think you probably get that bit!

Anyhoo, in order to do this, I need to order by records by my value field. Herein lies the problem. When I have an ORDER BY statement in my SQL String, I get the following message:

Current Recordset does not support updating. This maybe a limitation of the provider, or the selected locktype.

Weird I thought. Anyhoo, here's the code (I know the last-ish line is gibberish - I was just testing to see if I could update the field, but didn't want to overwrite the data):

strConADO = "Provider=SQLOLEDB.1;" & _
        "Initial Catalog=BuildSRi;" & _
        "Data Source=(Local);" & _
        "Trusted_Connection=Yes"

strSQL = " SELECT  Measure1, Quint1" & _
                " FROM [Temp]" & _
                " WHERE   ItemID = 77 AND" & _
                " dteMonth = '10/01/2002'" & _
                " ORDER BY Measure1 DESC"
    

Set conADO = New ADODB.Connection
conADO.ConnectionString = strConADO
conADO.Open

rstTemp.ActiveConnection = conADO
rstTemp.CursorLocation = adUseServer
rstTemp.CursorType = adOpenKeyset
rstTemp.Open strSQL

Do Until rstTemp.EOF
    
    rstTemp.Fields(0) = rstTemp.Fields(0)
    rstTemp.Update
    
Loop

So I don't think there's anything wrong with connection string, and I've tried each different CursorType one by one. They all give the same error. I also don't want to use a Client Side cursor, as this deals with alot of records, and will therefore be quite intensive - it will only ever be run by one user at any time.

Just when you thought it was safe to close this thread, and move onto the next one, there's something even weirder.

If I remove the ORDER BY line from the SQL statement - so the code looks like this:

strSQL = " SELECT  Measure1, Quint1" & _
                " FROM [Temp]" & _
                " WHERE   ItemID = 77 AND" & _
                " dteMonth = '10/01/2002'"

........It works fine!!

Is this a known problem?!

Please help - I'll be terribly grateful, and give you a star and everything
I can't even think of a way round it - the table needs to be done bit by bit, as each section doesn't relate to any other (OK, so the data is the same, but for the purposes of calculating quintiles, it doesn't), and it has to be ordered - that's the very nature!

Thanks in advance,

Tom

 

[Tranman]

Tom,
Here is a way around it:

Instead of doing your updating in the recordset, just do it directly in the table. Just step down through the sorted recordset one row at a time, and each row, when you know which quintile you want to assign, just fire off an update directly to the database:

conADO.Execute &quot;Update <tablename> set <fieldname> = <value> where <whatever>&quot;

I've done this in the past where I had a recordset that, for whatever reason, was not updatable. Works fine.

Good Luck,
Tranman

 

[CajunCenturion]

Have you tried setting the .LockType property of the Recordset?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein