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
[1197 byte] By [
RayMordy] at [2007-12-22]
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?
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]
