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):
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:
........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
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):
Code:
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:
Code:
strSQL = " SELECT Measure1, Quint1" & _
" FROM [Temp]" & _
" WHERE ItemID = 77 AND" & _
" dteMonth = '10/01/2002'"
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