Access 2000 RecordSet Sort problem

hi there,

using ms access 2000 with vb 6.3.

I am using a form with a button to first off filter results in a table and copy all the relevent rows into a temp table so that additional tasks can be performed. This works successfully. Part of the problem is that the order in which the data is entered into the first table means it is never in order (this cannot be prevented due to the nature of how the data is imported from several excel documents)

The temp table needs to be sorted so that the calculation algortihms will be able to calculate and generate revelent infomation. Currently defining and opening a recordset and after moving all the data to the temp table i am using:

recordset.Sort = "TextValue desc"

Where TextValue is my column name and data type is text. Everytime that i run the button i get to the sort line and then i get a runtime error:

RunTime error: 3251
"Opperation is not supported for this type of object"

is there anyway to sort this recordset after the data has been entered. Or do i need to perform additional tasks as the recordset has been used.

Or is there any other way to sort this table via the text column

Many Thanks

Answer this question

Access 2000 RecordSet Sort problem

Lighthouse Builder

Hey Ray,

If you've got DAO code there then exhaust every option to your problem before going to ADO.

You've got a few options available... your SQL statement there is slightly wrong, could be a typo, it should be

SELECT * FROM [Table] ORDER BY [Table].[TextValue]

ORDER and BY are seperate

Also the DoCmd.RunSQL is really for running INSERT and UPDATE commands on the database. The data in a table is never physically sorted, you just access it in a certain order. Generally an index is created and its the index thats sorted. The index is then used to access the records.

What you can do is open the table into the recordset using your SQL statement like this...

Dim wrkJet As Workspace
Dim dbsNorthwind As DatabaseSet

Dim rstTemp As Recordset

wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb")

Set rstTemp = dbsNorthwind.OpenRecordset( _
"SELECT * FROM [Table] ORDER BY [Table].[TextValue]", dbOpenDynaset, dbReadOnly)

You can use a query just the same as a table...

SELECT * FROM [QueryName]
Pranav Garg
hi,

thanks for your reply but the database that im using is a DAO type. And as soon as i start adding ADO items in there to setup connections and open the table then i start to get errors. As i havent used the ADO before im not getting far with this.

Is there anyway to sort this with DAO

thanks
mahima
Hey there,

thanks very much derek worked a treat
mohd sufian

Hi Ray,

This might be to do with the cursor type of the recordset. The sorting of a recordset is done by the ADO Cursor Engine which means you need to use a client side cursor. Before you populate the recordset change the CursorLocation property of the recordset to adUseClient and hopefully that will fix your problem.
JonnyAJAX
also trying to use :

DoCmd.RunSQL " Select * from Table OrderBy Table.TextValue;"

but that just gives me

Runtime error: 2342

Run SQL action requires an argument consisting of an SQL statement.

even though i made the sql in the query mode and then copied and pasted the code into VB to run.

Or is there anyway that i can get access to take the information from a Query instead of the table
Access 2000 RecordSet Sort problem

Answer this question