ADO Stream Object sorts the field order - why?

[Jon von der Heyden]

Hi All

A little while back Andrew showed me that using the ADO Stream object I could preserve the recordset filter. I am using this:

Public Function FilterRecordSet(ByVal objRecordSet As ADODB.RecordSet, ByVal strFilterText As String) As ADODB.RecordSet
    Dim objStream As ADODB.Stream
    
    On Error Resume Next
        objRecordSet.Filter = strFilterText
    On Error GoTo 0
    
    Set objStream = New ADODB.Stream
    With objRecordSet
        .Save objStream, adPersistXML
        .Close
        .Open objStream
    End With
    
    Set FilterRecordSet = objRecordSet
End Function

This seems to order the fields alphabetically. Do you know how to avoid this, or do you know an alternative way to use the filter method on a recordset and dump the filtered results (without looping through all records)?

 

[RoryA]

It doesn't change the order for me.

 

[RoryA]

Does it make any difference for you if you save it to a file using adPersistADTG and then open that?

 

[Jon von der Heyden]

Does it make any difference for you if you save it to a file using adPersistADTG and then open that?

Nope, afraid not. So it doesn't change the order of the fields for you? I wonder what is different about yours compared with mine. I create the recordset using the Recordset Open method.

Public Function RunScript(ByVal strSql As String) As ADODB.RecordSet
    Set RunScript = New ADODB.RecordSet
    Call RunScript.Open(strSql, Me.DBconnection, adOpenKeyset, adLockOptimistic)
End Function

The SQL string is build up based on the selection of fields that the user chooses from a listbox on a userform. Grabbing the lot from one of the tables would look like:

SELECT [COUNTRY], [PERIOD], [KPI_NAME], [KPI_DESCRIPTION], [UNIT_OF_MEASURE], [RECORD_TYPE], [RESPONSIBLE_OWNER], [COMMENT], [SECTOR], [DIVISION], [BUSINESS_UNIT], [COMMODITY_CATEGORY], [MAJOR_COMMODITY], [SUB_COMMODITY], [UPLOAD_USER], [DATE_CREATED], [LAST_UPDATED], [FILE_URL], Avg(IIf([FUNCTION]="Average",[RESULT],0)) AS AVERAGE_RESULT, Sum(IIf([FUNCTION]="Sum",[RESULT],0)) AS SUM_RESULT FROM KPI_TBL GROUP BY [COUNTRY], [PERIOD], [KPI_NAME], [KPI_DESCRIPTION], [UNIT_OF_MEASURE], [RECORD_TYPE], [RESPONSIBLE_OWNER], [COMMENT], [SECTOR], [DIVISION], [BUSINESS_UNIT], [COMMODITY_CATEGORY], [MAJOR_COMMODITY], [SUB_COMMODITY], [UPLOAD_USER], [DATE_CREATED], [LAST_UPDATED], [FILE_URL];

The user then inputs a filter expression into a textbox (or he/she uses the expression builder). Example of basic expression would be:

SECTOR='Energy'

The recordset is then converted using the following function:

Public Function FilterRecordSet(ByVal objRecordSet As ADODB.RecordSet, ByVal strFilterText As String) As ADODB.RecordSet
    Dim objStream As ADODB.Stream
    
    On Error Resume Next
        objRecordSet.Filter = strFilterText
    On Error GoTo 0
    
    Set objStream = New ADODB.Stream
    With objRecordSet
        .Save objStream, adPersistADTG
        .Close
        .Open objStream
    End With
    
    Set FilterRecordSet = objRecordSet
End Function

The recordset gets spat out either into a pivot table or a text file, e;g:

lngFile = FreeFile
strFile = .tbxFileName.Text
strData = objRecordSet.GetString(adClipString, -1, "|", vbCrLf, vbNull)
Open strFile For Binary Access Write As #lngFile
    Put #lngFile, , strData
Close #lngFile

I don't see that I've done anything unusual anywhere in this project. How is what you are doing different?

 

[RoryA]

Dunno - I'm using ADO2.8 and was testing with an extract from Excel. Absolutely no change in data order, although I was extracting using SELECT *. I'll try selecting the columns in a different order from the actual source data.

Edit: nope, that made no difference to me.

 

[Jon von der Heyden]

Doh! This is really baffling. I am using same library. My data source however is Access. Although not sure why it should make any difference, but I'll test this from an Excel source.

 

[Jon von der Heyden]

Doesn't seem as though the data source file format makes any difference for me.

Rory, if I email you a file with the class and an excel source file can you please run a test routine for me and let me know if it sorts the field order?

Pretty please...

 

[RoryA]

I'll try...

 

[Jon von der Heyden]

Thanks Rory. I just sent it. You just need to save the source file somewhere and then run the routine in the module in the file called 'Example_ADO_Filter_Sort_Issue'.

Edit: Er, mistake. You need to update the DBPath property in the routine to the fullname of the source file.

 

[RoryA]

OK, I'll try and have a look when I get home. Or, possibly more likely, tomorrow morning!