asked on

VBA Access ADO Recordset Field Names Output

Hi,

I am exporting some selected data from a table in Access into Excel. I have done this many time however for some reason the code below is not working as I expect it to. All the records are exported to excel but the field order is incorrect.

So in my query below the first two fields I want in columns A and B or Security Code Primary & Sedol. However for some reason when the data is exported it sorts the field names in alphabetical order?

Private Sub ExportData(Factory As clsAccess)

Dim rsEx As ADODB.Recordset

' query data from consolidate table
Factory.strQueryText = "SELECT SS_Consolidate.[Security Code Primary], SS_Consolidate.Sedol, SS_Consolidate.Isin, " & _
                            "SS_Consolidate.[Alternate Security Code], SS_Consolidate.[SECURITY NAME SHORT], SS_Consolidate.[Currency Code Local], " & _
                            "SS_Consolidate.[SECURITY TYPE CODE], SS_Consolidate.[COUNTRY RISK], SS_Consolidate.[Transaction Number External], SS_Consolidate.[Process Date], " & _
                            "SS_Consolidate.[Trade Date], SS_Consolidate.[Settlement Date], SS_Consolidate.[Transaction Category Code], " & _
                            "SS_Consolidate.[Currency Code Settlement], SS_Consolidate.[Fx Rate Local To Firm], SS_Consolidate.Units, " & _
                            "SS_Consolidate.[Price Trade Local], SS_Consolidate.[Trading Units], SS_Consolidate.[Cost Local], " & _
                            "SS_Consolidate.[Proceeds Local], SS_Consolidate.[Interest Purchased Sold Local], SS_Consolidate.[Commission Local], " & _
                            "SS_Consolidate.[Fee Total Capitalized User Defined Local], SS_Consolidate.[Portfolio Code], " & _
                            "SS_Consolidate.[Client Executing Broker], SS_Consolidate.[Principal Or Agent Name], SS_Consolidate.[Fx Rate Local To Portfolio], " & _
                            "SS_Consolidate.[Currency Code Portfolio], SS_Consolidate.[Block Id External], SS_Consolidate.Type " & _
                        "FROM SS_Consolidate " & _
                        "ORDER BY SS_Consolidate.[Security Code Primary];"
Set rsEx = Factory.SQLExecuteQuerySelect
    
    If rsEx.EOF = False Then
        ' export data to excel
        Dim wb As Workbook
        Dim ws As Worksheet
        Set wb = Application.ActiveWorkbook
        Set ws = wb.ActiveSheet
                
        ' populate header
        Dim i As Integer
        
        For i = 0 To rsEx.Fields.Count - 1
            ws.Cells(1, i + 1) = rsEx.Fields(i).Name
        Next
        
        ws.Range("A5").CopyFromRecordset rsEx
        ws.Rows("1:1").Font.Bold = True
        ws.Cells.EntireColumn.AutoFit
    Else
        MsgBox "No data found in Consolidate table, please check the contents of the excels files, Transactions & Security_Reference", vbInformation, "No Data"
    End If

Set rsEx = Nothing

End Sub

Open in new window

Code below is from the class

Public Function SQLExecuteQuerySelect() As ADODB.Recordset

' Any SQL select query is run from here

    On Error Resume Next                        ' cannot open recordset if already open
        prs.Close
    If Err <> 0 Then On Error GoTo 0
    
    prs.Open strQueryText, pcn, adOpenKeyset, adLockOptimistic
    Set SQLExecuteQuerySelect = Clone(prs)
    prs.Close
    
End Function

Private Function Clone(ByVal oRs As ADODB.Recordset) As ADODB.Recordset

' Function makes a clone of the private recordset. The ADO Recordset.Clone method doesn't actually clone the recordset. It doesn't create a new object _
    in memory it just returns a reference to the private recordset. This function make a real clone without any dependencies. _
    Without this function user cannot have more than one open recordset

Dim rsClone As ADODB.Recordset

    'save the recordset to the stream object
    Set pStream = New ADODB.Stream
    prs.Save pStream
    
    'and now open the stream object into a new recordset
    Set rsClone = New ADODB.Recordset
    rsClone.Open pStream
    
    'return the cloned recordset
    Set Clone = rsClone
    
    'release the reference
    Set rsClone = Nothing
    
End Function

Open in new window

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mcs26

ASKER

Hi,

Thanks for the reply. Yep your code works fine! I'm confussed though what is causing mine not to as other similar queries from other projects work fine? Have you come across this issue before?

I didn't even know you could order the fields of a recordset in alphabetical order?

Thanks again,
M

Rey Obrero (Capricorn1)

probably it was done when you use the Class Factory in your codes.

sorry, but i don't see any reason to use the Class if you just want to export the records to Excel.. actually, i've never used any UDF class just to export records to excel

mcs26

ASKER

Yes I agree I have inherited the code and now intrigued to understand whats going on.

The Clone function in the class is causing the error.

prs is a private variable in the class of type adodb.recordset. Even though it is private it is still passed to the Clone function by val. When I look at the local varibales whilst in the Clone function oRs has the correct field order and source.

The line rsClone.Open pStream is where the fields change order? Although for other select queries this has worked.

Public Function SQLExecuteQuerySelect() As ADODB.Recordset

' Any SQL select query is run from here

    On Error Resume Next                        ' cannot open recordset if already open
        prs.Close
    If Err <> 0 Then On Error GoTo 0
    
    prs.Open strQueryText, pcn, adOpenKeyset, adLockOptimistic
    Set SQLExecuteQuerySelect = Clone(prs)
    prs.Close
    
End Function

Private Function Clone(ByVal oRs As ADODB.Recordset) As ADODB.Recordset

' Function makes a clone of the private recordset. The ADO Recordset.Clone method doesn't actually clone the recordset. It doesn't create a new object _
    in memory it just returns a reference to the private recordset. This function make a real clone without any dependencies. _
    Without this function user cannot have more than one open recordset

Dim rsClone As ADODB.Recordset

    'save the recordset to the stream object
    Set pStream = New ADODB.Stream
    pRs.Save pStream
    
    'and now open the stream object into a new recordset
    Set rsClone = New ADODB.Recordset
    rsClone.Open pStream
    
    'return the cloned recordset
    Set Clone = rsClone
    
    'release the reference
    Set rsClone = Nothing
    
End Function

Open in new window