ADODB Order Question

[_Otacustes_]

Hello all,

I have a ADODB recorset which is full of records. I want to be able to sort the data in a field, say a field called "description" for instance.

If anyone can offer any advise, I would greatly appreciate it.

Thanks

 

[namliam]

add the order by statement in your Select you use on opening the recordset.

 

[_Otacustes_]

add the order by statement in your Select you use on opening the recordset.

I do not use a select statement to open the recordset. What I have is this:

    Dim adoRs As ADODB.Recordset
    Dim i As Integer
    
    Set adoRs = New ADODB.Recordset
    
    adoRs.Fields.Append "Desc1", adVarChar, 20
    adoRs.Fields.Append "Desc2", adVarChar, 20
    adoRs.Fields.Append "Desc3", adVarChar, 20
    adoRs.Fields.Append "Desc4", adVarChar, 100
    adoRs.Fields.Append "Desc5", adVarChar, 100
    
    adoRs.Open
    
    With Worksheets(sheetName)
        For i = 8 To 160
             adoRs.AddNew Array("Desc1", "Desc2", "Desc3", "Desc4", "Desc5"), Array("1","2","3","4")
            End If
        Next
    End With

 

[namliam]

This is obviously a snip-it from the code with with the floating End If in there...

Why are you building a recordset (seemingly fixed recordset?) in memory? Doesnt make sence to me???

Moreso... Native Access is DAO not ADO... You should use DAO where possible, becuase it keeps you more flexible in the database
i.e. Forms are DAO not ADO, so if you do anything (ever) with forms you have to use DAO. Using the two mixed will only cause you headaches!!!!

 

[LPurvis]

IMO generated recordsets (as opposed to just disconnected) are a fantastic feature of ADO. (As are their disconnected cousins FWIW).
They're a very handy storage mechanism with features default arrays don't even come close to offering (i.e. immediately ordering and filtering - cloning, persisting... etc).

While I agree that with native Jet work, DAO rules the waves in general - when there is functionality to be used that DAO doesn't provide - I use ADO with Jet without a moment's hesitation.

That said - I'll second the question Mailman asks.
The example you have looks - well... bordering on pointless. ;-)
Is it just an example - abstracting the actual complexity of what you're doing? (It's always my feeling that when posters do that they risk getting erroneous advice - which is only correct for the simplistic example offered).

And finally - just to technically answer your question.
You just specify the order using the Sort property

adoRs.Sort = "FieldName"

As opposed to DAO recordsets - order and filtering properties are immediately implemented in ADO.

 

[namliam]

adoRs.Sort = "FieldName"

is it obvious yet that I hardly ever use ADO unless forced to for some reason??

 

[_Otacustes_]

is it obvious yet that I hardly ever use ADO unless forced to for some reason??

oh my

Thanks for your reply. Out of interest what would you use?

 

[namliam]

Use DAO offcourse... Only 2 ways (in access) to handle recordsets, DAO or ADO....

But the main question tho is... what are you trying to do??

 

[_Otacustes_]

Use DAO offcourse... Only 2 ways (in access) to handle recordsets, DAO or ADO....

But the main question tho is... what are you trying to do??

What I amtrying to do is create an interface between our design office and ERP system. What I need to do is to re-jig the part details from our CAD system and put it into a specific format with additional information before the purchasing department gets it.

I have spent a significant amount of time on this so far, so I cannot ditch it now, but I will try DAO in my next project.

The trouble I have now is that an error "object or provider is not capable of performing the requested operation" and stops at this point

currentAdditionalDataRecord.Open additionalData

Private Function ConsolidateData(existingData As ADODB.Recordset, additionalData As ADODB.Recordset) As ADODB.Recordset

 
    Dim currentAdditionalDataRecord As ADODB.Record
       
    Set currentAdditionalDataRecord = New ADODB.Record
    
   currentAdditionalDataRecord.Open additionalData

     Do While (Not additionalData.EOF)
    
        If (ExistingDataSearch(existingData, currentAdditionalDataRecord)) Then
            MsgBox "data found"
        End If
           
        additionalData.MoveNext
    Loop


End Function

Thanks very much for your help.

Kind regards

 

[namliam]

I think you want to have Recordset not record

 

[LPurvis]

Again - I get the feeling you're just trying stuff hoping to (almost randomly?) hit the nail on the head.
(But a bit of time taken to plan and explain first can work wonders).

 

[_Otacustes_]

Again - I get the feeling you're just trying stuff hoping to (almost randomly?) hit the nail on the head.
(But a bit of time taken to plan and explain first can work wonders).

I am not trying randomly, I hope anyway.

All I am trying to do is open a record into a recordset which according to other resources should work in the way described but for some reason I get the error message.

 

[LPurvis]

OK. Well what kind of data are you wanting to open? i.e. why are you needing a Record Object?
We're just seeing small snippets.
It's impossible to see your overall implementation. Totally in the dark here.

 

[namliam]

I think you want to have Recordset not record

I guess you didnt see my previous message?? You are declaring it as a record... I think you need to say Recordset

 

[LPurvis]

I think the intention is indeed to use a Record object. I'm just wondering why - and to what ultimate goal? What is the data being dealt with? What is the manipulation goal?
(Do you see what I mean Otacustes? Little pieces of code alone aren't enough to diagnose a larger problem).

 

[_Otacustes_]

I guess you didnt see my previous message?? You are declaring it as a record... I think you need to say Recordset

I did see your previous message...maybe my understanding is a little clouded here?

As I understand it, and I am sure you will correct me if I am wrong, but a recordset contains records.

What I am trying to do is to pass a recordset and a record to the function called ExistingDataSearch hence the line

ExistingDataSearch(existingData, currentAdditionalDataRecord))

What i understand also is that you create a record and open the current recordset into it.

If I pass two recordsets into the function then I'll have to search through both - firstly to find the record I want to search for in the second one. Maybe that is the way I need to do it after all?

For instance, if I have a field called part number in both recordsets and I want to search for a specific record to see whether it is in the other recordset, I would have to search for the record (in the first set) that I would need to search for in the second?????

Not sure I have explained it that well, hopefully so?

 

[namliam]

That ADO goes beyond my knowledge... sorry...

 

[_Otacustes_]

Here is what I am trying to achieve...

I have two recordsets both exactly the same except the data they contain.

I have to consolidate them into one list.

Because they contain part data it may be that it is just an amendment of the quantity of that part so firstly I have to search for each part in the second recordset to see whether it exists in the first recordset. If it does then perform a comparison of the quantity data (and others).

For simplicity, maybe not, I was thinking of a third recordset which contains the final consolidation

rather than update the first or second one - the other two can then be discarded as they are of no further use.

This could be more confusing, I don't know!!

Going back to the previous code I posted; the existingData and additionalData recordsets are passed into the function ready for consolidation. For reference the third recordset has not been decided on
yet whether to use this or not, I have not got that far.

The intention is that the first record of the second recordset is read and passed to the function EXistingDataSearch to see whether it exists in the first recordset.

It is the reading of the record and passing it to the function which is the problem.

So there is a little background as to what I am trying to achive. Is there is an easier way, other than the obvious (DAO )?

Thanks

 

[LPurvis]

Sorry - I'm afraid I've become a bit busy with work now.
I'll try to have a look at this later on for you.

 

[datAdrenaline]

Please take note ... a "Recordset" object has the ability to contain just one record, but that does not make it a "Record" object ... the requirement of retreiving one record from a table does not mean you need a "Record" object ... you are likely to still need a "Recordset" object, that only contains ONE record in it...

... Time is short for me too! ...